Show only available time slots in select field-CodePudding

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

CodePudding user response：

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.

CodePudding user response：

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)

CodePudding user response：

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.