Sorting by date...

Anon

Hello.

I was wondering if one of you super smart php guys could answer a question for me. I am working on a travel website for my dad and right now most is going well.

One question I have though, is sorting the results of a query I make using mysql.

When adding records my dad enters a start date (Example would be Dec 1) and an end date (Example would be Dec 8).

What I want my result to do is sort by that field. But the current code I have, only sorts by alphabetical order... Can anyone think of a way to make this sort by the actual dates that he has entered?

Thanks,

Mekhu

PS. Right now I am using blah blah blah ORDER BY start_date

Anon

you have to change that field to the type DATE.. and from then on you have to insert the data in that field in the format:

YYYY-MM-DD

then you can sort with it, but if you want to change the format you have to use date_format() INSIDE the mysql query... you can look that on on www.mysql.com under documentation.. the syntax and functions chapter I belive, one of the appendixes.

date_format() is very powerful... try that out

Anon

So would that mean that I would have to change all the current dates in the database to that format?

Anon

You'll be glad you did in the future. Your situation is exactly the reason why the DATE type was created in the first place 🙂

But all is not lost - your Dad can still enter dates in as before; you can play with some of PHP's date-and-time functions (particularly strtotime()) to convert them into MySQL date format and back.

Of course, it may be possible to use strtotime() and sort the results in PHP with it, but I can tell you now that it will take a fair bit more work than tweaking the database will.