Altering Slot Times To Accomadate A Booking

PHP
1

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

I’m probably being far too simplistic here, but is this any help?

if ($PreviousSlotToTime < $slotFromTime && $slotToTime < $nextSlotFromTime){
      //do the booking
  }else{
     //reject the booking
  }
3 
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:


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


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

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

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)


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:


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

I’ve mucked about and got so far, I’ve started a new thread as could seem to get the code to post

7

sigh

8

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


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