0

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
Dharman
  • 30,962
  • 25
  • 85
  • 135
Jaco Swanepoel
  • 1
  • Why `11:00 - 12:00` not included in your output? – nice_dev May 13 '22 at 12:32
  • I am sure i am just saying it wrong so i really apologize, So if the whole day is booked on 2022-05-12 from 08:00 - 15:00 and leaving only 2 time slots available, only 15:00-16:00 & 16:00 - 17:00 needs to be displayed. dates & times are only added to the SQL table once someone books that time. so if a start_time is not found in the table for the day the person selected it needs to show it in the dropdown list. I hope this makes better sense – Jaco Swanepoel May 13 '22 at 12:45

3 Answers3

0

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.

raphael75
  • 2,982
  • 4
  • 29
  • 44
  • 1
    _"I'm also assuming that "booking_date" will have a value to indicate that time slot has been reserved"_ - that would require that records for all _possible_ time slots would be inserted into the database to begin with, but I don't think that is actually the case here. There are no records with booking_date NULL. Only actually _booked_ timeslots are in the database. – CBroe May 13 '22 at 12:07
  • Tank you for your reply. I will give this a shot. Apologies for not stating my table name. the table name is sb_bookings, the "booking_date" is used for the lookup (or rather, how i see it in my mind) so is for instance if 2022-05-12 & 2022-05-12 12:00:00 is not in the start time column then it needs to be displayed in the dropdown. – Jaco Swanepoel May 13 '22 at 12:12
0

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)

UioSun
  • 59
  • 5
0

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.

Peter
  • 626
  • 6
  • 6