[SOLVED] 'Availability table'

[keeps21]

I have an application which allows users to make a booking for a room. user picks the date, start time and end time.

 

What I want to do is create a colour coded 'table of availability'.

 

Basically draw a table, showing 'blocks' of 1 hour intervals, if that hour has been booked it is unavailable, color red, otherwise color green.

 

The bookings are stored in the database table 'bookings'

'booking_id |  date          | start_time | end_time | user_id | room_id |
-------------------------------------------------------------------
1             | 2009-09-30 | 16:00:00   | 17:00:00 |    1      |  1        |
2             | 2009-09-30 | 12:00:00   | 14:00:00 |    1      |  1        |

 

I'm pulling the data from the database for that room and date into an array

 

SELECT * 
FROM bookings
WHERE date= 2009-09-30

 

would give an array:

array( '0' => array('booking_id'           => '1', 
                          'date'              => '2009-09-30',
                          'start_time'       => '16:00:00', 
                          'end_time'         => '17:00:00',
                          'user_id'            => '1', 
                          'room_id            => '1', 
                        );
          '1' => array('booking_id'           => '2', 
                          'date'              => '2009-09-30',
                          'start_time'       => '12:00:00', 
                          'end_time'         => '14:00:00',
                          'user_id'            => '1', 
                          'room_id            => '1', 
                        );
       ) ;

 

what i need to do is write out a table where each cell represents an hour in time and color cells in which represent hours between start_time, and end_time in the array using a loop.

 

I just can't get my head around the loop required.

 

Any help would be greatly appreciated.

 

Thanks

[RussellReal]

this is quite interesting

 

try making the query like this:

 

SELECT *

FROM bookings

WHERE date= 2009-09-30

ORDER BY room_id ASC, start_time ASC

 

to keep all the rooms in order than all the start_times in order

 

then loop through the results with something like this

<?php

$prev_room = -1;

$hours = array();

while ($r = mysql_fetch_assoc($theQuery)) {

if ($r['room_id'] != $prev_room) { $hours[$r['room_id']] = array_combine($k = range(0,23),array_fill(0,23,'available')); $prev_room = $r['room_id']; }

$e = explode(':',$r['start_time'].':'.$r['end_time']);

$s = $e[0];

$e = $e[3];

while ($e > $s) {

$hours[$r['room_id']][$e - 1] = 'not available';

$e++;

}

}

print_r($hours);

?>

then $hours should hold an array of available and not-available

[RussellReal]

sorry don't use the above.. I made a boo-boo

 

<?php
$prev_room = -1;
$hours = array();
while ($r = mysql_fetch_assoc($theQuery)) {
if ($r['room_id'] != $prev_room) { $hours[$r['room_id']] = array_combine(range(0,23),array_fill(0,23,'available')); $prev_room = $r['room_id']; }
$e = explode(':',$r['start_time'].':'.$r['end_time']);
$s = $e[0];
$e = $e[3];
while ($e > $s) {
	$hours[$r['room_id']][$e - 1] = 'not available';
	$s++;
}
}
print_r($hours);
?>

 

use that instead

[keeps21]

Thanks very much. 

[RussellReal]

ANYTIME! <3 click 'solved' though