Display 1 header for multiple rows of SQL query

[memco]

I have several tables where you'll find common data (e.g. a course schedule with several classes on Monday, Tuesday, etc.) What I'm curious about is how I can generate a table structure and query to best display this. One Solution is something like this:

--------------------------------------
|             Class list             |
--------------------------------------
|                Monday              |
--------------------------------------
|col1 | col2 | col 3 | col 4 | col 5 |
--------------------------------------
|col1 | col2 | col 3 | col 4 | col 5 |
--------------------------------------
|                Tuesday             |
--------------------------------------
|col1 | col2 | col 3 | col 4 | col 5 |
--------------------------------------

The data is currently stored like this: day, start time, end time, course name, course number. The solution I have in place right now is to compare the current record's value for "day" with the one from last record and if it's different, print out a new header.

 

[angelwatt]

Here's a query for setting up a custom sort order (MySQL)

SELECT * FROM tbl
ORDER BY FIELD(day, 'Monday', 'Tuesday', 'Wednesday', 'Thursday', 'Friday');

Checking the previous day with the current one is a good way to check when you've moved to the next set of classes. I've done similar in the past for similar task. Not sure if there may be a better way. You could always break things up into multiple queries and do a week day at a time.

One alternative for the HTML table layout; Move the week day name to the first column and use a rowspan attribute to let it go down far enough. I'd recommend using alternating background colors for each day to help separate them visually. Knowing what value for the rowspan to use could be tricky though.

 

[memco]

I had considered the multiple queries, but didn't want the performance hit of opening so many connections. I considered the rowspan idea, but then I'd get stuck with multiple queries again as I'd have to get the count of the number of rows returned for each day. Thinking about it further though, I suppose

If I implement one of these do you think it'd be prudent to set up a function to pool all queries and run them at once? Right now I have a function setup to take in a query and the name of the database create an ADO connection, execute the query then destroy the connection and return the result as an array (using ASP and Access).