Avoid booking overlap situation in PHP

Question

I have an application that allows users to book entertainment facilities. Users can select a start date (05/05/2022) and an end date (05/08/2022) and then send the data through the database. However, (problem) I have a user who wants to book the same facility. They select a start date (05/03/2022) and an end date (05/06/2022). My goal is to prevent users from booking facilities with an end date between existing booking dates. (Implemented) If the start and end dates are between existing booking dates, I can disable the

P粉554842091 · Answer

You can check if a new booking overlaps an existing booking using the following logic:

SELECT * 
FROM reservations 
WHERE facility = :facility AND :begDate <= endDate AND :endDate >= begDate

Given a facility and a date range, the query will check if a reservation for the same reservation already exists with an overlapping time range. It returns the "problematic" reservation, or no rows if no conflict exists.