Day view calendar/mysql entries

[goosebriar]

I am trying to create a day view of a lab resource reservation system.

 

So far, I've been able to generate a day view table with php, using variables for start time, end time and periods.  Periods represent intervals on the day view.

 

I have two columns in the table:  the first one starts at the start time (say 8:00) and there is a row for every time until the end time, each row time being increased by the period, ie. 8:00, 8:30, 9:00 where the period is 30 minutes and the end time is 9:00.

 

My second column in the table is filled with unix timestamps representing the date for that particular row, so the 8:00 row on July 1, 2007 is represented by a timestamp of 1183291200.

 

I then created variables to represent start and end times and date of a reserved lab resource.  I did a little switch statement in my second column of my table saying, if that row's timestamp equals the start or end time of my reserved resource, than give it a certain style.

 

All well and good, except I need to tie this into a mysql database where the reservation details (start, end, date) are stored.  Where I am getting fuzzy is how to integrate the query results from the database into my little system.  I would like to have that second column pick up the results from my query and display the reservations, but I'm not sure now to do that.

 

I'd like to post the code here, but I'm not sure how to post php code on these forums without getting yelled at for doing it wrong (I did that once, can you tell?)

 

Any help or advice would be appreciated.

[goosebriar]

I've tried some solutions but am in a looping nightmare.  Any suggestions?

 

$today=date("l, F jS Y");
$dayofweek=date("w");
$month=date("m");
$day=date("d");
$year=date("Y");

$labID=$_GET['labID'];
///////////////////////////////////
$avail=sprintf("SELECT * FROM lrms_res_avail
WHERE resID='$insert_resourceID'
AND Day='$dayofweek'");
$rs_avail=mysql_query($avail);
$row_rs_avail=mysql_fetch_assoc($rs_avail);
$numrows_avail=mysql_num_rows($rs_avail);
//echo "Numrows: ".$numrows_avail."<br />";

$res_avail=sprintf("SELECT * FROM lrms_resource WHERE labID='$labID'");
$rs_res_avail=mysql_query($res_avail);
$row_rs_res_avail=mysql_fetch_assoc($rs_res_avail);
$numrows_res_avail=mysql_num_rows($rs_res_avail);
/////////////////////////////////
if ($numrows_avail==0) {
/////////////////////////////////
	if ($numrows_res_avail==0) {
		echo "";
	} else {
		echo "The availability for this day and resource have not been entered.\n";
		echo "<br />Please enter availability <a href='lab_resources.php?labID=".$labID."'>here</a>.\n";
	}

/////////////////////////////////
} else { //for numrows avail
/////////////////////////////////
	$startlen=strlen($row_rs_avail['start_time']);
	//make sure start and end time 4 digits
		if ($startlen==3) {
			$start="0".$row_rs_avail['start_time'];
		} else {
			$start=$row_rs_avail['start_time'];
		}

	$endlen=strlen($row_rs_avail['end_time']);
		if ($endlen==3) {
			$end="0".$row_rs_avail['end_time'];
		} else {
			$end=$row_rs_avail['end_time'];
		}
//Determine start and end
	$starthour=substr($start,0,2);
	$startmin=substr($start,2,2);

	$endhour=substr($end,0,2);
	$endmin=substr($end,2,2);

//Convert to timestamp
	$startstamp=mktime($starthour, $startmin, 0, $month, $day, $year);
	$endstamp=mktime($endhour, $endmin, 0, $month, $day, $year);
//Determine number of segments	
	$period=($row_rs_avail['period']);
		//find number of minutes between start and end
			$diffstamp=($endstamp-$startstamp)/60;
			$periodno=$diffstamp/$period;
//start table
	echo "<table width='300' class='table' cellspacing='0'>\n";
  		echo "\t<tr class='green'>\n";
	echo "\t\t<th width='100'>Time</th>\n";
	echo "\t\t<th width='200'>Reserved</th>\n";
	echo "\t</tr>\n";
//find entries
		$entry=sprintf("SELECT * FROM lrms_entry 
		WHERE resourceID='$insert_resourceID'
		AND start >='$startstamp'
		AND start <'$endstamp'");
		$rs_entry=mysql_query($entry);
		$row_rs_entry=mysql_fetch_assoc($rs_entry);
		$numrecords=mysql_num_rows($rs_entry);

		//create range
	for($i=0;$i<mysql_numrows($rs_entry);$i++) {
                for($j=0;$j<mysql_num_fields($rs_entry);$j++) {
                     $retval[$i][mysql_field_name($rs_entry,$j)] = mysql_result
			($rs_entry,$i,mysql_field_name($rs_entry,$j));
                }//end inner loop
		   }//end outerloop
			//$rowstart=array();
			$rowstart=$row_rs_entry['start'];
			$startrange[]=$rowstart;
			$rowend=$row_rs_entry['end'];
			$endrange[]=$rowend;

		   $retval_count=count($retval);

	//create periods
	for ($i=0; $i<=$periodno; $i++) { //for loop #1
      		$add=($i*$period)*60;
		$newtime=$startstamp+$add;
		$newtime_date=date("G:i", $newtime);

			if (!in_array($newtime, $retval)) {
					echo "\t\t<tr><td align='right'>".$newtime_date."</td><td>".$newtime."</td></tr>\n";
				}

		//loop through retval array
		for ($n=0; $n<$retval_count; $n++) { //for loop #2
				if (in_array($newtime, $retval[$n])) { //if loop#1
					$startval=$retval[$n]['start'];
					$endval=$retval[$n]['end'];
						//echo "Start Value: ".$startval."<br />";
					if ($newtime==$startval) {
						echo "<tr>";
						echo "\t\t<td align='right'>".$newtime_date."</td><td>";
						echo "Start: ";
						echo $retval[$n]['userID']."</td></tr>\n";
					}
					   
					   //create range for middle
						$rowstart=$retval[$n]['start'];
						$rowend=$retval[$n]['end'];
						$rowdiff=($rowend-$rowstart)/60;
						$periodrow=$rowdiff/$period;
					   	
					   	$rangeno=count($range);
					   	for ($j=0; $j<=$periodrow; $j++) {
            					$addnew=($j*$period)*60;
							$range[$j]=$rowstart+$addnew;
            				}

						$range=array($range);
						if (in_array($newtime, $range[0])) {
							echo "<tr>";
							echo "\t\t<td align='right'>".$newtime." ".$newtime_date."</td><td>";
							echo "Middle</td></tr>\n";
						}

					if ($newtime==$endval) {
						echo "<tr>";
						echo "\t\t<td align='right'>".$newtime_date."</td><td>";
						echo "End: ";
						echo $retval[$n]['userID']."</td></tr>\n";
					}

				} //end if loop#1
			} //end  for loop #2
		} //end  for loop #1
echo "</table>";
////////////////////////////////
} //end if num rows res avail
/////////////////////////////////