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)

    Checking database for a timeslot range

    Hi All
    I'm really stuck on a problem, posted a similar question but not getting anywhere trying to figure it out.
    I have a table of time slot ranges ie 9:00 - 9:30, 9:31 - 10:00, 10:01 - 10:30 etc
    Someone places a booking say 30 minutes at 9:45 I need to slot in a booking slot as 9:45 - 10:15 as booked and adjust the time either side to match up one minute before ie


    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 SpacePhoenix; Feb 4, 2013 at 11:15. Reason: placed php tags around php code

  2. #2
    Keeper of the SFL StarLion's Avatar
    Join Date
    Feb 2006
    Location
    Atlanta, GA, USA
    Posts
    3,747
    Mentioned
    64 Post(s)
    Tagged
    0 Thread(s)
    So... what's the point of the slots, if you're going to allow people to book odd times anyway?
    Never grow up. The instant you do, you lose all ability to imagine great things, for fear of reality crashing in.

  3. #3
    SitePoint Enthusiast
    Join Date
    Apr 2012
    Location
    Poole, Dorset, United Kingdom
    Posts
    72
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    The slots are just time ranges available, different staff members offer different services so each service may be 35 mins or 45 mins
    the idea is if a service will fit in to a time frame that is available and if so, fit the availabilty around so there is no overlap either side.

  4. #4
    Always A Novice bronze trophy
    K. Wolfe's Avatar
    Join Date
    Nov 2003
    Location
    Columbus, OH
    Posts
    2,079
    Mentioned
    53 Post(s)
    Tagged
    0 Thread(s)
    I feel this is more of a DB question, at least at first.. Let's take a step back a little bit, and post a "show create table" (http://dev.mysql.com/doc/refman/5.0/...ate-table.html) result for us on all the tables relevant above.

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

    Talking

    HTML Code:
    id 	int(11) 		 
    clientID 	int(11) 	 
    staffID 	int(11) 		 
    timeSlotID 	int(11) 	  	 
    timeSlotIDTemp 	int(11) 	 	 
    slotDate 	int(11) 	
    slotFromTime 	int(11) 	 
    slotToTime 	int(11) 	 	 
    fromTime 	time 	 
    toTime 	time 	 
    slotStatus 	text 		  	 
    places 	int(11) 		 
    bookingID 	int(11) 	 
    groupID 	int(11) 		 
    recuringID 	int(11) 		 
    LIVE 	text 
    
    id 	clientID 	staffID 	timeSlotID 	timeSlotIDTemp 	slotDate 	slotFromTime 	slotToTime 	fromTime 	toTime 	slotStatus 	places 	bookingID 	groupID 	recuringID 	LIVE
    13 	1359901484 	150 	98 	0 	0 	1361264400 	1361266440 	09:00:00 	09:34:00 	BOOKED	0 	338 	0 	0 	YES
    3 	1359901484 	150 	98 	0 	0 	1361266500 	1361267040 	09:35:00 	09:44:00 	AVAILABLE	0 	0 	0 	0 	YES
    4 	1359901484 	150 	98 	0 	0 	1361267100 	1361267940 	09:45:00 	09:59:00 	AVAILABLE	0 	0 	0 	0 	YES
    5 	1359901484 	150 	98 	0 	0 	1361268000 	1361268840 	10:00:00 	10:14:00 	AVAILABLE	0 	0 	0 	0 	YES
    14 	1359901484 	150 	98 	0 	0 	1361268900 	1361270940 	10:15:00 	10:49:00 	BOOKED	0 	339 	0 	0 	YES
    8 	1359901484 	150 	98 	0 	0 	1361271000 	1361271540 	10:50:00 	10:59:00 	AVAILABLE	0 	0 	0 	0 	YES
    16 	1359901484 	150 	98 	0 	0 	1361350800 	1361351640 	09:00:00 	09:14:00 	AVAILABLE	0 	0 	0 	0 	YES
    15 	1359901484 	150 	98 	0 	0 	1361271600 	1361273640 	11:00:00 	11:34:00 	BOOKED	0 	340 	0 	0 	YES
    11 	1359901484 	150 	98 	0 	0 	1361273700 	1361274240 	11:35:00 	11:44:00 	AVAILABLE	0 	0 	0 	0 	YES
    12 	1359901484 	150 	98 	0 	0 	1361274300 	1361275140 	11:45:00 	11:59:00 	AVAILABLE	0 	0 	0 	0 	YES
    28 	1359901484 	150 	98 	0 	0 	1361351700 	1361356140 	09:15:00 	10:29:00 	BOOKED	0 	341 	0 	0 	
    22 	1359901484 	150 	98 	0 	0 	1361356200 	1361357040 	10:30:00 	10:44:00 	AVAILABLE	0 	0 	0 	0 	YES
    Thanks for any input
    Last edited by SteveWhea; Feb 4, 2013 at 11:47. Reason: Placed table data in code tags so it lines up better

  6. #6
    Always A Novice bronze trophy
    K. Wolfe's Avatar
    Join Date
    Nov 2003
    Location
    Columbus, OH
    Posts
    2,079
    Mentioned
    53 Post(s)
    Tagged
    0 Thread(s)
    How often will someones available time change? Do you need to know what their previous time slots were if they were not booked?

  7. #7
    SitePoint Enthusiast
    Join Date
    Apr 2012
    Location
    Poole, Dorset, United Kingdom
    Posts
    72
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Its more the php analysis to insert a booking time range ie 9:00 - 9:45 and search for times before and after and alter their times
    so they match up ie from 08:00 - 08:59 and 9:46 - 11:00

    eg
    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

  8. #8
    Always A Novice bronze trophy
    K. Wolfe's Avatar
    Join Date
    Nov 2003
    Location
    Columbus, OH
    Posts
    2,079
    Mentioned
    53 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by SteveWhea View Post
    Its more the php analysis to insert a booking time range ie 9:00 - 9:45 and search for times before and after and alter their times
    so they match up ie from 08:00 - 08:59 and 9:46 - 11:00

    eg
    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 was asking because I was getting ready to suggest a slight table change as long as it fits your requirements. I would probably run a table of "available time slots" and then a second table of bookings. The time slot is available if the count of an inner join between the two tables with the correct criteria is null.

    EDIT: Also, I'd change your date/time format on the 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
  •