How to order MySQL datasets by date

Databases
1

Hi,

I’m trying to write a script that fetches a bunch of records from a MySQL database table and displays them in chronological order, starting with the most recent.

So far I have:

$query = "SELECT DATE_FORMAT(date,'%e.%c.%Y at %k:%i') AS date, title, name, message FROM guestbook ORDER BY date DESC";

This more or less works, but it displays 9:00 and 11:00 incorrectly (the wrong way round). I’m guessing it would do the same for any time with only one digit before the colon.

Sample output:
Date: 12.4.2011 at 9:51
Date: 12.4.2011 at 11:57
Date: 12.4.2011 at 11:28
Date: 12.4.2011 at 11:01
Date: 11.4.2011 at 23:08
Date: 11.4.2011 at 22:25
Date: 11.4.2011 at 17:14

What I would like:
Date: 12.4.2011 at 11:57
Date: 12.4.2011 at 11:28
Date: 12.4.2011 at 11:01
Date: 12.4.2011 at 9:51
Date: 11.4.2011 at 23:08
Date: 11.4.2011 at 22:25
Date: 11.4.2011 at 17:14

Can anyone give me a hand to sort this out?
Any help gratefully appreciated.

2

Use another alias for the date in the select, so the order by will use the original date stored in the database.

3

Cool, that did the trick.
Thank you very much for your help.

4

even better would be to return only the datetime column, unformatted, properly sorted… and do all your formatting where it should be done, in the application layer

:slight_smile: