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.


             SELECT r.room_id
			 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

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).
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 ?

you should experiment with this part of the query –

SELECT ...
AND b.end_date >= '" . $startDate . "'
AND b.start_date <= '" . $endDate . "'

just use your php logic to vary the value of $stardate and $enddate accordingly

Thanks, Rudy.

It seems to me you are confirming my first thought, which was to iterate through the range of possible startDates. I’ve been working along those lines:

  1. Set $startDate to the earliest start date in the range
  2. Create $maxStartDate equal to the latest start Date,
    then:

		while (strtotime($startDate) <= strtotime($maxStartDate)) {
           if (!empty($nights)) {
				$endDate = date('Y-m-d', strtotime('+' . $nights-1 . ' days',strtotime($startDate)));
           }
           ....  // lots of code in here, much as before

          $startDate = date('Y-m-d', strtotime('+1 day', strtotime($startDate)));
        }

It looks as if it’s going to work. No extra SQL at all, a bit more PHP for the WHILE loop and to combine all the separate searches.