mysql data from calendar check if booked

Question

I have a database with reservations for items. All reservations have an: ID , item ID, personWhoBookedId, time_from (unixtime) , time-to(unixtime).

I like to display a day per day time schedual from hour to hour, and colour the timethat an item is booked and display the personWhoBookedID on that timeframe.

like:

room1:
5:00 - 6:00: free
6:00 - 7:00: booked by[person]
8:00 - 9:00:free

and

room 2:
5:00 - 6:00: free
6:00 - 7:00: booked by[person]
8:00 - 9:00: booked by[person]

etc

At this time I iterate through the existing items with mysql, then check per timeframe with mysql if there is a booking that equals itemID and timeis between the mentioned timeframe of time-from and time-to

This works but is does a lot of queries on the database.

To increase performance I think I better frist get all the reservations and store them in a multi dimensional array, like this:

     $allreservationsperday = mysql_query("select id, personid, itemid, time_from, time_to FROM reservations where time_from >= '$unixfrom' and time_to < '$unixto'");

 while($reservationarray = mysql_fetch_array($allreservationsperday)){
 $res[$reservationarray ["id"]]["personid"] = $reserveringenarray["personid"];
 $res[$reservationarray ["id"]]["itemid"] = $reserveringenarray["itemid"];
 $res[$reservationarray ["id"]]["time_from"] = $reserveringenarray["time_from"];
 $res[$reservationarray ["id"]]["time_to"] = $reserveringenarray["time_to"];
}

and then display the timeline by a for Loop to loop through the hours of the day But I cannot figure out how to check per hour if there is a reservation in the just formed array.

Any help most welcome! jeroen

Answer 1

just loop through them and check for differences on the next value in line?

foreach($res as $key=>$val){
    if($val['time_from'] == $res[$key+1]['time_from'])
        //this is same hour. lets do something here?
    else
        //this is a new hour. 
}

of course you vill have to check if $res[$key+1] isset also before comparison.

Answer 2

@jonaz: I don't see how to fit this into the script. Currently I tried this (for an overview for boat rentals): A preview of the script below is at this test site

    echo "<br>Overview for today:<br><table cellpadding=0 cellspacing=0 border=1 class='navbar' id='navbar' width='100%'>";
$boten = getBootIdType("2"); //Get all boats that can be rented
foreach($boten as $boten=>$value){ //display a timeline per boat
    echo "<tr>";
    echo "<td>Boat ".$value."</td>";

    $unix_from = strtotime(date("d-m-Y 7:00")); //bookings can be made from 7am to 9pm
    $unix_to = strtotime(date("d-m-Y 21:00"));

    while ($unix_from <= $unix_to) {

     // HERE I WANT TO CHECK IF A BOOKING EXISTS IN THE ARRAY AND DISPLAY THE PERSON ID AS IDENTIFIER
     // and set a different background is there is a booking on that time

        echo "<td bgcolor='$bg'>".date("H:i", $unix_from)."</td>";
        $unix_from = $unix_from + 1800;
    }
    echo "</tr>\r\n";
}
echo "</table>";