$result= mysql_query("SELECT date_format(date, '%d/%m/%Y') as date, title,
id, display FROM news ORDER BY date DESC ");

I have the query above the problem is oders them like so

30/05/2007
29/07/2007
25/0/2007


The order is taken by the first number. Is there any way to order them
properly without a timestamp?


Ta,

R.

May I suggest:

SELECT date_format(date, '%d/%m/%Y') as mydate, title,
id, display FROM news ORDER BY date DESC

changing the alias of your formatted date to mydate, the raw value of
date is now available to order by.

- michael dykman


On 9/12/07, Ross Hulford <ross@aztechost.com> wrote:
> $result= mysql_query("SELECT date_format(date, '%d/%m/%Y') as date, title,
> id, display FROM news ORDER BY date DESC ");
>
> I have the query above the problem is oders them like so
>
> 30/05/2007
> 29/07/2007
> 25/0/2007
>
>
> The order is taken by the first number. Is there any way to order them
> properly without a timestamp?
>
>
> Ta,
>
> R.
>
>
>
> --
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe: http://lists.mysql.com/mysql?unsub=mdykman@gmail.com
>
>


--
- michael dykman
- mdykman@gmail.com

- All models are wrong. Some models are useful.

I suspect that your problem is that you are converting your date field to a
string before sorting. You shouldn't use the same name for the alias as you
do for the field. Try

$result= mysql_query("SELECT date_format(date, '%d/%m/%Y') as
formatted_date, title,
id, display FROM news ORDER BY date DESC ");

Regards,

Jerry Schwartz
The Infoshop by Global Information Incorporated
195 Farmington Ave.
Farmington, CT 06032

860.674.8796 / FAX: 860.674.8341

www.the-infoshop.com
www.giiexpress.com
www.etudes-marche.com


> -----Original Message-----
> From: Ross Hulford [mailto:ross@aztechost.com]
> Sent: Wednesday, September 12, 2007 4:49 PM
> To: mysql@lists.mysql.com
> Subject: ordering dates
>
> $result= mysql_query("SELECT date_format(date, '%d/%m/%Y') as
> date, title,
> id, display FROM news ORDER BY date DESC ");
>
> I have the query above the problem is oders them like so
>
> 30/05/2007
> 29/07/2007
> 25/0/2007
>
>
> The order is taken by the first number. Is there any way to
> order them
> properly without a timestamp?
>
>
> Ta,
>
> R.
>
>
>
> --
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe:
> http://lists.mysql.com/mysql?unsub=j...e-infoshop.com
>
>

> $result= mysql_query("SELECT date_format(date, '%d/%m/%Y') as date, title,
> id, display FROM news ORDER BY date DESC ");
>
> I have the query above the problem is oders them like so
>
> 30/05/2007
> 29/07/2007
> 25/0/2007
>
>
> The order is taken by the first number. Is there any way to order them
> properly without a timestamp?

You're ordering by "date" but previously you turn "date" into a string by
calling date_format on it. Change the "as date" to something else and
then the ordering will be chronologically descending. Like this:

SELECT date_format(date, '%d/%m/%Y') as formatted_date, title, id, display
FROM news ORDER BY date DESC