ORDER BY date ASC

PHP programming forum. Ask questions or help people concerning PHP code. Don't understand a function? Need help implementing a class? Don't understand a class? Here is where to ask. Remember to do your homework!

Moderator: General Moderators

Post Reply
Jim_Bo
Forum Contributor
Posts: 390
Joined: Sat Oct 02, 2004 3:04 pm

ORDER BY date ASC

Post by Jim_Bo »

Hi,

I have a query like so:

$query="SELECT id, DATE_FORMAT(date, '%d %M %Y') as date, event FROM events ORDER BY date DESC";
$result=mysql_query($query);

But it doesnt list the ORDER BY in a correct manner .. I am getting an order of:

30 November 2005


28 April 2008


25 December 2006


How do I correct this to make it order ASC:


30 November 2005

25 December 2006

28 April 2008


Thanks
ruchit
Forum Commoner
Posts: 53
Joined: Mon Sep 26, 2005 6:03 am

Post by ruchit »

Code: Select all

$query="SELECT id, DATE_FORMAT(date, '%d %M %Y') as `date`, event FROM events ORDER BY `date`;
Jim_Bo
Forum Contributor
Posts: 390
Joined: Sat Oct 02, 2004 3:04 pm

Post by Jim_Bo »

hi,

it seems to give the same result as the original code

Cheers
User avatar
CoderGoblin
DevNet Resident
Posts: 1425
Joined: Tue Mar 16, 2004 10:03 am
Location: Aachen, Germany

Post by CoderGoblin »

try

Code: Select all

SELECT id, DATE_FORMAT(date, '%d %M %Y') as use_date, event, date FROM events ORDER BY date DESC
When you change the format it is converted to text and sorting as text.
Jim_Bo
Forum Contributor
Posts: 390
Joined: Sat Oct 02, 2004 3:04 pm

Post by Jim_Bo »

Hi,

That works great


Cheers
User avatar
Skara
Forum Regular
Posts: 703
Joined: Sat Mar 12, 2005 7:13 pm
Location: US

Post by Skara »

The reasoning behind this is because it doesn't recognize 'd M Y' as a date. It orders first by the number in front (the day), then by the lettering in the second word (month), then the number at the end (year).
The only way to correctly sort by date is to have a timestamp or use the universal date format (yyyy/mm/dd).

;)
Post Reply