MySQL - using expressions in ORDER BY statement

[tompage]

I am currently teaching myself PHP/MySQL to add functionality to a website showing event listings for an entertainment venue. The events in any day can run from 11am until 3am. I have a search script that will return all events by title, time, date and other criteria and users can click on any column heading in the results to sort by that column.

My problem is the event start time column. Sorting the data using "ORDER BY time" puts the post-midnight shows at the beginning of the list not the end.

Does anyone have any ideas as to how I could adapt the ORDER BY part of the SELECT statement to return the post-midnight shows at the end of the list.

I could presumably sort after deducting 4 hours off every time. Would something like ORDER BY (time-4) work (using simplistic shorthand for the "time-4")? I assume I would have to use the DATE_SUB() command but I am not sure about the format.

The time is currently recorded simply as hh:mm:ss in the MySQL database.

Any help would be much appreciated.

[Rottingham]

If I understand the question right, you would want to use the ASC or DESC tags to the ORDER BY statement. Ascending is smallest to largest, descending is largest to smallest.

Ie

SELECT * from entertainment ORDER BY time ASC

For this to work properly though, you would need to include the date of the service as well, so the SQL service can order them properly. If you make the time field type = DATETIME it would be YYYY:MM:DD hh:mm:ss.

[tompage]

Thanks for your quick reply.

I understand the ASC/DESC command, but what I want is to end up with a list of events where the sort order is ascending, so events at 11am are first and events at 11pm are near the end, but those at 1am come even later still. A normal ASC sort would put 1am before 11am.

You suggested adding in the date info as well, but we store date information in separate fields that also store pricing info (ie is that date preview pricing, weekday pricing or weekend pricing). Events generally run for a number of days at the same time every day. Thus we only record one time for each event not a series of date/time fields for each day n which the event takes place.

If someone is searching for all events on 24 May, the results need to actually show all events from 11am on 24 May to 3am on 25 May - they are all treated as "24 May".

Does that make sense?

[Rottingham]

I see. I think you'll still need to simply change that field to DATETIME. Then your WHERE would be

SELECT * FROM events WHERE DATETIME BETWEEN 5/24/05 11:00:00 5/25/05 03:00:00

.

Other than that, I have no solution! Sorry!

Good luck with your project.

[tompage]

Although it seems slightly strange, I suppose you could be right. For all times before midnight, I could insert a chosen arbitrary date into all the start time fields for every event, even if that date bears no relation to the actual dates on which the event will take place.

Then for every event with a post-midnight start time, I insert a date of one day after the arbitrary date chosen above. I never actually need to display that date as I would simply pick out the hh:mm information to display on the results page and only use the date information to make the sorting work.

On the event data entry pages I would have to ensure that the script automatically appended the correct arbitrary date to the start time inserted by the user depending upon whether the start time is midnight-4am or 10am-midnight.

It seems slightly odd and would be neater if I could somehow put an expression into the ORDER BY statement to deal with it, but it might work.

[sonjay]

Maybe instead of messing around with arbitrary dates or subtracting 4 hours from each time or some such, you should simply insert the events into the database using the actual, technically correct date: i.e., an event at 1 a.m. on May 25 is inserted into the db as 1 a.m., May 25 (not May 24).

Then in your SELECT statements, when someone wants to see "Events on May 24," your query will ask for events between 4am May 24 and 3am May 25. Your ORDER BY results will be correct, your actual dates will be correct, and you won't be looking at this code again in a year and scratching your head, wondering "What WAS I thinking when I wrote that?"