How to exclude timeslot in dropdown list | PHP

[JacoSwanepoel]

Good Afternoon everyone

I am really hoping that someone can assist me with a problem that I am facing. I am 53 and trying to learn myself new things and one of them is PHP which is not the easiest thing for me to understand. I am "trying" to build a online salon booking system for my wife and i am having difficulty excluding time slots that is already booked

I have a database table called sb_bookings and the structure is as follows (For now at least)

| id | booking_date  | booking_time     | booking_title        | booking_start               | booking_end               |
| 1  | 2022-05-15     | 10:00:00             | Customer Name  | 2022-05-15 10:00:00  | 2022-05-15 11:00:00  |

For the sake of this example let’s say that I only have this 1 booking on 2022-05-15 at 10:00am. If a user selects a date on the datepicker & clicks the “Select Date” button it opens a modal using JS and passes the selected date to the modal. I know the start hours for the day & the end hours and want to display the hours in a dropdown list. This I can accomplish using this little piece of code

 <div class="col-lg-4">
   <?php
      $start = "08:00:00";
      $end = "17:00:00";
      $tStart = strtotime($start);
      $tEnd = strtotime($end);
      $tNow = $tStart;
   ?>
   <select class="form-control m-t-5" name="booking_start" required="">
     <option value="">Select Time</option>
     <?php
     while($tNow <= $tEnd){ ?>
     <option value="<?php  echo date("H:i:s",$tNow); ?>"> <?php echo date("H:i:s",$tNow);?></option>
     <?php
     $tNow = strtotime('+60 minutes',$tNow);
     }
    ?>
   </select>
</div>

Apologies for the formatting in advance. This gives me the output as follows in the dropdown

08:00:00
09:00:00
10:00:00 <---- Needs to be excluded from the dropdown seeing that the date & time is already in the database
11:00:00
12:00:00
13:00:00
14:00:00
15:00:00
16:00:00
17:00:00

Is there a way that "10:00:00" can be excluded from the dropdown options using php seeing that there is already a DB entry with that date & time. Like I said I am trying to learn something new & challenging and for the most part I am enjoying it, so please be kind. but honestly I am really stumped an how to get this done. 

any help will greatly be appreciated.

[Barand]

As well as your booking table you need a "slot" table

08:00:00
09:00:00
10:00:00
11:00:00
12:00:00
13:00:00
14:00:00
15:00:00
16:00:00
17:00:00 

which defines the slots for each day.

Then you can match this table with slots booked (left join) and output the unused slots.

[JacoSwanepoel]

Thank you for your reply Barand. I will add a slot table to my DB. but now my brain is running a mile a minute trying to figure out this query 🤔 

[Barand]

It will look something like

SELECT slot.starttime
FROM slot
     LEFT JOIN 
     sb_booking ON slot.starttime = TIME(booking_start)
                AND DATE(booking_start) = CURDATE()
WHERE booking_start IS NULL

 

[JacoSwanepoel]

2 minutes ago, Barand said:

It will look something like

SELECT slot.starttime
FROM slot
     LEFT JOIN 
     sb_booking ON slot.starttime = TIME(booking_start)
                AND DATE(booking_start) = CURDATE()
WHERE booking_start IS NULL

 

Thank you so much. Correct me if I'm wrong. based on the query the structure will look something like 

08:00:00
09:00:00
10:00:00
11:00:00
12:00:00
13:00:00
14:00:00
15:00:00
16:00:00
17:00:00

Instead of 

|id | Slot 1 | Slot 2 | Slot 3 | Slot 4 | Slot 5 | Slot 6 | Slot 7 |

Apologies again for this,,, I am very new to all of this

[Barand]

Yes, 1 time per row (normalized).

Never use your alternative (unnormalized) structure of muliple values in a single row.

CREATE TABLE slot (
starttime TIME NOT NULL PRIMARY KEY
);

C

Edited May 16, 2022 by Barand

[JacoSwanepoel]

3 minutes ago, Barand said:

Yes, 1 time per row (normalized).

Never use your alternative (unnormalized) structure of multiple values in a single row.

CREATE TABLE slot (
starttime TIME NOT NULL PRIMARY KEY
);

C

Great stuff. Thank you for your input so far, I will try my best to figure this out. 

[JacoSwanepoel]

Ok,,, Great I ended up with this....

SELECT sb_time_slots.slot_start_time, sb_bookings.booking_date, sb_bookings.start_time 
FROM sb_time_slots LEFT JOIN sb_bookings ON sb_time_slots.slot_start_time = TIME(start_time) 
AND DATE(booking_date) = '$SelectedDate' WHERE booking_start IS NULL

and ultimately ending up with,,,,

