MYSQL- available room between date range

[mirapsm]

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) 
    {
        die(mysql_error());
    }

    while($row = mysql_fetch_array($query))

<!-- end snippet -->



below is html code :

<tr>
<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>
</tr>
<tr>
<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>
</tr>
<tr>
<th scope="row">&nbsp;</th>
<th scope="row">&nbsp;</th>
<th class="leftbox" scope="row">
<input type="submit" id="submit" value="Check Availability"  /></th>

[Taywin]

Hmm...

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

[Santanu.Das]

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.

[Jun_7]

Sir what if the start date is in between but the end date is not?
example room1 is booked for 4 days start date 6/14/2018, end date 6/19/2018.
and the other guest search room start date on 6/18/2018, end date 6/24/2018.
and i used between in my query.
it means room1 is not select or it will not show?, right?