MySql DATE and ORDERING something by the DATE and displaying

ncwdavid

Hey, I am making a website for a band and there is an upcoming gigs page. I want the user to be able to click on the "Date" heading and it will display the gigs according to the date, ASC or DESC.

I have the "date" field in the database set to "DATE". When adding a gig you put the date like this e.g "05-02-08" and that gets inserted into the date column which is set to DATE type. How do I order my results by the date? I know how to do the query like "ORDER BY date DESC" but its just trying to get them to come out in the right order.

I hope I provided enough information. Thanks in advance.

rvec

you could use a datetype field in mysql.

Or you could use a php function to make a date.

AftershockVibe

DATE is the way to go because it means that MySQL does all the complex stuff for you. Alternatively, you could store it as an Int after generating a Timestamp in PHP.

In fact, they're probably equivalent if all you want to do is put them in order but I haven't looked into it.

However, if you start wanting to do less obvious stuff like sort/filter by month, it is much easier to get a MySQL function to do this on a DATE field than it is to parse in PHP on a timestamp afterward.

kv

If you are using a mysql "DATE" field, then "ORDER BY date_field DESC" will automatically get the results in correct order. It does not do a string-order but will do a date-order.

This may work for small amount of data, but if you have huge data (several thousand rows), then I have observed that sorting on date fields are always much slower compared to sorting on any other type fields (text, number,..).