<div class="col-lg-3">
  <select type="text" class="form-control m-t-5" name="available_time" id="available_time" required="">
    <?php
    	$SQL_AvailableTime = mysqli_query($conn, "SELECT sb_time_slots.slot_start_time, sb_bookings.booking_date, sb_bookings.start_time FROM sb_time_slots LEFT JOIN sb_bookings ON sb_time_slots.slot_start_time = TIME(start_time) AND DATE(booking_date) = '$SelectedDate' WHERE booking_start IS NULL");
    while ($row = mysqli_fetch_array($SQL_AvailableTime)) {
        echo("<option value='" . $row['slot_start_time'] . "'>" . $row['slot_start_time'] . "</option>");
    	}
  	?>
    <label for="dropdown">Select</label>
  </select>
</div><!-- col -->

and it works great, however i was wondering how can this work between the 2 or more branches when we decide to open another store? Specifically when updating hours of operation and say changing the booking slot duration from 1 hour to 45min for instance.

is there an alternate way to do this for when one of our branches decides to change booking slot times.

<div class="col-lg-4">
   <?php
      $start = "08:00:00";
      $end = "17:00:00";
      $tStart = strtotime($start);
      $tEnd = strtotime($end);
      $tNow = $tStart;
   ?>
   <select class="form-control m-t-5" name="booking_start" required="">
     <option value="">Select Time</option>
     <?php
     while($tNow <= $tEnd){ ?>
     <option value="<?php  echo date("H:i:s",$tNow); ?>"> <?php echo date("H:i:s",$tNow);?></option>
     <?php
     $tNow = strtotime('+60 minutes',$tNow);
     }
    ?>
   </select>
</div>

is there any way to dynamically update the hours of operation and the time slot duration without the use of a second table while achieving the required results

Edited May 16, 2022 by JacoSwanepoel

[Barand]

One possibility, assuming you will also have a salon_id in the booking table too ...

+----------+-----------+------------+-------------+---------+
| salon_id | starttime | valid_from | valid_until | slot_id |
+----------+-----------+------------+-------------+---------+
|        1 | 08:00:00  | 2022-05-01 | NULL        |       1 |
|        1 | 08:45:00  | 2022-05-01 | NULL        |       2 |
|        1 | 09:30:00  | 2022-05-01 | NULL        |       3 |
|        1 | 10:15:00  | 2022-05-01 | NULL        |       4 |
|        1 | 11:00:00  | 2022-05-01 | NULL        |       5 |
|        1 | 11:45:00  | 2022-05-01 | NULL        |       6 |
|        1 | 12:30:00  | 2022-05-01 | NULL        |       7 |
|        1 | 13:15:00  | 2022-05-01 | NULL        |       8 |
|        1 | 14:00:00  | 2022-05-01 | NULL        |       9 |
|        1 | 14:45:00  | 2022-05-01 | NULL        |      10 |
|        1 | 15:30:00  | 2022-05-01 | NULL        |      11 |
|        1 | 16:15:00  | 2022-05-01 | NULL        |      12 |
|        1 | 17:00:00  | 2022-05-01 | NULL        |      13 |
|        2 | 08:00:00  | 2022-05-01 | 2022-05-31  |      14 |
|        2 | 09:00:00  | 2022-05-01 | 2022-05-31  |      15 |
|        2 | 10:00:00  | 2022-05-01 | 2022-05-31  |      16 |
|        2 | 11:00:00  | 2022-05-01 | 2022-05-31  |      17 |
|        2 | 12:00:00  | 2022-05-01 | 2022-05-31  |      18 |
|        2 | 13:00:00  | 2022-05-01 | 2022-05-31  |      19 |
|        2 | 14:00:00  | 2022-05-01 | 2022-05-31  |      20 |
|        2 | 15:00:00  | 2022-05-01 | 2022-05-31  |      21 |
|        2 | 16:00:00  | 2022-05-01 | 2022-05-31  |      22 |
|        2 | 17:00:00  | 2022-05-01 | 2022-05-31  |      23 |
|        2 | 08:00:00  | 2022-06-01 | NULL        |      24 |
|        2 | 08:45:00  | 2022-06-01 | NULL        |      25 |
|        2 | 09:30:00  | 2022-06-01 | NULL        |      26 |
|        2 | 10:15:00  | 2022-06-01 | NULL        |      27 |
|        2 | 11:00:00  | 2022-06-01 | NULL        |      28 |
|        2 | 11:45:00  | 2022-06-01 | NULL        |      29 |
|        2 | 12:30:00  | 2022-06-01 | NULL        |      30 |
|        2 | 13:15:00  | 2022-06-01 | NULL        |      31 |
|        2 | 14:00:00  | 2022-06-01 | NULL        |      32 |
|        2 | 14:45:00  | 2022-06-01 | NULL        |      33 |
|        2 | 15:30:00  | 2022-06-01 | NULL        |      34 |
|        2 | 16:15:00  | 2022-06-01 | NULL        |      35 |
|        2 | 17:00:00  | 2022-06-01 | NULL        |      36 |
+----------+-----------+------------+-------------+---------+

