Search code examples
phpmysqlsqldate-arithmetic

PHP - Prevent overlapping reservations

I have an application that allows a user to reserve a recreational facility. The user gets to select a beginning date (05/05/2022) and end date (05/08/2022), then data gets sent through a database.

However, (ISSUE) I have a user that wants to reserve the same facility. They select a beginning date (05/03/2022) and end date (05/06/2022). My goal is to prevent a user from reserving a facility with an end date between dates of an existing reservation.

(ACHIEVED) I have achieved by disabling the desired facility being reserved if the beginning date and end date are between an existing reservations' dates. I have done an SQL query of:

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

What are some suggestions to handle ISSUE alongside handling ACHIEVED in a query?

Solution

  • My goal is to prevent a user from reserving a facility with an end date between dates of an existing reservation.

    You can check if the new reservation would overlap an existing one with the following logic:

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

    Given a facility and a date range, the query checks if a reservation already exists for the same reservation and with an overlapping time range. It returns the "offending" reservation(s), or no rows if there are no conflicts.