Page 1 of 1
Sorting MySQL output from a text displayed date.
Posted: Tue Nov 13, 2007 4:29 am
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,
Posted: Tue Nov 13, 2007 5:34 am
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
Posted: Tue Nov 13, 2007 6:01 am
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.
Posted: Tue Nov 13, 2007 2:31 pm
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.