You are invited to Log in or Register a free Frihost Account!

MySql DATE and ORDERING something by the DATE and displaying

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.
you could use a datetype field in mysql.

Or you could use a php function to make a date.
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.
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,..).
Related topics
Interview: Derek Liu, Gaia Online Anime Community
FTP - file last modified date
Webpage Display Problem
PHP Tutorial: Basic Shoutbox w/ MySQL
mySQL - Day, date, month, year, time Output/Input
What is the best way to save a date/timestamp in mysql
Formatting mySQL date
Spanish date functions in php and mysql
How can I check...
Number or date/time field for timestamp?
Checking MySQL column and displaying some entries ..
Check the closest event in Calendar?
MySQL Date Addition with Variable Interval Type
Checkbox and Date filter with mysql and PHP
Reply to topic    Frihost Forum Index -> Scripting -> Php and MySQL

© 2005-2011 Frihost, forums powered by phpBB.