Order by month name

[gavin.sibley]

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

[PFMaBiSmAd]

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')

[gavin.sibley]

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());

 

[PFMaBiSmAd]

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.)

[gavin.sibley]

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.

[PFMaBiSmAd]

You are missing a single quote at the start of the 'December' string.

[gavin.sibley]

Works perfectly thank you very much.