Order by formated date

[Stu_Rogers]

I have read lots of posts about using MySql to format the datefield (which works great), but I need to order my array by the actual date - not the formated date.

This is where I've got so far...

$result = @mysql_query("SELECT DATE_FORMAT(date,'%D %M') AS date, venue, coursetitle FROM training ORDER by date");

if (!$result) {
echo("<p>Error performing query: " . mysql_error() . "</p>");
exit();
}

while ($record=mysql_fetch_array($result)) {

echo("<tr><td>" . $record["date"] . "</td>\n");
echo("<td>" . $record["venue"] . "</td>\n");
echo("<td>" . $record["coursetitle"] . "</td></tr>\n");

}

Any help would be appreciated.

[coopster]

Welcome to WebmasterWorld, Stu_Rogers.

First, although MySQL allows it [dev.mysql.com], using keywords for column names is a bad practice and it is best to move away from this as early as possible. 'date' is one of those reserved words you really shouldn't use for a column name :)

Next, you have things almost correct here, except that your column name and ALIAS are the same. Try using a different name when ALIASing and then use the original column name for your ORDER BY clause. I have taken the liberty of renaming your column to 'mydate' rather than 'date' for the reason explained earlier:

$result = @mysql_query("SELECT DATE_FORMAT(mydate,'%D %M') AS mydateformatted, venue, coursetitle FROM training ORDER by mydate");

[Stu_Rogers]

That has worked. I think I'd looked at it for so long that I'd ruled out such an obvious solution.

Thanks for the tip on keywords too - I will rename that in the database.

[coopster]

I think I'd looked at it for so long that I'd ruled out such an obvious solution.

We've all been there. Sometimes it just takes another set of eyes.