0

I am trying to achieve something like this.

**First user:**

Check-in date: 2020-08-12

Check-out date: 2020-08-19

**Second user:**

Check-in date: 2020-08-13

Check-out date: 2020-08-18

Error message for the second user will be: Dates have already been booked.

i tried to come up with a statement below,

$sql = "
SELECT count(*) 
  FROM $TableName
 where prop_ID = $pid
   AND  checkInDate <= '$checkin'
   AND checkoutDate >= '$checkout'
";

However, the above statement only checks for its date for check-in and check out and not the dates between.

Anyone who can help will be of great help! Thanks in advance!

Strawberry
  • 33,750
  • 13
  • 40
  • 57
Joey Kang
  • 11
  • 2
  • 1
    Start with fixing the [sql injection](https://stackoverflow.com/questions/60174/how-can-i-prevent-sql-injection-in-php). There are two criteria for overlap. You input date range is between and existing range and secondly if the existing date rage is within your input date range. Within a date range means the start or end date is within the range. – danblack Aug 15 '20 at 01:00
  • Danblack is mistaken. Event A overlaps Event B if Event A starts before Event B ends, and ends after Event B starts. This is the only test for an overlap. The point about sql injection is spot on however. – Strawberry Aug 15 '20 at 05:23
  • Please confirm if that is the only scenario you need to cover? What about second user 2020-08-18 to 2020-08-21 or 2020-08-08 to 2020-08-15? Also please confirm edge cases e.g. 2020-08-19 to 2020-08-21 (user 1 check out and next user check in same date)? With that information I will be able to write the sql. – Mark Aug 15 '20 at 10:08

0 Answers0