Jump to content

How to exclude timeslot in dropdown list | PHP


JacoSwanepoel

Recommended Posts

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.

Link to comment
Share on other sites

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.

Link to comment
Share on other sites

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

Link to comment
Share on other sites

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. 

Link to comment
Share on other sites

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 by JacoSwanepoel
Link to comment
Share on other sites

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.

Link to comment
Share on other sites

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

 

Link to comment
Share on other sites

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?

Link to comment
Share on other sites

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 by Barand
Link to comment
Share on other sites

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

Link to comment
Share on other sites

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?

Link to comment
Share on other sites

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

 

Link to comment
Share on other sites

This thread is more than a year old. Please don't revive it unless you have something important to add.

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.