Page 1 of 1

[SOLVED] MySQL DB - SQL ORDER BY

Posted: Wed Jun 30, 2004 10:35 am
by hairyjim
hi all,

Basically I have the following SQL statemnt:

Code: Select all

$sql = "SELECT sid, title, DATE_FORMAT(creation_date,'%d/%m/%Y') as creation_date FROM my_db WHERE category = 'product' ORDER by creation_date desc LIMIT 5";
Now the returned results of this are:

Code: Select all

new discovery 29/04/2004  
New Release 27/01/2004  
Server Support 27/01/2004  
Released 12/03/2004  
Coming soon! 11/06/2004
It seems to have ordered them by the 'd', the last article should actually be at the top.

I'm a little stumped on this, I have no idea what I need to do to sort them correctly.

Cheers for any advice offered.
Jim

Posted: Wed Jun 30, 2004 10:40 am
by JayBird
When formatting a date using DATE_FORMAT, i always give the formatted date a new name.

This may solve your problem, not sure

Code: Select all

$sql = "SELECT sid, title, DATE_FORMAT(creation_date,'%d/%m/%Y') as formatted_creation_date FROM my_db WHERE category = 'product' ORDER by creation_date desc LIMIT 5";
Mark

Posted: Wed Jun 30, 2004 10:45 am
by markl999
This may solve your problem, not sure
That's exactly the problem, your solution is correct ;)

Posted: Wed Jun 30, 2004 10:46 am
by hairyjim
Yes that did work.

So I take it that PHP or the SQL was getting confused.

The lesson is always give the formatted date a new name.!

Ta muchly

Posted: Wed Jun 30, 2004 10:51 am
by redmonkey
Nothing to do with PHP or MySQL being/getting confused.

The DATE_FROMAT function takes a given date and returns the result as a string. Therefore the ORDER BY sorting is being performed on strings not dates.

For your initial query, MySQL did return the correct results in the correct order that you asked it for, you just asked it for the wrong thing.