Sorting MySQL output from a text displayed date.

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
impulse()
Forum Regular
Posts: 748
Joined: Wed Aug 09, 2006 8:36 am
Location: Staffordshire, UK
Contact:

Sorting MySQL output from a text displayed date.

Post by impulse() »

If I have a query that outputs like:

+--+--------------+
| X | April/2006 |
+--+--------------+
| X | Jan/2006 |
+--+--------------+
| X | June/2005 |
+--+--------------+

X being a count which was grouped by the date then how would I sort those dates in order in MySQL?

Regards,
sunilbhatia79
Forum Newbie
Posts: 24
Joined: Sun Nov 11, 2007 9:37 pm
Location: Mumbai, India

Post by sunilbhatia79 »

You cannot sort these properly.... you will always get April first and then Jan...

I recommend that you create another field that will hold numbers like 200701 for Jan 2007 and 200704 for Apr 2007. Further, create a PHP script that will read all the records and convert the data Apr 2007 to 200704 and store it into the table...

Then use the newly created field to sort and get your query...

Further, i would also recommend that you drop the old field and update your code accordingly... its not a good practice to have your date cols as strings mentioned by you earlier.

Please let me know if you need more help.

Sunil
User avatar
Kieran Huggins
DevNet Master
Posts: 3635
Joined: Wed Dec 06, 2006 4:14 pm
Location: Toronto, Canada
Contact:

Post by Kieran Huggins »

yeah, Sunil's totally on to something there. Either a UNIX timestamp or a MySQL Date column is my first instinct, depending on how fancy your sorts need to get. MySQL has some pretty sexy date methods that need a date column to work.
impulse()
Forum Regular
Posts: 748
Joined: Wed Aug 09, 2006 8:36 am
Location: Staffordshire, UK
Contact:

Post by impulse() »

As far as I can see the output has to be this way because the query is grouped. I have managed to solve this with PHP though, just exploded the date and converted it to a timestamp for the first second of the first day for that month then sorted by timestamp and converted back to a string date.

Thanks for your suggestions either way though.
Post Reply