SitePoint Sponsor

User Tag List

Results 1 to 8 of 8
  1. #1
    SitePoint Enthusiast
    Join Date
    Apr 2012
    Location
    Poole, Dorset, United Kingdom
    Posts
    72
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Altering Slot Times To Accomadate A Booking

    Hi All, bit stuck on the logic on how to fix this problem. I have the following slots available for a typical day in the week,
    all times are unixtime stamps but for ease I am showing them in time format. Someone books a slot at 09:40 - 10:24,
    I need to fix the times on line 2 and line 4 to fit the booking in so their is no overlap on times.

    1 09:20:00 09:39:00 AVAILABLE
    2 09:40:00 09:59:00 AVAILABLE
    3 09:40:00 10:24:00 BOOKED
    4 10:00:00 10:19:00 AVAILABLE
    5 10:20:00 10:39:00 AVAILABLE
    6 10:40:00 10:59:00 AVAILABLE

    I'm using
    $query = "SELECT id, staffID, timeSlotID FROM tblServicesTimeSlotsTEST WHERE slotFromTime BETWEEN '$slotFromTime' AND '$slotToTime'
    to grab the id number, but cant work out what to do next

    Any help would be great

  2. #2
    SitePoint Zealot 2ndmouse's Avatar
    Join Date
    Jan 2007
    Location
    West London
    Posts
    196
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I'm probably being far too simplistic here, but is this any help?

    PHP Code:
    if ($PreviousSlotToTime $slotFromTime && $slotToTime $nextSlotFromTime){
          
    //do the booking
      
    }else{
         
    //reject the booking
      

    Detect file changes remotely. SimpleSiteAudit is an early
    warning anti-hacker system which sends an alert on detection.

    PHP Find Orphan Files - Finds all the unreferenced files on your site.

  3. #3
    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)
    Code:
    1 09:20:00 09:39:00 AVAILABLE	
    2 09:40:00 09:59:00 AVAILABLE	
    3 09:40:00 10:24:00 BOOKED	
    4 10:00:00 10:19:00 AVAILABLE	
    5 10:20:00 10:39:00 AVAILABLE	
    6 10:40:00 10:59:00 AVAILABLE
    Despite my previous failure to grasp exactly what you are doing, and given your new vector attacking this problem.

    Are you saying how do I turn the above into:
    Code:
    1 09:20:00 09:39:00 AVAILABLE	
    2 09:40:00 09:59:00 BOOKED	
    3 10:00:00 10:19:00 BOOKED	
    4 10:20:00 10:39:00 BOOKED	 *
    5 10:40:00 10:59:00 AVAILABLE
    (ie any part of a 20 minute slot which is taken is then fully booked itself)

    OR
    Code:
    1 09:20:00 09:39:00 AVAILABLE	
    2 09:40:00 09:59:00 BOOKED	
    3 10:00:00 10:19:00 BOOKED	
    4 10:20:00 10:24:00 BOOKED	
    5 10:25:00 10:39:00 AVAILABLE  *
    6 10:40:00 10:59:00 AVAILABLE
    (ie any part of a 20 minute slot which does not match the end of slot minute exactly, then inserts a new record with the remaining minutes)

    OR

    What you posted you are stuck with, and you just want some PHP to analyse the slots and echo out what remains bookable around this booking?

  4. #4
    SitePoint Enthusiast
    Join Date
    Apr 2012
    Location
    Poole, Dorset, United Kingdom
    Posts
    72
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Hi
    Sorry if I'm not explaining it well, I need some PHP to analyse the slots to make the times either side match up to the booking start and finish
    so in this case
    line 1 is fine,
    line 2 is deleted as it overlaps booking time
    line 4 is deleted as it overlaps booking time
    line 5 start time becomes 10:25

    1 09:20:00 09:39:00 AVAILABLE
    2 09:40:00 09:59:00 AVAILABLE
    3 09:40:00 10:24:00 BOOKED
    4 10:00:00 10:19:00 AVAILABLE
    5 10:20:00 10:39:00 AVAILABLE
    6 10:40:00 10:59:00 AVAILABLE

  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)
    So your question is, you have these values coming from a my mysql database as unix timestamps (but in order to explain what you want we have substituted time stamps)

    Code:
    1 09:20:00 09:39:00 AVAILABLE	
    2 09:40:00 09:59:00 AVAILABLE	
    3 09:40:00 10:24:00 BOOKED	
    4 10:00:00 10:19:00 AVAILABLE	
    5 10:20:00 10:39:00 AVAILABLE	
    6 10:40:00 10:59:00 AVAILABLE
    and then you want the results to appear in a friendlier fashion such as:

    Code:
    1 09:20 to 09:39 AVAILABLE
    2 09:40 to 10:24 BOOKED	
    3 10:25 to 10:39 AVAILABLE
    4 10:40 to 10:59 AVAILABLE
    So, take array of timestamps and analyse it and output meaningful text, have I got it?

  6. #6
    SitePoint Enthusiast
    Join Date
    Apr 2012
    Location
    Poole, Dorset, United Kingdom
    Posts
    72
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I've mucked about and got so far, I've started a new thread as could seem to get the code to post

  7. #7
    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)
    *sigh*

  8. #8
    SitePoint Enthusiast
    Join Date
    Apr 2012
    Location
    Poole, Dorset, United Kingdom
    Posts
    72
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I've droped my code in here if you could take a look

    Slot booked at 9:45 - 10:15
    1. 9:00 - 9:30 available
    2. 9:31 - 10:00 available
    3. 10:01 - 10:30 available

    After booking, row 2 is deleted as booking fills this slot fully,
    row 1 finish time is 1 minute before start of booking and row 3 start time is 1 minute after booking finish time
    1. 9:00 - 9:44 available
    2. booking 9:45 - 10:15
    3. 10:16 - 10:30 available

    I've played around with the code but can figure it all out
    PHP Code:
    // if a timeslot fall between after delete
    $query "SELECT id FROM tblServicesTimeSlotsTEST WHERE slotFromTime BETWEEN '$slotFromTime' AND '$slotToTime' AND
    clientID='
    $clientID' AND staffID='$staffID'";
    $result mysql_query($query,$db);
    $row mysql_fetch_array($result);
    $after_id $row[id];

    if(
    $after_id!="")
    {
    $adjustSlotFromTime $slotToTime 60;
    $fromTimeAdjust date("H:i:s",$adjustSlotFromTime);
    $query "UPDATE tblServicesTimeSlotsTEST SET slotFromTime='$adjustSlotFromTime', fromTime='$fromTimeAdjust' WHERE id='$after_id' AND clientID='$clientID'";
    echo(
    "After Slot : $query<br>");
    mysql_query($query,$db);

    }

    // if a timeslot fall between before delete
    $query "SELECT id FROM tblServicesTimeSlotsTEST WHERE slotToTime BETWEEN '$slotFromTime' AND '$slotToTime' AND
    clientID='
    $clientID' AND staffID='$staffID'";
    $result mysql_query($query,$db);
    $row mysql_fetch_array($result);
    $before_id $row[id];

    if(
    $before_id!="")
    {
    $adjustSlotToTime $slotFromTime 60;
    $ToTimeAdjust date("H:i:s",$adjustSlotToTime);
    $query "UPDATE tblServicesTimeSlotsTEST SET slotToTime='$adjustSlotToTime', ToTime='$ToTimeAdjust' WHERE id='$before_id' AND clientID='$clientID'";
    mysql_query($query,$db);
    echo(
    "Before Slot : $query<br>");

    Last edited by ServerStorm; Feb 4, 2013 at 19:35. Reason: please use [code][/code] tags around your code


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
  •