How to display an available room using date range? below is a code, where is display all the room on that date. its should display a room that had not been booked between two dates.

my input :  
$datein = 31-12-2014  
$dateout = 01-01-2015

below is database :

    ---------- room Table (example)  
    id   |    name            
    1    |   Home1  
    2    |   Home2      
    3    |   Home3

    ---------- resinvent Table (example)  

        resid |  id   |   datein   | dateout  |  status  
          1   |  1    | 2014-12-31 | 2015-01-01|  Active

below is sql code :

<!-- begin snippet: js hide: false -->

<!-- language: lang-html -->

        $in = date("Y-m-d", strtotime($_POST['start']));
        $out = date("Y-m-d", strtotime($_POST['end']));

    $query = mysql_query("SELECT * FROM room WHERE id NOT IN(SELECT * FROM resinvent WHERE (datein <= '$dateout' AND dateout => '$datein' )");

    if($query === FALSE) 

    while($row = mysql_fetch_array($query))

<!-- end snippet -->

below is html code :

<th scope="row">Check In Date</th>
<th scope="row">: </th>
<th scope="row"><input type="text" class="w8em format-d-m-y highlight-days-67 range-low-today" name="start" id="sd"  maxlength="10"  readonly="readonly"/></th>
<th scope="row">&nbsp;</th>
<th scope="row">Check Out Date</th>
<th scope="row">:</th>
<th scope="row"><input type="text" class="w8em format-d-m-y highlight-days-67 range-low-today" name="end" id="ed" maxlength="10"  readonly="readonly" /></th>
<th scope="row">&nbsp;</th>
<th scope="row">&nbsp;</th>
<th scope="row">&nbsp;</th>
<th class="leftbox" scope="row">
<input type="submit" id="submit" value="Check Availability"  /></th>
3 Years
Discussion Span
Last Post by Shark_1


Your database tables don't look like or at least the column name doesn't look like. What is resinvent table for? Is it a reservation or is it a room available table???

Edited by Taywin


You can use MINUS Clause in SQL Statement.
Multiple queries can be put together and their output combined using the minus clause. The Minus clause outputs the rows produced by the first query, after filtering the rows retrieved by the second query.
The statement should be

"SELECT DISTINCT id FROM room MINUS SELECT DISTINCT id FROM resinvent WHERE datein <= '$out' AND dateout => '$in'

The MINUS clause picks up records in the first query after filtering the records retrieved by the second query.
Hope it can help you.

This topic has been dead for over six months. Start a new discussion instead.
Have something to contribute to this discussion? Please be thoughtful, detailed and courteous, and be sure to adhere to our posting rules.