grantp22 Posted August 22, 2010 Share Posted August 22, 2010 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 Quote Link to comment https://forums.phpfreaks.com/topic/211430-order-by-field/ Share on other sites More sharing options...
fenway Posted August 22, 2010 Share Posted August 22, 2010 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. Quote Link to comment https://forums.phpfreaks.com/topic/211430-order-by-field/#findComment-1102383 Share on other sites More sharing options...
grantp22 Posted August 22, 2010 Author Share Posted August 22, 2010 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 Quote Link to comment https://forums.phpfreaks.com/topic/211430-order-by-field/#findComment-1102397 Share on other sites More sharing options...
fenway Posted August 22, 2010 Share Posted August 22, 2010 You could technically order on STR_TO_DATE( strMonth, '%M %Y' ). Quote Link to comment https://forums.phpfreaks.com/topic/211430-order-by-field/#findComment-1102400 Share on other sites More sharing options...
grantp22 Posted August 22, 2010 Author Share Posted August 22, 2010 Thanks, I'll give that a try! Quote Link to comment https://forums.phpfreaks.com/topic/211430-order-by-field/#findComment-1102403 Share on other sites More sharing options...
grantp22 Posted August 22, 2010 Author Share Posted August 22, 2010 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! Quote Link to comment https://forums.phpfreaks.com/topic/211430-order-by-field/#findComment-1102404 Share on other sites More sharing options...
Recommended Posts
Join the conversation
You can post now and register later. If you have an account, sign in now to post with your account.