SitePoint Sponsor

User Tag List

Results 1 to 3 of 3
  1. #1
    SitePoint Addict
    Join Date
    Jul 2006
    Location
    Fionnphort, Isle of Mull, Scotland
    Posts
    354
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    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.
    PHP Code:
                 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 ?
    Tim Dawson
    Isle of Mull, Scotland

  2. #2
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,338
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by ramasaig View Post
    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.
    you should experiment with this part of the query --
    Code:
    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
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  3. #3
    SitePoint Addict
    Join Date
    Jul 2006
    Location
    Fionnphort, Isle of Mull, Scotland
    Posts
    354
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    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:
    PHP Code:
            while (strtotime($startDate) <= strtotime($maxStartDate)) {
               if (!empty(
    $nights)) {
                    
    $endDate date('Y-m-d'strtotime('+' $nights-' 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.
    Tim Dawson
    Isle of Mull, Scotland


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •