...

View Full Version : Date sorting problem with MySQL query



denhamd2
05-29-2007, 04:21 PM
Hi,

I have a MySQL query to select all the entries in the database and order by date:


$query_get_forum_messages = "SELECT * FROM bookings, users WHERE theuserid = userid ORDER BY date ASC";


However, all the dates are in the format 27012007 rather than 20070112. The current way causes the dates to list incorrectly, in that 01022007 follows 01012007, where it should be 02012007 following 01012007. Is there any way PHP might be able to help out? There are already a lot of entries and I'm looking to avoid going back and changing the whole system.

Thanks in advance...

whizard
05-29-2007, 05:16 PM
I don't understand, how is 27012007 the same date as 20070112?

Are the dates in a date field in the database? If so you can use MySQL's date functions: (http://www.databasejournal.com/features/mysql/article.php/2172731)

Alternatively, you can use PHP's date() function (http://www.php.net/manual/en/function.date.php).

HTH
Dan

CFMaBiSmAd
05-29-2007, 05:38 PM
As whizard has pointed out, if your dates are stored in a mysql DATE type field, there are a number functions available to directly manipulate them and ORDER BY and comparisons work (for your existing scheme comparisons only work when the year is the same), and work as quickly as possible - http://dev.mysql.com/doc/refman/5.0/en/date-and-time-functions.html

By not storing them as a DATE type, you are left with using multiple individual string functions to build them into something that can be sorted, either in mysql or in php, which would be slower than if they were a DATE type.

It is better to correct this type of thing sooner rather than later.

denhamd2
06-03-2007, 08:21 PM
I've changed the type in the database to date. So bearing in mind I have the list of dates stored in the field in the format DDMMYYYY could you tell me how I would sort by the latest date first? Thanks again

Fumigator
06-03-2007, 11:44 PM
I've changed the type in the database to date. So bearing in mind I have the list of dates stored in the field in the format DDMMYYYY could you tell me how I would sort by the latest date first? Thanks again

MySQL stores "date" type values in a compressed format, not a DDMMYYYY format, so you can perform sort and comparisons and calculations on "date" type values to your heart's content (this is the value of using "date" types). Use the "ORDER BY" clause:



SELECT *
FROM table_name
WHERE ....
ORDER BY date_field desc



EZ Archive Ads Plugin for vBulletin Copyright 2006 Computer Help Forum