php - How to sort using month and day using mysql

571

I have a date in this format.

08 april 1989
02 December 1984
13 January 1986

I would like to sort the results using month and day ,which has column "dateofb" and i sorting is like

13 january 1986
08 april 1989
02 december 1984

I have used the below code which doesn't work fine ,

$sel = $db->query("select * from biography where dateofb >= (CURDATE() - INTERVAL 90 DAY) order by dateofb desc limit 0,3");

I would like to display the 3 sorted results coming 90days.

250

Answer

Solution:

Is it possible to convert thedateofb column toDATE column type? This would allow you to do what you're looking for. The format you have now is invalid and would need to be converted viaSTR_TO_DATE(dateofb, '%d %M %Y')

Example:

$sel = $db->query("select * from biography where STR_TO_DATE(dateofb, '%d %M %Y') >= (CURDATE() - INTERVAL 90 DAY) order by STR_TO_DATE(dateofb, '%d %M %Y') desc limit 0,3");

^- See how gross that looks? If yourdateofb was aDATE column, you could just do:

$sel = $db->query("select * from biography where dateofb >= (CURDATE() - INTERVAL 90 DAY) order by dateofb desc limit 0,3");

^- which is your original query.

279

Answer

Solution:

If your "dateofb" field is a DATE or DATETIME:

SELECT 
    *    
FROM biography
WHERE dateofb >= (CURDATE() - INTERVAL 90 DAY)
ORDER BY MONTH(dateofb), DAY(dateofb) ASC
LIMIT 0, 3

People are also looking for solutions to the problem: php - Error Uploading Images to my server

Source

Didn't find the answer?

Our community is visited by hundreds of web development professionals every day. Ask your question and get a quick answer for free.

Ask a Question