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.

But, (Question)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)I can achieve this by disabling the required amenities for the reservation if the start and end dates fall between existing reservation dates. I've completed the SQL query:

"SELECT * FROM reservations WHERE facility = :facility AND :begDate BETWEEN begDate AND endDate AND :endDate BETWEEN begDate AND endDate";

What are some suggestions for handling ISSUE in queries while handling ACHIEVED?

Answer 1

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.