is it possible to sort a query by day of the week?

[LJackson]

I have a query which i would like it to pullout the lessons ordered by day of the week ie. monday first and sunday last

if i use ASC or Desc its alphabetic and the days come out muddled.

here is my current query
$classesList = "SELECT * FROM clubs WHERE area = '$area' AND location = '".$venue."' ORDER BY trainingNight DESC";

and its returning wednesday,monday, friday instead of monday, wednesday, friday

thanks
Luke

[Len Whistler]

I would use Numeric representation of the day of the week - 0 (for Sunday) through 6 (for Saturday) - in the database and use PHP to output the full name after it's been ordered.


-------

[kbluhm]

Check out DAYOFWEEK():
http://dev.mysql.com/doc/refman/5.1/en/date-and-time-functions.html#function_dayofweek

If `trainingNight` is a DATE or DATETIME field, you could add this function/alias to your select statement:
SELECT *, DAYOFWEEK( trainingNight ) AS weekdayNum FROM clubs...
Then alter your ORDER BY:
... ORDER BY weekdayNum ASC
You could then also use DAYNAME() to get the... day name. No need for extra data in the DB.

[LJackson]

Hi,

thanks for your replys :)

@kbluhm - unfortunatly trainingNight is a day text field e.g Monday so i assume it wont work with DAYOFWEEK()?

@Len Whistler - thanks for the suggestion mate, the only problem i can see with this is it might confuse the admin person who might be adding new data?

thanks for the suggestions
Luke

[kbluhm]

There's still hope for you. :) You can use a CASE clause and define the numbers right in the query:
SELECT *,
CASE trainingNight
WHEN 'Sunday' THEN 1
WHEN 'Monday' THEN 2
WHEN 'Tuesday' THEN 3
WHEN 'Wednesday' THEN 4
WHEN 'Thursday' THEN 5
WHEN 'Friday' THEN 6
WHEN 'Saturday' THEN 7
ELSE NULL
END as weekdayNum

FROM clubs ...

And then:

... ORDER BY weekdayNum ASC

[CFMaBiSmAd]

You would need to do something like this -

ORDER BY FIELD(trainingNight,'Monday','Tuesday','Wednesday','Thursday','Friday','Saturday','Sunday')

Edit: or see kbluhm's post above.

[LJackson]

Thanks kbluhm, CFMaBiSmAd both your suggestions work :)

really appreciate your help
Luke