Automatically Insert Time Slots

G’day Everyone,

Just wondering if someone could give me a hand with this part of a booking system I am working on.

I have a table with teachers: id, teacher_id, name.
I have another table bookings: id, teacher_id, time

What I would like to do is automatically generate 5 min time slots between a given time (eg. 6pm and 8pm) for each teacher. Therefore each teacher from the teachers table would have 24 blank time slots allocated to them in the bookings table.

Can anyone put me on the right track with this???

Hi Dan,

So, you want your bookings table to look something like:

id, teacher_id, time
1, 1, 18.00
2, 1, 18.05
3, 1, 18.10
4, 1, 18.15

etc…

Is that right? If so do you need to do it on the fly, ie a time is submitted, and then 5minute slots are allocated from there, or it will always be between 18.00 and 20.00.

If it’s the latter, I’d make up a table called slots (id, slot) values (1, 18.00), (2, 18.05), (3, 18.10) etc… then have a look-up table teacherslots(teacherid, slotid), and link teachers to time slots that way.

If it’s on the fly, then I would do it in PHP and calculate the time slots and add them to an array. You can then generate the sql query using that array and submit it.

Any good?

G’day Micky

Yeah I would like to do it on the fly, eg to and from times are selected from drop down lists. So I guess I would need to somehow calculate the amount of times slots and put into an array then?

Also could that teacherslots lookup table handle the bookings do you think?

Would that be the best way to go about it?

Cheers, thanks for your help

Your bookings table should only be filled if the time is actually booked.

Timeslots in 5 minute intervals?


$start = strtotime("8:00");
$mins = range(0,7200,300); //Measured in seconds.
foreach($mins AS $min) {
 $time = date('H:i',$start+$min);
 echo $time."<br>";
}

Here are some discussions on a recent similar post, which you will see is pretty warts’n all, but might lend an insight if you wanted to follow up on Starlion’s advice

Your bookings table should only be filled if the time is actually booked.

To create an sql query using StartLions code, you could try:


$sql="INSERT INTO booking (time) VALUES ";
$start = strtotime("8:00");
$mins = range(0,7200,300); //Measured in seconds.
foreach($mins AS $min) {
 $time = date('H:i',$start+$min);
 $sql .="('" . $time . "'), ";
}
$sql = rtrim($sql, ',');  //remove trailing whitespace and comma

that should work as long as your booking id is set to auto_increment.

I still dont see why you’d need to fill a table with entries that way, but w/e.

Thanks Micky that’s similar to what I have done and seems to work well.

StarLion, at the moment I am pre populating a booking table with empty time slots for each person and when a booking is made updating that record as opposed to inserting a new record.

Do you think there is a better way to achieve this?

Yup. Only put the records into the table when they’re booked; that way you dont fill your database with inactive entries. (24 entriesperday, X people, Y days, Z bytes per entry… you do the math on the potential wastage)

Retrieve the records using a SELECT, and then it’s just a comparison to see if a timeslot is booked.

A smaller table queries faster. (makes declarative statement and waits to be shouted down by MySQL gurus)

That is where I am getting stuck. If I pre populate the bookings table it is a simple query to retrieve the “available” times. I can’t get my head around doing a comparison like you have suggested…

If I have these tables:

timeslots: id, time
teacherslots: slotid, teacherid
bookings: id, teacherid, parentid, slotid

How could I get a list of available times?

A = B + C

A = All possible times
B = Booked times
C = Unbooked times.

I gave you the code for A above.
B is your SELECT.
Find C. (array_diff…)

Hey Dan,


sql = "SELECT time, teacherslots.teacherid FROM timeslots LEFT JOIN teacherslots ON timeslots.id = slotid WHERE teacherslots.teacherid IS NULL"

Would give you all the slots that do not have a teacherid assigned to them, i.e. not booked.

StarLion, below is my code. I am creating an array off all times then another one with booked times, then using array_diff


//GET ALL TIMES
$sql = "SELECT slotid FROM teacherslots
		WHERE teacherid = 'ANG'";

$result = mysqli_query($link, $sql);

if (!$result)
{
$error = 'Database error getting available times!';
include 'error.html.php';
exit();
}

while ($row = mysqli_fetch_array($result))
{
$alltimes[] = array('slotid' => $row['slotid']);
}

//GET BOOKED TIMES


$sql = "SELECT slotid FROM bookings
		WHERE teacherid = 'ANG'";

$result = mysqli_query($link, $sql);

if (!$result)
{
$error = 'Database error getting available times!';
include 'error.html.php';
exit();
}

while ($row = mysqli_fetch_array($result))
{
$bookedtimes[] = array('slotid' => $row['slotid']);
} 

$available = array_diff($alltimes, $bookedtimes);

	print_r($available);

But $available contains nothing? Any ideas

OK, I think I have this working now. it must have been something to do with the way I was creating the 2 arrays. I am using the same code as above expect the arrays are now like this inside the while loop:


while ($row = mysqli_fetch_array($result)) {
 $alltimes[$row[0]] = $row[0];
}

and

while ($row = mysqli_fetch_array($result)) {
	 $bookedtimes[$row[0]] = $row[0];
}

I then use array_diff($alltimes, $bookedtimes) and that gives me the result I am after…

Does that look correct to you guys. I would love to hear your thoughts on that and any other advice you might have to improve it.

What does the teacherslots table represent?

It is a lookup table holding all the available times for each teacher. I guess looking at it know I don’t need that, I could get that from a query like this:

SELECT time, teacherid FROM timeslots, teachers WHERE teacherid = ‘ZZZ’

Store that in an array, then the booked times from the booking table in the second array and then use array_diff.

Okay so each teacher will have different timeslots, or are they all 6-8 each day? Will they all have one ‘period’? (IE: one slot from X to Y each day, at 5 minutes per booking)? Will it vary from day to day?

I’m trying to get a decent picture of what data you need to model.

Each teacher will have the same time slots each day and for the same period (5 mins).

So why store them in the database at all?

If all teachers have the same start and end time, use PHP to generate the slots when needed using the code above.

If they dont, store a start time on your teachers table, and retrieve that to use for $start in the code above.

In either case, you should not need the teacherslots table.