PHP display bookings loop

[Technocracy]

Hey guys,

 

I couldn't really think of a decent topic title for this but I hope by the end of it you'll know what I mean.

 

Brief background: I work in an IT department in a school. I am in the process of writing a page to show room and resource booking for the current day and for the next day. There are six blocks that bookings can be made (before school, periods one-four, after school). I have written a query to display all the bookings for a given day. I have looped the six available periods to it has a header for them, and have done the same for containers for the bookings.

 

What I am having problems with is getting it to show under the correct period for the start time and end time for the booking. For example, if a booking starts and ends period three, I want it to show just for period three. If it is a daily booking, it goes on all day.

 

My query is this and I am happy with it - phpmyadmin display everything I want and the start time and end time are correct.

 

(
SELECT
	`bno` AS `bookingid`,
	UPPER(`whofor`) AS `name`,
	`timestart`,
	`timeend`,
	`where` AS `room`,
	'' AS `comments`,
	TRIM(TRAILING '||' FROM `What1`) AS `equipment`,
	IF(`date` = '1', '1', '1') AS `type`
FROM
	`booked`
WHERE
	UNIX_TIMESTAMP(CONCAT_WS('-', `year`, `month`, `day`)) = '{$time}'
	AND `active` = 'yes'
	AND `timestart` != 'all'
)
UNION (
SELECT `bno` AS `bookingid`,
UPPER(`whofor`) AS `name`,
'1' AS `timestart`,
'6' AS `timeend`,
`where` AS `room`,
'' AS `comments`,
TRIM(TRAILING '||' FROM `What1`) AS `equipment`,
IF(`date` = '1', '1', '1') AS `type`
FROM
`booked`
WHERE
UNIX_TIMESTAMP(CONCAT_WS('-', `year`, `month`, `day`)) = '{$time}'
AND `active` = 'yes'
AND `timestart` = 'all'			
)
UNION (
SELECT `bid` AS `bookingid`,
UPPER(`name`),
`period`,
`period`,
IF(`room` = '8', 'Lower Laptops', 'Middle Laptops') AS `room`,
`comments` AS `comments`,
'',
IF(`timebooked` = '2', '2', '2') AS `type`
FROM
`bookedrooms`
WHERE
UNIX_TIMESTAMP(CONCAT_WS('-', `year`, `month`, `day`)) = '{$time}'
AND `room` IN(8,9)
AND `name` != 'not available'
AND `active` = 'yes'
)
ORDER BY `timestart`, `type` ASC

 

$time = (isset($_GET['tomorrow'])) ? strtotime('tomorrow 00:00') : strtotime('today 00:00');

 

Just wondering if anybody here has ever done anything similar to this to give me a hand.

 

Thanks in advance!

[Technocracy]

Bump? Am I allowed to do that, I don't know?

 

This is what I am trying to get: