[SOLVED] How to sort year and month fields in same row from mysql table using php

How to sort year and month fields in same row from mysql table using php

I have a mysql table with month and year fields as below.

    year        month       filename                    filepath
    2013        Feb         2013Feb_Report.xlsx         ./main_reports/2013Feb_Report.xlsx
    2013        Jan         2013Jan_Report.xlsx         ./main_reports/2013Jan_Report.xlsx
    2012        Jul         2012Jul_Report.xlsx         ./main_reports/2012Jul_Report.xlsx
    2013        Mar         2013Mar_Report.xlsx         ./main_reports/2013Mar_Report.xlsx
    2011        Mar         monthly report180413.xlsx   ./main_reports/monthly report180413.xlsx
    2012        Sep         2012Sep_Report.xlsx         ./main_reports/2012Sep_Report.xlsx
    2012        Oct         2012Oct_Report.xlsx         ./main_reports/2012Oct_Report.xlsx

I'm retrieving all fields from this table and prints it as below.

    2011 Mar  :  monthly report180413.xlsx
    2012 Sep  :  2012Sep_Report.xlsx
    2012 Oct  :  2012Oct_Report.xlsx
    2013 Jan  :  2013Jan_Report.xlsx

I want to retrieve this table by sorting both month and year fields in DESC order as below. How can I do this? Should I change the data type of the month and year fields? Please Help..

    2013 Jan  :  2013Jan_Report.xlsx
    2012 Oct  :  2012Oct_Report.xlsx
    2012 Sep  :  2012Sep_Report.xlsx
    2011 Mar  :  monthly report180413.xlsx

The sql query I have used is as below. It retrieves year and month in DESC order but the month is sorted in DESC order of alphabets i.e., 'Jan' comes above 'Feb'. What I need is 'Feb' above 'Jan'..

 "SELECT * FROM main_reports ORDER BY year DESC, month DESC";

Any help please.. Thanks in advance..

Solution

Assuming you're using MySQL. Try using the handy ORDER BY FIELD option:

ORDER BY year DESC, FIELD( month, 'Dec', 'Nov', 'Oct', 'Sep', 'Aug', 'Jul', 'Jun', 'May', 'Apr', 'Mar', 'Feb', 'Jan' )