Calculating time availability and displaying it?

[Solarpitch]

Hey,

 

I posted a similar topic this morning but I think it was a little complicated to understand. I'm trying to display a list of teetimes for a golf club with a number of available slots for that specific time. (Slots 0 - 4 )

 

0 = Fully Booked

4 = All Slots Available

 

Heres an example of my time_sheet table ...

 

ID       Date         Time          Slot1       Slot2        Slot3        Slot4
---      --------    --------     ------     --------   -------     -------
1        06242008   7:30          Paul        Fred
2        06242008   10:00        Ger         Alan         Lisa

 

What I then do is when a user chooses a date, say today 24-6-08, I want the code to check the table and see if there are any booking already in for that date, in this case there are 2 as above. I then want it to be able to calculate that amount of free slots available for those times and default the rest of the time slots for that day to 4.

 

The image below will show you what is being produced at the min. I set a start and finish time, so 7:00am to 11:50am and calculate 10 min intervals inbetween (Teetime every 10 min) I'm just having a problem setting the available slots.

 

 

Here is the function I have that does the above

 

<?php


function check_teetimes($teedate);
{

$sql = "select * from golfpro_time_sheet where realdate= '".$teedate."'";

$myinfo = array();
$result = mysql_query($sql) or die(mysql_error());

//This is how I calculate available slots. I check 4 columns in the database, if it is empty then that slot is available to be booked so increment the availability of that time. 

	while(($row = mysql_fetch_row($result)) != false) {

	$slot1 = $row[3];
	$slot2 = $row[5];
	$slot3 = $row[7];
	$slot4 = $row[9];

	$avail_slots = 0;

	if($slot1 == "") {$avail_slots = $avail_slots + 1;}
	if($slot2 == "") {$avail_slots = $avail_slots + 1;}
	if($slot3 == "") {$avail_slots = $avail_slots + 1;}
	if($slot4 == "") {$avail_slots = $avail_slots + 1;}

// Set the start and finish time and calcaulate a 10 min interval

$st = strtotime(date('Y-m-d') . "7:00");
$en = strtotime(date('Y-m-d') . "11:50");

$int = 10 * 60;

        $myinfo[0] = "<table id='table'><tr>";

        $last = 07;
 for ($i = $st; $i <= $en; $i += $int) {
 $hour = date('h:i a',$i);
 $d1 = split(":",$hour);
        $breakhour = $d1[0];

// This will just tell the code to break to a new line when it see's a new hour. 


        if($last && $last != $breakhour){ //new hour
        $myinfo[0] .= "</tr><tr>";
        $last = $breakhour;
        }

$time = date('h:i a',$i);
        $time_2 = date('G:i',$i);
//if NOT in the exclude array then add a value
if( !in_array($time, $exclude) ) 
    {
	$myinfo[0] .= "<td id='slot_bk' ONMOUSEOVER=\"popup('$player','yellow')\"; ONMOUSEOUT='kill()'>$time<br>Slots:$t</td>";
    }
}	

}

$myinfo[0] .= "</tr></table>";

return $myinfo;
}

}


?>

 

 

I've spent the day trying to work this out so I could really do with some help.

 

Cheers