MySQL On this Day

[Ciba]

Wondered if anyone could help me.

I've got a table of data with years formatted as YYYY-MM-DD and I am looking to implement an "On this day" type feature on to the site. I've been struggling with coming up with a way of pulling out the data though where it matches the current month and day regardless of the year.

So far I've though of using a simple query like:

$currentday = date('d');

$currentmonth = date('m');

SELECT * FROM table WHERE date LIKE $currentmonth AND $currentday

But my results are failing, so I guess I need a wildcard to recognise the year?

Is there a better solution?

 

[BaJ]

Try:

SELECT * FROM table WHERE date = CURDATE();

 

[GravyMonster]

^ That will only work where the date is the same as the current date, the OP wants it to pick out records where the month and day matches, but any year. Try:

$currentday = date('j'); // Day of month without leading zero
$currentmonth = date('n'); // Month without leading zero
SELECT * FROM Table WHERE MONTH(DateField) = $currentmonth AND DAYOFMONTH(DateField) = $currentday ORDER BY DateField DESC;

 

[BaJ]

You're right, that'll teach me to read the OP properly next time.

Although you could still utilise the MySQL date functions in some way:

SELECT * FROM Table WHERE MONTH(DateField) = MONTH(CURDATE()) AND DAYOFMONTH(DateField) = DAYOFMONTH(CURDATE()) ORDER BY DateField DESC;

I think that works, not tested it mind. Ways to skin a cat and all that

 

[Ciba]

Thanks guys that brilliant.

I used this method in the end,

SELECT * FROM table WHERE DAY (date) = $day AND MONTH (date) = $month

Quite simple in the end.