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,
Sorting MySQL output from a text displayed date.
Moderator: General Moderators
-
sunilbhatia79
- Forum Newbie
- Posts: 24
- Joined: Sun Nov 11, 2007 9:37 pm
- Location: Mumbai, India
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
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
- Kieran Huggins
- DevNet Master
- Posts: 3635
- Joined: Wed Dec 06, 2006 4:14 pm
- Location: Toronto, Canada
- Contact:
-
impulse()
- Forum Regular
- Posts: 748
- Joined: Wed Aug 09, 2006 8:36 am
- Location: Staffordshire, UK
- Contact:
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.
Thanks for your suggestions either way though.