PHP SQL - order by

Author

order by

Mark D. Smith

2006-04-18, 8:02 am

Hi

i have some data which i can read ok but i need to display by date posted.
Problem is the date is stored in a field as text DD/MM/YY

using ORDER BY date_posted ASC does not give desired effect as it seems to
use only the DD

example of current output.

05/04/06
05/04/06
05/04/06
05/04/06
16/01/06
16/01/06
22/03/06
23/03/06
24/03/06

is there a way (otherthan re writing all the dates in database) to sort the
dates by order ?

Mark

J.O. Aho

2006-04-18, 7:00 pm

Mark D. Smith wrote:
> Hi
>
> i have some data which i can read ok but i need to display by date posted.
> Problem is the date is stored in a field as text DD/MM/YY
>
> using ORDER BY date_posted ASC does not give desired effect as it seems to
> use only the DD
>
> example of current output.
>
> 05/04/06
> 05/04/06
> 05/04/06
> 05/04/06
> 16/01/06
> 16/01/06
> 22/03/06
> 23/03/06
> 24/03/06
>
> is there a way (otherthan re writing all the dates in database) to sort the
> dates by order ?

Whats the datatype for the column? It seems like it's not of the type DATE
which had sorted it correctly from the beginning.

One way would be trying to alter the type of the column to DATE, this may
involve that you have to store the dates and id in a temporary table.

http://dev.mysql.com/doc/refman/5.0/en/datetime.html
http://dev.mysql.com/doc/refman/5.0/en/alter-table.html

//Aho

Rich

2006-04-18, 7:00 pm

On Tue, 18 Apr 2006 11:01:51 +0100, Mark D. Smith wrote...
>
>Hi
>
>i have some data which i can read ok but i need to display by date posted.
>Problem is the date is stored in a field as text DD/MM/YY
>
>using ORDER BY date_posted ASC does not give desired effect as it seems to
>use only the DD
>
>example of current output.
>
>05/04/06
>05/04/06
>05/04/06
>05/04/06
>16/01/06
>16/01/06
>22/03/06
>23/03/06
>24/03/06
>
>is there a way (otherthan re writing all the dates in database) to sort the
>dates by order ?
>
>Mark
>
>

If the date information was stored as "text" instead of a date type, I don't
think there's be an easy way to sort that. It will probably just do an
alphabetical sort. Not sure if "alter table" has any options to modify the
column type, but may simplify what you're trying to do.

Rich

--
Carry Forward Bandwidth! Included In All NewsGuy Accounts!
Don't lose what you don't use! - http://newsguy.com/overview.htm

--

Bob Stearns

2006-04-18, 7:00 pm

Mark D. Smith wrote:

> Hi
>
> i have some data which i can read ok but i need to display by date posted.
> Problem is the date is stored in a field as text DD/MM/YY
>
> using ORDER BY date_posted ASC does not give desired effect as it seems to
> use only the DD
>
> example of current output.
>
> 05/04/06
> 05/04/06
> 05/04/06
> 05/04/06
> 16/01/06
> 16/01/06
> 22/03/06
> 23/03/06
> 24/03/06
>
> is there a way (otherthan re writing all the dates in database) to sort the
> dates by order ?
>
> Mark
>
>
You have two choices:
Better, unload the table, drop it (remembering to drop any fk
constraints that have it as target, and any views it is involved in),
recreate it with the data type of the column changed to date, reload the
table (remembering to put back all fk constraints which have it as
target and all views in which it participates). Now your order by will
work, and you are sure the data is a date.
Faster: order by substr(date_posted,7,2), substr(date_posted,1,5)

richard.a.fletcher@googlemail.com

2006-04-20, 3:59 am

I recommend the first choice. Doing things right make life easier in
the long run

Mark D. Smith

2006-04-21, 7:01 pm

<richard.a.fletcher@googlemail.com> wrote in message
news:1145524568.929463.105810@e56g2000cwe.googlegroups.com...
> I recommend the first choice. Doing things right make life easier in
> the long run
>

thanks to all who replied, i have changed the column to date and re worked
the code.

Mark

Sponsored Links