Altering Time Slots To Insert Booking

[Steve_Wh]

Hi All

I'm really stuck on a problem, I'm that desperate I'll pay someone to fix this!

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

}

[PFMaBiSmAd]

You may in fact have a data structure (array/database table) someplace that defines the normal start/stop time slots, but this should be static information that only defines the display grid. You should only store rows in the actual booking database table that indicate when something is booked.

 

All you need to do to display the result is to query for all the booked rows that match the day(s) you are trying to display, retrieve all those matching rows into an array, get the data structure that defines the start/stop time slot grid, iterate over that data structure and for any booked time that has a start/end time that falls in between or spans the start/end time of the current time slot you are trying to display, display the booked state.

[Barand]

try

 

<?php
include("testDBconnect.php");


   $mysqli->query("DROP TABLE SLOT");

   $sql = "CREATE TABLE slot (
    id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
    starttime TIME,
    endtime TIME,
    booked tinyint DEFAULT 0
    )";
   $mysqli->query($sql);

   $sql = "INSERT INTO slot (starttime,endtime) VALUES
    ('09:00:00', '09:30:00'),
    ('09:31:00', '10:00:00'),
    ('10:01:00', '10:30:00'),
    ('10:31:00', '11:00:00'),
    ('11:01:00', '11:30:00'),
    ('11:31:00', '12:00:00')
    ";
   $mysqli->query($sql);


$books = '09:45:00';    // start booking
$booke = '10:15:00';    // end booking

$sql = "UPDATE slot
   SET starttime = '$books', endtime = '$booke', booked=1
   WHERE starttime <= '$booke'
   AND endtime >= '$books'
   ORDER BY starttime
   LIMIT 1";
$mysqli->query($sql);

$dt = new datetime($books);
$less1min = $dt->sub(new DateInterval('PT1M'))->format('H:i:s');
$dt = new datetime($booke);
$plus1min = $dt->add(new DateInterval('PT1M'))->format('H:i:s');

$sql = "UPDATE slot
   SET endtime = '$less1min'
   WHERE endtime <= '$books'
   ORDER BY endtime DESC
   LIMIT 1";
$mysqli->query($sql);

$sql = "UPDATE slot
   SET starttime = '$plus1min'
   WHERE endtime > '$booke'
   ORDER BY endtime ASC
   LIMIT 1";
$mysqli->query($sql);

/* results
+----+-----------+----------+--------+
| id | starttime | endtime  | booked |
+----+-----------+----------+--------+
|  1 | 09:00:00  | 09:44:00 |	  0 |
|  2 | 09:45:00  | 10:15:00 |	  1 |
|  3 | 10:16:00  | 10:30:00 |	  0 |
|  4 | 10:31:00  | 11:00:00 |	  0 |
|  5 | 11:01:00  | 11:30:00 |	  0 |
|  6 | 11:31:00  | 12:00:00 |	  0 |
+----+-----------+----------+--------+
*/

 

So how much would you have paid?