sorting month-year wise

Question

i have some entries which are having data month-wise

like

May-2009
April-2009
March-2009
-
-
-
-
-
-
January-2000

Now i want this data to be sorted monthly via mysql query.

$q=mysql_query(SELECT id,action,
     L1_data,
     L2_data,
     L3_data,
     L11_data 
  from table 
  where action='feild name' 
  ORDER BY DATE_FORMAT(L11_data,'%m-%Y') DESC);

while($r=mysql_fetch_array($q))
{
-------
}

here L11_data is the one that is having all the month values (eg May-2009 etc)

Iam using this query but it is of no help. what i want is to show data month-wise from May-2009 to January-200 Can anyone please suggest me some code for this???

Answer 1

You should order by STR_TO_DATE(L11_data, '%m-%Y'), assuming L11_data is already in the format "May-2009". If L11_data is a date or datetime field, you should order by that field alone.

Edit: Sorry, it should be %M not %m for "July" format. %m represents "07" format. %b for "Jul" format.

SELECT
    id, action,
    L1_data, L2_data,
    L3_data, L11_data 
FROM table 
WHERE action = 'field name' 
ORDER BY STR_TO_DATE(L11_data, '%M-%Y') DESC

Answer 2

In your case the data may be legacy, but when creating new systems, you should store the data in an appropriate format. For month-year pairs in MySQL, it would be the date type. In MySQL, dates can have a zero value in month and day parts, eg "00-01-2000" would represent January 2000.

If you have your data saved in a precisely-typed field, selecting and sorting is way easier.