gavin.sibley Posted March 14, 2012 Share Posted March 14, 2012 I need to order a query by event_year and then event_month. the event_month field in the database uses the month names and not numbers (january, february, march etc). How can i order by these? thanks, gavin Quote Link to comment Share on other sites More sharing options...
PFMaBiSmAd Posted March 14, 2012 Share Posted March 14, 2012 You can create a custom ORDER BY term by using a FIELD() statement - ORDER BY FIELD(event_month,'January','February','March', list out the remainder of the month names here...,'December') Quote Link to comment Share on other sites More sharing options...
gavin.sibley Posted March 14, 2012 Author Share Posted March 14, 2012 thanks for the reply. i have tried the code you reccomended but cant seem to get it to work, i am completely new to PHP. i cant see what im doing wrong, any idea? the code is below. $getrecen = mysql_query("SELECT * FROM `temp_users` WHERE `email` NOT LIKE 'admin' AND `refresh_quote` = '1' ORDER BY active ASC, event_year ASC, FIELD(event_month,'January','February','March', 'April', 'May', 'June', 'July', 'August', 'September', 'October', 'November', December'), event_day ASC LIMIT $LimitValue, $LIMIT") or die(mysql_error()); Quote Link to comment Share on other sites More sharing options...
PFMaBiSmAd Posted March 14, 2012 Share Posted March 14, 2012 cant seem to get it to work Exactly what did it do vs what you expected? A) I (we) recommend that you form you query statement in a php variable, then echo it to make sure it contains what you expect. B) You should be storing your event date in one column as a mysql DATE data type (YYYY-MM-DD format.) Quote Link to comment Share on other sites More sharing options...
gavin.sibley Posted March 14, 2012 Author Share Posted March 14, 2012 i was hoping i could have the event dates in order displayed in a table. with the code as it is with the changes you suggested i get the following syntax error. You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ''), event_day ASC LIMIT 0, 20' at line 1 unfortunately im not a PHP person and didnt build the site, i am having to fix it as the initial guy who built it has left. Quote Link to comment Share on other sites More sharing options...
PFMaBiSmAd Posted March 14, 2012 Share Posted March 14, 2012 You are missing a single quote at the start of the 'December' string. Quote Link to comment Share on other sites More sharing options...
gavin.sibley Posted March 14, 2012 Author Share Posted March 14, 2012 Works perfectly thank you very much. Quote Link to comment 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.