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.
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.
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?
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.
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…
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.
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.