SitePoint Sponsor

User Tag List

Results 1 to 19 of 19
  1. #1
    SitePoint Enthusiast danielando's Avatar
    Join Date
    Feb 2008
    Location
    Melbourne
    Posts
    15
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

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

  2. #2
    SitePoint Addict
    Join Date
    Oct 2009
    Location
    London, UK
    Posts
    382
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    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?

  3. #3
    SitePoint Enthusiast danielando's Avatar
    Join Date
    Feb 2008
    Location
    Melbourne
    Posts
    15
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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
    Last edited by danielando; Jun 11, 2011 at 20:55. Reason: addition

  4. #4
    Keeper of the SFL StarLion's Avatar
    Join Date
    Feb 2006
    Location
    Atlanta, GA, USA
    Posts
    3,748
    Mentioned
    71 Post(s)
    Tagged
    0 Thread(s)
    Your bookings table should only be filled if the time is actually booked.

    Timeslots in 5 minute intervals?

    PHP Code:
    $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>";


  5. #5
    SitePoint Wizard silver trophybronze trophy Cups's Avatar
    Join Date
    Oct 2006
    Location
    France, deep rural.
    Posts
    6,869
    Mentioned
    17 Post(s)
    Tagged
    1 Thread(s)
    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.

  6. #6
    SitePoint Addict
    Join Date
    Oct 2009
    Location
    London, UK
    Posts
    382
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    To create an sql query using StartLions code, you could try:

    PHP Code:
    $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.

  7. #7
    Keeper of the SFL StarLion's Avatar
    Join Date
    Feb 2006
    Location
    Atlanta, GA, USA
    Posts
    3,748
    Mentioned
    71 Post(s)
    Tagged
    0 Thread(s)
    I still dont see why you'd need to fill a table with entries that way, but w/e.

  8. #8
    SitePoint Enthusiast danielando's Avatar
    Join Date
    Feb 2008
    Location
    Melbourne
    Posts
    15
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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?

  9. #9
    Keeper of the SFL StarLion's Avatar
    Join Date
    Feb 2006
    Location
    Atlanta, GA, USA
    Posts
    3,748
    Mentioned
    71 Post(s)
    Tagged
    0 Thread(s)
    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*)

  10. #10
    SitePoint Enthusiast danielando's Avatar
    Join Date
    Feb 2008
    Location
    Melbourne
    Posts
    15
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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?

  11. #11
    Keeper of the SFL StarLion's Avatar
    Join Date
    Feb 2006
    Location
    Atlanta, GA, USA
    Posts
    3,748
    Mentioned
    71 Post(s)
    Tagged
    0 Thread(s)
    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...)

  12. #12
    SitePoint Addict
    Join Date
    Oct 2009
    Location
    London, UK
    Posts
    382
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    Hey Dan,

    Code:
    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.

  13. #13
    SitePoint Enthusiast danielando's Avatar
    Join Date
    Feb 2008
    Location
    Melbourne
    Posts
    15
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by StarLion View Post
    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...)
    StarLion, below is my code. I am creating an array off all times then another one with booked times, then using array_diff

    Code:
    //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

  14. #14
    SitePoint Enthusiast danielando's Avatar
    Join Date
    Feb 2008
    Location
    Melbourne
    Posts
    15
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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:

    Code:
    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.

  15. #15
    Keeper of the SFL StarLion's Avatar
    Join Date
    Feb 2006
    Location
    Atlanta, GA, USA
    Posts
    3,748
    Mentioned
    71 Post(s)
    Tagged
    0 Thread(s)
    What does the teacherslots table represent?

  16. #16
    SitePoint Enthusiast danielando's Avatar
    Join Date
    Feb 2008
    Location
    Melbourne
    Posts
    15
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by StarLion View Post
    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.

  17. #17
    Keeper of the SFL StarLion's Avatar
    Join Date
    Feb 2006
    Location
    Atlanta, GA, USA
    Posts
    3,748
    Mentioned
    71 Post(s)
    Tagged
    0 Thread(s)
    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.

  18. #18
    SitePoint Enthusiast danielando's Avatar
    Join Date
    Feb 2008
    Location
    Melbourne
    Posts
    15
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Each teacher will have the same time slots each day and for the same period (5 mins).

  19. #19
    Keeper of the SFL StarLion's Avatar
    Join Date
    Feb 2006
    Location
    Atlanta, GA, USA
    Posts
    3,748
    Mentioned
    71 Post(s)
    Tagged
    0 Thread(s)
    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.


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •