php - How to select just the available rooms on a reservation system?

Question

I'm trying to build a reservation system for a hotel and I don't know how to select just the available rooms? I have two tables: table of rooms with called "chambre" it have: id, libelle and table called" reservation_client" it has: id, id_client, id_chambre, start, end. For the start and end they are the start date of reservation and the end of reservation

This is my select form:

<div class="form-group">
    <label class="col-sm-2 control-label">Chambre</label>
    <div class="col-sm-9">
        <select class="form-control" name="id_chambre">
            <option>Selectionnez une chambre</option>

            <?php 
                 /*echo $req2=$bdd->query('SELECT * FROM reservation_client WHERE $donnees["start"]!==$_POST["start"] ' )/*or die (print_r($bdd->errorinfo()))*/
                $req=$bdd->query('SELECT * FROM chambre' )/*or die (print_r($bdd->errorinfo()))*/;

                $req2=$bdd->query('SELECT * FROM reservation_client ' );
                    while ($donnees = $req->fetch() ) { 

                  echo"<option  value='".$donnees['id']."'>".($donnees['libelle']) ."</option> "  ;                                                 
                      }                   
              ?>

        </select>
    </div>
</div>

I tried this code but without any result:

$req2=$bdd->query('SELECT * FROM reservation_client WHERE start<end AND end>star ' );

Answer 1

You just need to join the table, if you don't know how to do that then do some research into mysql LEFT JOIN and INNER JOIN. In this case you want to LEFT JOIN so that you can include rows without a matching reservation. I can't write it exactly becaus I don't know your tables but this example should give you an idea:

SELECT * FROM chambres c
LEFT JOIN reservation_client rc ON rc.chambre = c.id
WHERE rc.date BETWEEN (your date range)
AND rc.id IS NOT NULL