database driven calendar problem

[ptaylor]

I am trying to write out a calendar style html table with data in each cell, and with a column on the left that shows the times.

 

Basically it should look like this when written out to the webpage:

 

Time    Mon    Tues    Wed    Thur    Fri    Sat

8:00    data    data2

9:30              data3              data4   

10:00                        data5            data6

11:00  data7              data8

 

The times on the left will vary each month, and the 'data' (these are actually fitness classes held each month) will appear on various days of the week.

 

There is no need to toggle from one month to the next and such, it's just a listing of the current month to display the classes held on which days and at which times.

Very easy to do statically in html, but a bit more challenging dynamically.

 

Using MySQL, the table that holds the data looks like this:

 

schedule_id     class_time     class_title     day_order       
1               08:00           data                1       
2               08:00           data2               3       
3               09:30           data3               2       
4               09:30           data4               4       
5               10:00           data5               3       
6               10:00           data6               5       
7               11:00           data7               1       
8               11:00           data8               3

 

The day_order field corresponds to the weekday - Mon is 1, Tues is 2, etc.

 

My query looks like this:

 

SELECT schedule_id, class_time, class_title, day_orderFROM `class_schedule` order by class_time, day_order

 

Here is the html/ php code to write it out:

 

 <table border="0" cellpadding="2" cellspacing="0" width="625">	  
<tr>	  
<td class="calheader">Time</td>
<td class="calheader">Mon</td>	  
<td class="calheader">Tue</td>	  
<td class="calheader">Wed</td>	  
<td class="calheader">Thu</td>	  
<td class="calheader">Fri</td>	  
<td class="calheader">Sat</td>	  
<td class="calheader">Sun</td>	  
</tr>      

<? $i=0;	  
$first_time = "";
  
while($i<$num_rows){
  
$sid = mysql_result($sql,$i,"schedule_id");		  
$ctime = mysql_result($sql,$i,"class_time");		  
$cdate = mysql_result($sql,$i,"CURDATE()");		  
$strtime = date("h:i:s",$cdate.$ctime);		  
$ctitle = stripslashes(mysql_result($sql,$i,"class_title"));		 		 
$order = mysql_result($sql,$i,"day_order");	
  		  
if ($ctime != $first_time){	

  if (isset($first_time)){				
  echo "";			 
}	

if ($first_time == ""){	
  
echo "<tr>\n";		  		  
echo "<td class=\"timecell\">". $ctime ."</td>\n";		 
}	
      
$first_time = mysql_result($sql,$i,"class_time");	  		  
}	
  		  		  
$j = 1;		 
while ($j<8 && $first_time != ""){		     					  
if ($order == $j){			  
$thetitle = $ctitle;		   
} else {			   
$thetitle = " ";		  
}	
  
echo "<td class=\"calcell\">". $thetitle ."</td>\n";	

$j++;
	  
}

unset($first_time);		 			
if (!isset($first_time))
{	  
echo "</tr>\n";		
}      
$i++;	  	 
}	  	 

unset($ctitle);	  
unset($thetitle);	  
unset($weekday);	  
unset($sid);	 
unset($clink);	 
unset($order);	 
mysql_close();

 

The above code sort of works, but the trouble I am having is that it is creating a new table row for each class, even if they occur at the same time slot. It should be putting them on the same row, to the right of the time slot across the days of the week.

 

If anyone could assist, that would be wonderful - I am really stuck with this...

Thanks