[SOLVED] Ordering by date & time not working

[jcstanley]

Hi

 

I have the following query which does select all the relevant information from the 'news' table but does not display them in descending order by date(&time) as requested.

 

// Define the number of results per page 
$max_results = 4; 

// Figure out the limit for the query based 
// on the current page number. 
$from = (($page * $max_results) - $max_results);  

// Perform MySQL query on only the current page number's results 

$sql = mysql_query("SELECT title, description, picture, DATE_FORMAT(date, '%d %M %y %H:%i:%s') AS formatted_date FROM news ORDER BY formatted_date desc LIMIT $from, $max_results"); 

 

any ideas where it is going wrong?

 

many thanks

[suma237]

the query is working fine?.what is the error?please mention that,so that i can check it.

[jcstanley]

the error is that the results are not listed in descending (nor ascending) order on the page.  They appear to be randomly placed.

 

thanks

[Rithiur]

You should order by "date" and not by "formatted_date". The reason is that once you've formatted like that, the date can not be string sorted to make sensible order anymore. This is because the day comes first in the string. so when you order by the formatted date, you will always get first the dates which are later in the month (due to the fact that their date number is higher, which is the first thing the string is sorted by).

 

So, just change your "ORDER BY formatted_date desc" to "ORDER BY date desc" and it should be fine.

[jcstanley]

thanks that works perfectly