来自日历的mysql数据检查是否已预订

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.