Checking database for a timeslot range

PHP
1

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

// 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>");
        }
2

So… what’s the point of the slots, if you’re going to allow people to book odd times anyway?

3

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

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/en/show-create-table.html) result for us on all the tables relevant above.

5 
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

6

How often will someones available time change? Do you need to know what their previous time slots were if they were not booked?

7

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

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.