[SOLVED] Problem ordering by date

[jcstanley]

Hi

 

I have a query as follows:

 

$query = "SELECT eventid, name, DATE_FORMAT(date, '%d %b %y') AS date, time, type, extra FROM events WHERE type = 'Training' AND date BETWEEN CURDATE() AND DATE_ADD(CURDATE(), INTERVAL 21 DAY) ORDER BY 'date' desc";

 

This displays any events within the next 21 days, but not quite in the right order!

 

The outputted result is:

25 Mar 07

24 Mar 07

08 Apr 07

07 Apr 07

06 Apr 07

 

As you can see the months are in the correct order but how can the days be reversed? eg:

24 Mar 07

25 Mar 07

06 Apr 07

07 Apr 07

08 Apr 07

 

Many thanks

 

[paul2463]

you put the orderby date in single ticks try this one

 

$query = "SELECT eventid, name, DATE_FORMAT(date, '%d %b %y') AS `date`, time, type, extra FROM events WHERE type = 'Training' AND date BETWEEN CURDATE() AND DATE_ADD(CURDATE(), INTERVAL 21 DAY) ORDER BY `date` desc";

[jcstanley]

you put the orderby date in single ticks try this one

 

$query = "SELECT eventid, name, DATE_FORMAT(date, '%d %b %y') AS `date`, time, type, extra FROM events WHERE type = 'Training' AND date BETWEEN CURDATE() AND DATE_ADD(CURDATE(), INTERVAL 21 DAY) ORDER BY `date` desc";

 

Sorry, the above code is what I have already

[kenrbnsn]

You're ordering by the ASCII reformated date format, not by the internal format. Change the "as" clause to something other than the name of the date field. Something like:

<?php
$query = "SELECT eventid, name, DATE_FORMAT(`date`, '%d %b %y') AS formatted_date, `time`, `type`, extra FROM events WHERE type = 'Training' AND date BETWEEN CURDATE() AND DATE_ADD(CURDATE(), INTERVAL 21 DAY) ORDER BY `date` desc";
?>

 

Ken

[jcstanley]

Thanks Ken

 

That works perfectly!