order by field()

[grantp22]

Can anybody help out with the following mysql query, I want to order a field by month, but the field values in the database contains the year as well!

Like this eg:

 

+--------------------+--------+---------+

|    strMonth      | event |  place  |

+--------------------+--------+---------+

|February 2011  | ......    | .......    |

|August 2010      | ......    | .......    |

|September 2010| ......    | .......    |

|January 2011    | ......    | .......    |

|October 2010    | ......    | .......    |

|November 2010 | ......    | .......    |

|December 2010 | ......    | .......    |

 

 

This is what I would like to achieve:

 

+--------------------+--------+---------+

|    strMonth      | event |  place  |

+--------------------+--------+---------+

|August 2010      | ......    | .......    |

|September 2010| ......    | .......    |

|October 2010    | ......    | .......    |

|November 2010 | ......    | .......    |

|December 2010 | ......    | .......    |

|January 2011    | ......    | .......    |

|February 2011  | ......    | .......    |

 

 

I could do it like this below if it was just the month, but it will include the year and the year portion can range from 2008 to 2015:

 

SELECT * from myTable order by field(strMonth, 'August', 'September', 'October') asc, strMonth

 

LEFT(strMonth, LENGTH(strMonth)-5) //which should give you eg: August

 

So i thought I could do this:

 

order by field(LEFT(strMonth, LENGTH(strMonth)-5), 'August', 'September', 'October') asc, strMonth

 

But I am sure this will give me:

 

+--------------------+--------+---------+

|    strMonth      | event |  place  |

+--------------------+--------+---------+

|January 2011    | ......    | .......    |

|February 2011  | ......    | .......    |

|August 2010      | ......    | .......    |

|September 2010| ......    | .......    |

|October 2010    | ......    | .......    |

|November 2010 | ......    | .......    |

|December 2010 | ......    | .......    |

 

I just cant figure out how the get them ordered by month and then order by year

 

Can any body help me out with this problem

 

Thanks

Grant

[fenway]

Use a proper DATE field, and this won't be an issue.  Technically, there's a way to achieve this, but it's such a bad idea that I'll spare you.

[grantp22]

Hi fenway I figured this was going to be problematic, and my sql is not that hot! The original designer of the website I am upgrading is using the same database for other functionallity, I have been asked to make additions to the website ie: more pages connecting to his db. He no longer works for the company and I have been asked to expand the website.

 

And now I am forced to use these string based fields because the old pages rely on these fields being strings, and I need them to be date based for my additonal pages and now I have run into this problem where I can't change the old page script to date based values because there are so many changes I would need to make.

 

So now I am stuck trying to adapt my own source to accept his old string based db values. I have been trying to figure this out for hours now and even visiting the mysql forums to try and put together a solution. So any ideas you may have will be greatly appreciated at this point no matter how good or bad they are, I getting desperate!

 

Thanks

Grant

[fenway]

You could technically order on STR_TO_DATE( strMonth, '%M %Y' ).

[grantp22]

Thanks, I'll give that a try!

[grantp22]

fenway, thanks, that worked! I know this is not the ideal solution because the query takes longer, but I guess it's going to have to do for now!