Searching for a range of results within a range of dates
I'm working on a PHP/MySQL script that will search an accommodation database for availability. With help from this forum I have the following MySQL query, which works a treat when the user selects a specific start date and number of nights accommodation required ($nights). Table1 contains the room information, table2 the existing bookings (reservations). ($endDate = $startDate + $nights -1) The query selects rooms which are NOT booked.
Now I want to allow the user to specify a range of dates for the start date (e.g. three days either side of the preferred start date).
FROM " . $table1 . " AS r
LEFT OUTER JOIN " . $table2 . " AS b
ON r.room_id = b.room_id
AND b.end_date >= '" . $startDate . "'
AND b.start_date <= '" . $endDate . "'
WHERE r.rm_type = '" . $type . "' AND b.room_id is NULL
I'm NOT looking for rooms that are free throughout the range of start dates. I want to know which rooms are free for '$nights' or more consecutive nights at any time within the range. At this point I don't even need to know the specific availability dates of any room that gets included, although that information may be useful later (so if collected in passing it should not be discarded)
One way to do this might be to repeat the search several times, incrementing the start and end dates by one each time, to cover the range. This might be fine for three days either side, but what about two weeks either side (that is a whole month to search).
The database I have to search is relatively small (< 1000 rooms). So it could probably stand the iterative method. But before I start on that I wonder if there's a better way ?