0

I need to search a user's date range input against a series of date ranges in a MySQL table.

Perhaps an example might explain this better. Let's say I have a "Hotel" database with a "Rates" table. One row is for "Fall," from September 1 to November 30, and another is "Winter," from December 1 to March 30 with additional columns in there as well. A guest is booking a room that overlaps two rates. He'll be there for Thanksgiving and leaving in early December. That'll cover two separate rates. I need to run a search that returns every "Rate" row that's within the guest's stay.

This only works if a guest's booking is completely WITHIN ONE (and only one) rate:

SELECT * FROM 'rates' WHERE start < $guestArrive AND end > $guestDepart

Any ideas?

user2975310
  • 51
  • 1
  • 4

1 Answers1

0

Are start and end dates mutually exclusive? can you do this:

SELECT * FROM 'rates' 
WHERE 
    (start <= $guestArrive AND $guestArrive <= end)
    OR
    (start <= $guestDepart and $guestDepart <= end)

or clearer:

SELECT * FROM 'rates' 
WHERE 
    ($guestArrive BETWEEN start AND end)
    OR
    ($guestDepart BETWEEN start AND end)
cmdprompt
  • 141
  • 1