Mysql question

PHP
1

For some reason I am having trouble with this solution. I am new to php and mysql, but this still should not be this hard to work out. Any help would be greatly appreciated.
I have a simple sql table called ‘schedule’ with columns ‘id’ ‘classType’ ‘class_start’ ‘class_end’ ‘teacher_name’ ‘day’

It is used to store a simple 7 day school class schedule…meaning someone can fill out the form for when they have a class. For example . On Monday there is a class at 9am to 10am and blablabla is teacher.
I am trying to display the results from monday to sunday with only displaying the day once with all the classes for that day under that day then move onto the next day and again display the day once with all the classes for that day.
Any ideas

2

Are the days based on numeric values of 1-7 or actual days names?

3

First of all, stick to a naming scheme. By calling one column ‘classType’ and another ‘class_end’, you’re going to give yourself a headache remembering how you’ve named them. My personal preference is the former, which is called camelCasing.

What kind of column is ‘Day’? If it isn’t already, I’d recommend it being an unsigned TINYINT rather than VARCHAR, which would simplify things a little. So for example, with a week starting Monday, Monday would be 1 whereas Sunday would be 7.

I’d also recommend classStart and classEnd to be Time columns.

The above two paragraphs would basically make putting the rows into order a little bit easier.

Do a query grabbing everying, ordering by Day ASC primarily, and secondarily classStart ASC. Then it’s just a case of looping through MySQL_Fetch_Assoc, outputting anything extra whenever the day changes (note: you find when the day changes by storing the current day in a variable instantiated outside the loop, then seeing if the new value is the same as the old before you set it again).

4

the days are inserted as Monday, Tuesday…

5

thanks…I usually do as this was a quick write up of my situation and not the real thing. I usually use the underscore for all php and camelCaseing for css so that helps separate the two during design and development.

Little confused…can you give a quick sql of what that would look like or point my to a resource. I want the final display to look something like this …

[U]Monday[/U]                          [U]Teacher[/U]                [U] Class[/U]
6am to 9am                                bla bla bla                         Math
9am to 10am                              bla bla bla                         History

Tuesday                                    
9am to 10am                              bla bla bla                         History 
9am to 10am                              bla bla bla                         Math

Wednesday
6am to 9am                                bla bla bla                         Math
9am to 10am                              bla bla bla                         History

rough example but that is the idea. Day of week title is result of the query as Teacher and Class title are hard coded. Only want the day of week to display once with all the classes under it. You might already understand what i am after but thought I would give it more detail. thanks for the reply

6

Sorry the table is off…title Teacher and Class should be shifted the right.

7

I read it over and I have my brain wrapped around what you are saying. It is the changing the day variable outside the loop that has me a little confused. I think I can handle the rest.

8

[COLOR=#ff6600][B]@Jake Arkinstall:[/B][/COLOR] Wouldn’t using GROUP BY be a better solution for this type of data output?

9

I tried that , but it still gives me a “day” for each result as “day” is in the loop. I just want to move “day” out of the loop and insert as a title for the rest of the loop results for the classes. Just don’t know how to make it change for each day.

10

It depends if you want to run a single query or not. It’s generally a better idea to get the data in query and loop through it; PHP handling raw data row by row seems to be more efficient than requerying.

However, the days are varchars. That changes things.

As you haven’t described the kind of data that ‘class_start’ and ‘class_end’ hold, this may have varying results. I’d actually recommend that you ditch that system unless classes can go from 9.30 to 10.45 or something - if ALL classes stick to certain time periods, give them their own table and allocate the classes to a period ID - which is like telling a class it is in period 3, for example. That was if that period changes, so does the whole timetable, rather than having to change every record with that in.

<?php
$DayQueryFormat = "SELECT * FROM schedule WHERE day = '%s' ORDER BY class_start ASC";
$Days = array('Monday', 'Tuesday', 'Wednesday', 'Thursday', 'Friday', 'Saturday', 'Sunday');
echo '<table>';
foreach($Days as $Day){
    echo '<tr><th>' . $Day . '</th><th>Teacher</th><th>Class</th></tr>';
    $DayQuery = MySQL_Query(sprintf($QueryFormat, $Day));
    while($Class = MySQL_Fetch_Assoc($DayQuery)){
        echo '<tr>';
            echo '<td>' , $Class['class_start'] , ' to ' , $Class['class_end'] , '</td>';
            echo '<td>' , $Class['teacher_name'] , '</td>';
            echo '<td>' , $Class['classType'] ,  '</td>';
        echo '</tr>';
    }
}
echo '</table>';
11

[B]

Should
$DayQuery = MySQL_Query(sprintf($QueryFormat, $Day));
be
$DayQuery = MySQL_Query(sprintf($DayQueryFormat, $Day)); ?

12

I got it working with the above edit. I also edited the foreach to only include Teacher and Class table header only once for the first loop. so far it works, but I still have to fine tune it for more detailed output results…meaning I will be adding a few more columns for the results. So …for now thanks for the help. In the learning stage I usually need a nudge in the right direction before I begin to wrap my brain around what I am doing.

foreach($Days as $Day){
    ?><tr><th><?php  echo $Day ?></th><th><?php if ($Day == 'Monday'){echo 'Teacher';}?></th><th><?php if ($Day == 'Monday'){echo 'Class';}?></th></tr><?php
    $DayQuery = MySQL_Query(sprintf($DayQueryFormat, $Day));
    while($row = MySQL_Fetch_Assoc($DayQuery)){