[SOLVED] MySQL DB - SQL ORDER BY

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
hairyjim
Forum Contributor
Posts: 219
Joined: Wed Nov 13, 2002 9:04 am
Location: Warwickshire, UK

[SOLVED] MySQL DB - SQL ORDER BY

Post 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
Last edited by hairyjim on Wed Jun 30, 2004 10:46 am, edited 1 time in total.
User avatar
JayBird
Admin
Posts: 4524
Joined: Wed Aug 13, 2003 7:02 am
Location: York, UK
Contact:

Post 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
User avatar
markl999
DevNet Resident
Posts: 1972
Joined: Thu Oct 16, 2003 5:49 pm
Location: Manchester (UK)

Post by markl999 »

This may solve your problem, not sure
That's exactly the problem, your solution is correct ;)
hairyjim
Forum Contributor
Posts: 219
Joined: Wed Nov 13, 2002 9:04 am
Location: Warwickshire, UK

Post 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
redmonkey
Forum Regular
Posts: 836
Joined: Thu Dec 18, 2003 3:58 pm

Post 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.
Post Reply