Structure
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;
Data
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).
Scenario:
If I have user, that will be created let's say 2020-01-01, then select should pick template with name "yes_4".
Scenario:
User was created in 2018-01-01, then template selected should have name "agreement_x"
Scenario:
User created in 2019-01-15 will select row with name "template_2"
So far:
:date = - user created
query
SELECT * FROM `template`
WHERE DATE(`valid`) < DATE(:date)
ORDER BY DATE(`valid`) DESC
LIMIT 1
valid
is of typeDATE
, there is no need for applyingDATE()
to it.