CODEWITHSUNDEEP
phpmysqlsorting
Rupert Forbes
Rupert Forbes

Reputation: 23

How to sort month years from mysql to php

I have a list from mysql db which includes months combined with years that need to be sorted accordingly. At the moment the returned list (ordered DESC) looks like this:

122015 - (Dec 2015)
112015 - (Nov 2015)
102015 - (Oct 2015)
92015 - (Sep 2015)
82015 - (Aug 2015)
12016 - (Jan 2016)

I would like the above list to follow logical sorting with Jan 2016 being top of the list e.g.

12016 - (Jan 2016)
122015 - (Dec 2015)
112015 - (Nov 2015)
102015 - (Oct 2015)
92015 - (Sep 2015)
82015 - (Aug 2015)

How do I go about achieving this in php?

*Update the sql query I used is:

SELECT mnthyears FROM dates ORDER BY mnthyears DESC

Upvotes: 1

Views: 131

Answers (3)

n-dru
n-dru

Reputation: 9420

Using mysql LPAD (padding function) combined with SUBSTR method in ORDER BY clause:

SELECT mnthyears
FROM dates
ORDER BY SUBSTR(LPAD( mnthyears, 19, '0'),3,4) DESC, SUBSTR(LPAD(mnthyears,19,'0'),1,2) DESC

If however you have only those 5 or 6 digits strings (without "- (Dec 2015)"), just change 19 to 6

Upvotes: 1

Abhishek Sharma
Abhishek Sharma

Reputation: 6661

use MySQL str_to_date

str_to_date(replace(replace('(Jan 2016)',')',''),'(',''),'%b  %Y')

Query 

SELECT mnthyears FROM dates 
ORDER BY str_to_date(replace(replace('(Jan 2016)',')',''),'(',''),'%b  %Y') DESC

Upvotes: 1

Jan
Jan

Reputation: 13858

Considering the length of your date string changes, you should be able to pull this off inside your SQL code - no need to do it in PHP afterwards

select case when len(mnthyears ) = 18 then '0'+mnthyears else date_column end 
   as mnthyears FROM dates ORDER BY 1 DESC;

Upvotes: 0

Related Questions