CREATE TABLE `template` ( `name` VARCHAR(16) NOT NULL COLLATE 'utf8_unicode_ci', `valid` DATE NULL DEFAULT NULL, UNIQUE INDEX `name` (`name`) ) COLLATE='utf8_unicode_ci' ENGINE=InnoDB;
I am trying to select right template name based on date. "Valid" column represents date till template is valid. If Template have 0000-00-00 or NULL valid date, that means that this template is current (latest). Template names does not suggest order (they can be random string).
If I have user, that will be created let's say 2020-01-01, then select should pick template with name "yes_4".
User was created in 2018-01-01, then template selected should have name "agreement_x"
User created in 2019-01-15 will select row with name "template_2"
:date = - user created
SELECT * FROM `template` WHERE DATE(`valid`) < DATE(:date) ORDER BY DATE(`valid`) DESC LIMIT 1