Display 1 header for multiple rows of SQL query

Discussion in 'Web Design and Development' started by memco, Sep 2, 2009.

  1. memco macrumors 6502

    Joined:
    May 1, 2008
    #1
    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:

    Code:
    --------------------------------------
    |             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.
     
  2. angelwatt Moderator emeritus

    angelwatt

    Joined:
    Aug 16, 2005
    Location:
    USA
    #2
    Here's a query for setting up a custom sort order (MySQL)
    Code:
    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.
     
  3. memco thread starter macrumors 6502

    Joined:
    May 1, 2008
    #3
    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).
     

Share This Page