Order by day?

[George Botley]

Hello,

 

 

I have classes in a database with no set UNIX date, just the day like Wednesday and in two other columns the start and end dates.

 

 

I want to be able to order by the day first and then by end_time but php orders the day column by spelling and not the day it holds in chronological order.

 

 

Is there anyway to change the query to order the day column as a date?

 

 

See the query below?

 

 

$query = "SELECT * FROM zumba_timetable WHERE end_time>'$current_time' ORDER BY day, end_time ASC LIMIT 0,1";

[George Botley]

Just marked this as complete, but it isn't after confirming....

 

 

Any ideas?

[Pikachu2000]

This is why dates should be stored in the database in YYYY-MM-DD HH:MM:SS format. Then you can use the functions to calculate/manipulate the values. All I can think of for you to do (if changing the format is truly not an option) is to either add a field to the table to hold a number 0-6 to indicate the sort order, or prepend a digit to the name of the day in the table, order by the first character of the field, and return a substring of the field containing all but the first character. Either way is pretty clunky.

[kicken]

Another option is to use a case clause to convert the days to a number.  Still not great, ideally you would store the day as a number, then convert it to a textual date for display, rather than the other way around (assuming you can't use an actual datetime value for some reason)

 

SELECT
day, 
end_time,
CASE 'Tuesday'  
	WHEN 'Sunday' THEN 0
	WHEN 'Monday' THEN 1
	WHEN 'Tuesday' THEN 2
	WHEN 'Wednesday' THEN 3
	WHEN 'Thursday' THEN 4
	WHEN 'Friday' THEN 5
	WHEN 'Saturday' THEN 6
END as dayNum
FROM blah
ORDER BY
dayNum,
end_time

[Chud37]

I was going to say have a number system, like 1 = sunday 2 = monday etc.  Or you could use time() as a timestamp, then work out the day with date("d",$day) or something like that.

 

Zumba is everywhere! Someone i know just became a Zumba instructor.

 

[PFMaBiSmAd]

Use either one of the following for a 'custom' sort order -

 

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

 

or

 

ORDER BY FIND_IN_SET(day,'Sunday,Monday,Tuesday,Wednesday,Thursday,Friday,Saturday'),end_time

[George Botley]

Thanks for all of your comments guys,

 

 

Each method is good in solving my problem although PFMaBiSmAD's suited my plan best.

 

 

The events repeat on the same day of every week, so a timestamp would not work for simply saying Monday.. or would it?

 

 

George.