Adnama Adnama - 6 months ago 21
SQL Question

check if dates fall in range dates

I am trying to do a simple reservation system, and I would like to have an sql query to check if the dates are available and that no date in the date range is taken.

Example: there is this date taken: 2016-01-01 -> 2016-01-08
And I have to check dates like:


  • 2015-12-25 - 2016-01-03

  • 2015-12-25 - 2016-01-20

  • 2016-01-04 - 2016-01-06

  • 2016-01-04 - 2016-01-10

  • 2016-01-01 - 2016-01-08



These are the scenarios I had thought of. But while researching, all the examples have 2 sets of dates to check. All the scenarios are not to check unknown dates from the database.
I am lost. If anyone can help I would appreciate. Maybe you had used this scenario in the past.

Answer
For future reference for anyone who has this problem, here is how I solved my issue :

$query  = "SELECT * FROM booking WHERE ";
        $query .= "checkInDate >= $chkInDate AND checkOutDate >= $chkOutDate OR ";
        $query .= "checkInDate >= $chkInDate AND checkOutDate <= $chkOutDate OR ";
        $query .= "checkInDate <= $chkInDate AND checkOutDate >= $chkOutDate OR ";
        $query .= "checkInDate <= $chkInDate AND checkOutDate <= $chkOutDate OR ";
        $query .= "checkInDate =  $chkInDate AND checkOutDate  = $chkOutDate ";