[RESOLVED] Booking table

blaster_master

I'd like to build a table like the following:
http://www.chem.ubc.ca/local/resource/xrdb/scheduler/bookings.php

The idea is that people book a time slot and the booked slot shows up on the table in a different color, say red (i.e. the non-red rows are the available slots). Now to make my life easier I've decided to let the users choose which date they want to see the availability for, so only a table for that particular day is shown (as oppose to the example booking page above).

The db that I am using is an SQL db (Postgres) to store the booking info on. The following would be a simple pseudo-code:

get the date from user (month/day/year)
fetch all the rows from the db with the selected date
find the reserved time slots
display them on the table in red; the rest of the table is unchanged

The format the booking info are stored is as follows:
username
date booked on (booked_on)
booked date (book_date)
starting booked time (book_from)
ending booked time (book_until)

so basically (book_until - book_from) could represent a reserved interval, which is supposed to show up on the table (for as many entries as needed).

I have been stuck on this for sooo long now... ANY help would be greatly appreciated.

bpat1434

So what does your code look like?

blaster_master

That's the problem! I don;t know how to implement this monster! Here's what I have so far (doesn't have the booking table):

// create book_from and book_until and book_date
$book_from = $book_from_hour.":".$book_from_min;
$book_until = $book_until_hour.":".$book_until_min;
$book_date = $book_month." ".$book_day." ".$book_year;

// see if the entry exists
$check_exist = pg_query("select * from booking where (book_date='$book_date') AND (rh_type='$rh_type)
	((book_from<'$book_from' AND book_until>'$book_from') OR 
	(book_from<'$book_until' AND book_until>'$book_until') OR 
	(book_from>='$book_from' AND book_until<='$book_until'))");
if(!pg_num_rows($check_exist)==0){
	... ERROR: time slot unavailable...
	exit;
}

// make the reservation
$currentdate = getdate(time());
$time = $currentdate["hours"].":".$currentdate["minutes"];
$booked_on = $currentdate["month"]." ".$currentdate["mday"]." ".$currentdate["year"];
$book_now = "INSERT INTO booking (username, booked_on, book_date, book_from, book_until, comments, rh_type)
	VALUES ('".$username."', '".$booked_on."', '".$book_date."', '".$book_from."', '".$book_until."', '".$comments."', '".$rh_type."')";
$book_result = pg_query($book_now);

The above only checks the db to make sure that there's no second entry interfering with the one which is about to be reserved. Then reserves it... I absolutely have no idea how to construct the code for that table. I have been through the entire non-commercial scripts on hotscripts.com and such, but no luck as of now...

bpat1434

Aight, so if let's say it's in a mySQL database.... something like:

<?php
$_POST['date'] = '3/8/2006'; // A mySQL timestamp
$start = 0; // Midnight
$end = 23; // 11 pm

$query = "SELECT startTime, endTime, title FROM reservations WHERE resDate='".mysql_real_escape_string($_POST['date'])."'";
$result = mysql_query($query) or die(mysql_error());

$style = ' style="background-color: #f00;"';

function checktime($num)
{
  $time = str_pad($num, 2, '0', STR_PAD_LEFT);
  $time .= ':00';
  return $time;
}

$i=0;

while($row = mysql_fetch_array($result))
{
  echo '<tr><td><b>'.checktime($i).'</b></td>';
  echo '<td';
  if( (date('H', $row['startTime']) <= $i) && (date('H', $row['endTime'])>=$i) ){ echo $style; }
  echo '>'.$row['title'].'</td></tr>'."\n";
  if($i==$end){ break; } // If we've reached a 24 hour period, we're done....
  $i++;
}
?>

That should get you moving...

blaster_master

thanks very much man. looking at it...

blaster_master

so i finally got the time to try it out... unfortunately the while loop doesn't seem to be doing what it is supposed to do; if in a certain day there're say 4 reservations, then the while loop only executes 4 times and not 24 times. i changed the conditions to:

while($i<=$end)
{...}

but now i can't figure out the fetch_array part... i'd need something like:

while ($row = pg_fetch_array($query))
{...}

but that doesn't work nested within the first loop (it only executes once, when $i=0)

Roger_Ramjet

There are 2 basic approaches to time-tabling:
1. generate a table with a place for every posible time-slot: ie 1 hour slotsx10 hours a day = 10 timeslot cols per day record
2. just record the bookings made: ie each time-slot has it's own record

Which you choose depends on the environment and the application: eg
we built a room booking system for a uni that used the first because the time-table for a whole semester was generated in advance and lecturers then distributed it to their classes. We had a finite set of rooms that could be booked for predetermined periods by a finite set of lecturers running a finite set of courses. So generating the finite set of time-slots was easy and the best solution.

If your booking system is more open ended then the second solution is what you need.

With that second solution, you need a method of generating the matrix of available time-slots to left-join to the bookings made so that you can also display the available time-slots. Now, on a weekly basis that can be done thus:

table weekdays
w_id int not null primary key
dow char(4) not null
1| Mon
2 | Tues
3 | Wed
4 | Thur
5 | Fri

table slots
s_id, int, not null, primary key
start, time, not null
1 | 9:00
2 | 9:30
3 | 10:00
4 | 10:30
... etc

Joining these 2 tables with a cartesian join (,) will produce a matrix of possible time slots for each of the days of the week.

The bookings table then has foreign key columns for both those tables.

booking
b_id
w_id
s_id
cols for other data about who booked it etc

NB this does require sub-query support, or else you are going to have to create a table that holds the data and would need to be re-created if you ever change the days or hours.

// matrix query
$matrix = "SELECT * FROM weekdays , slots";

// used as a subquery to produce the booked and available data
$sql = "SELECT * FROM (SELECT * FROM weekdays , slots) AS m
     LEFT JOIN bookings b ON (m.w_id=b.w_id AND m.s_id=b.s_id)";

Your results are going to look like this

m.w_id | m.dow | m.s_id | m.start | b.b_id | b.w_id | b.s_id | etc
1 | Mon | 1 | 9:00 | null
1 | Mon | 2 | 9:30 | 1 | 1 | 2 | etc
1 | Mon | 3 | 10:00 | 2 | 1 | 3 | etc
1 | Mon | 4 | 10:30 | null

That means that something is booked for mon at 9:30 and for Mon at 10:00 and available for the other slots. Just look for the NULL in your code and colour the table cell accordingly for available slots.

Of course you can, and should, use a column list in the select to reduce the replications in the resultset.

blaster_master

thanks for the help. It gave me a good idea how to approach the problem, which is now solved in a slightly different way:

for every booking row, I created a column called 'bin' (for binary). I then stored the entire day (from hour 00:00 to 23:30, incremented by 30) in binary format where 1's represent 'reserved' and 0's otherwise. So 001110... means booked from 01:00 until 02:00. Then, upon calling the booking table, a PHP script OR's all of the bins of 1 single day and puts the result into an array ($result). Finally:

$c = 0;	// table time counter
$d = 0;	// array index counter
while($c<24)
{
  echo '<table width="165" border="0"><tr><td width="55">'.$c.'</td>';
  echo '<td width="55"';
  if ($result[$d] == '1'){ echo $style; }
  echo '>1st</td>';
  echo '<td width="55"';
  $d++;
  if ($result[$d] == '1'){ echo $style; }
  echo '>2nd</td></tr></table>';
  $c++;
  $d++;
}

I used all the ideas provided on this board (including one in another posting re 'logical OR') in addition to my own to come up with the above.

Anyhow, I appreciate all your help. problem solved!