In the example data, salon one is on 45 minute slots but salon 2 is on 60 minute slots. On June 1st, salon 2 also changes to 45 minute slots.

[JacoSwanepoel]

Will this method require me to add booking slots for each salon added for each day of the month or am I misunderstanding? 

[Barand]

Slots 1 - 13 are used every day from until forever (no until date)

Slots 14 - 23 are valid until end of May

Slots 24 - 36 replace 14 - 23 on June 1st.

You use those slots where "'$SelectedDate' BETWEEN valid_from AND valid_until" for required salon

SELECT s.slot_start_time
FROM sb_time_slots s
     LEFT JOIN 
     sb_bookings b ON s.slot_start_time = TIME(b.start_time) 
                   AND s.salon_id = b.salon_id
                   AND '$SelectedDate' BETWEEN s.valid_from AND s.valid_until
                   AND '$SelectedDate' = b.booking_date
WHERE booking_start IS NULL");
    

 

[JacoSwanepoel]

Ahhh,,, Ok Great. The lightbulb just lit up,,, lol.
so when a salon updates their booking time slot duration I will need to run a update on the valid_until column for the specific salon ID with todays date -1 day and post a new "array is it?" to the database table with the updated time slots for that salon?

[Barand]

10 minutes ago, JacoSwanepoel said:

update on the valid_until column for the specific salon ID with todays date -1 day

Not necessarily today - 1, it would be the day before the new ones come into effect

• Insert new slot times for salon X setting valid_from to Y
• Update current slots for salon X setting validUntil to Y-1

[edit] Unless you are always changing your practices or opening new salons it is going to be a relatively infrequent process

Edited May 16, 2022 by Barand

[JacoSwanepoel]

Thank you so much for your valuable time and information, Your input makes 100% sense. I have alot to think about and some changes to make as well. You are an absolute legend. 

[Barand]

Don't go away - I'm just about to type up an alternative solution for you

[Barand]

Instead of storing the slot times, store the frequency for each salon

Table: slot_frequency
+----------+------+------------+-------------+
| salon_id | freq | valid_from | valid_until |
+----------+------+------------+-------------+
|    1     |  45  | 2022-05-01 |    NULL     |
|    2     |  60  | 2022-05-01 | 2022-05-31  |
|    2     |  45  | 2022-06-01 |    NULL     |
+----------+------+------------+-------------+

When you process a booking for salon X, get the frequency in use today and generate a temporary slot table

+-----------+
| starttime |
+-----------+
| 08:00:00  |
| 08:45:00  |
| 09:30:00  |
| 10:15:00  |
| 11:00:00  |
| 11:45:00  |
| 12:30:00  |
| 13:15:00  |
| 14:00:00  |
| 14:45:00  |
| 15:30:00  |
| 16:15:00  |
| 17:00:00  |

just for the duration of this booking

[JacoSwanepoel]

6 minutes ago, Barand said:

get the frequency in use today and generate a temporary slot table

I get the 1st part, but the second part i am unsure how to do? I have to admit that this does seem more into the direction that i want to go with this, but if all else fails then I still have your 1st option to work from. 

how do you generate a temporary slot table?

[Barand]

Like this

$selected_salon = 1;

$stmt = $pdo->prepare("SELECT freq
                       FROM slot_frequency
                       WHERE salon_id = ?
                       AND CURDATE() BETWEEN valid_from AND coalesce(valid_until, '9999-12-31')
                      ");
$stmt->execute([ $selected_salon]);

$freq = $stmt->fetchColumn();

$t1 = new DateTime('08:00:00');
$t2 = new DateTime('17:15');
$step = new DateInterval("PT{$freq}M");
$period = new DatePeriod($t1, $step, $t2);
// generate time slots
foreach ($period as $d) {
    $data[] = sprintf("('%s')", $d->format('H:i:s'));
}
// create temp table
$pdo->exec("CREATE TEMPORARY TABLE sb_time_slot (
              slot_start_time TIME primary key
          )");
// insert into the temporary table
$pdo->exec("INSERT INTO sb_time_slot(slot_start_time) VALUES " . join(',', $data));

 

[JacoSwanepoel]

Good Morning Barand
I think solution 2 is what i am looking for and will try to implement this as soon as possible

Regards 

Jaco Swanepoel