'Show only available time slots in select field

I am working on a booking system where users can book certain services online. I am stuck on finding and displaying available time slots within a specific day. I know the length of the needed time slot is 1 hour and the business hours.

Is there a way to show time slots that has not yet been booked on a certain day and display only the available time slots that is available to be booked in a dropdown select form?

If a customer selects a specific day and clicks "Select Day" then it needs to query the DB and return the results.

My SQL structure is as follows

|id | title | start_time          | end_time            | booking_date |
| 1 | Name1 | 2022-05-12 08:00:00 | 2022-05-12 09:00:00 | 2022-05-12   |
| 2 | Name2 | 2022-05-12 10:00:00 | 2022-05-12 11:00:00 | 2022-05-12   |
| 3 | Name3 | 2022-05-12 13:00:00 | 2022-05-12 14:00:00 | 2022-05-12   |
| 4 | Name4 | 2022-05-12 14:00:00 | 2022-05-12 15:00:00 | 2022-05-12   |

as per above the select form should display the timeslots that is not already taken.

09:00 - 10:00
12:00 - 13:00
15:00 - 16:00
mysql


Solution 1:[1]

It would be something like:

select
  id, title
from
  <table>
where
  start_time between '2022-05-12 00:00:00' and '2022-05-12 11:59:59'
and 
  booking_date is null

I don't know the name of your table, so you would need to replace <table> with that. I'm also assuming that "booking_date" will have a value to indicate that time slot has been reserved, that it's a date field, and it will be null if that slot hasn't been selected. However, booking_date could have a different purpose.

Solution 2:[2]

This is a lazy answer (because I think just use SQL will do it, use subSelect and other function, but I don't know how to do, sorry.)

get today occupy time:

SELECT id, TIME(start_time) AS s_time FROM tablename 
WHERE start_time >= '2022-05-12 00:00:00'
AND start_time < '2022-05-13 00:00:00'

diff time in php:

$sqlResult = []; // sql result
$timeAll = [
  '00:00:00',
  '01:00:00',
  '02:00:00',
  '03:00:00',
  ... // TODO: we need fill it
  '23:00:00',
];

foreach ($sqlResult as $item) {
  if (isset($timeAll[$item['s_time']])) {
    unset($timeAll[$item['s_time']]);
  }
}

return $timeAll;

// TODO: javascript or other client code can use it.

ref knowledge link:

MySQL SELECT WHERE datetime matches day (and not necessarily time)

Solution 3:[3]

If you choose 2022-05-26, and Peter is occupying room F25 from 2022-05-16 until 2022-05-29, it means the date you select must be out of this range. So, the query below will only return rooms that were not booked on that day.

SELECT b.id, b.room_id as available_room FROM booking as b 
WHERE(
    unix_timestamp('$mydate') 
    NOT BETWEEN unix_timestamp(b.start_date) 
    AND unix_timestamp(b.end_time)
) 
AND unix_timestamp(b.end_time) < unix_timestamp('$mydate');

Assuming $mydate is the variable that contains the date selected by the user, the above query will return rooms that will be available in the future on that particular day.

Sources

This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.

Source: Stack Overflow

Solution Source
Solution 1 raphael75
Solution 2 UioSun
Solution 3 Peter

Related Questions