query for available rooms in hotel reservation between two dates

0
votes

I need to select just the available rooms in hotel reservation system

Here's my query

echo $str = $_POST['start'];
echo $en = $_POST['end'];
$sql = "SELECT * 
        FROM chambre 
        WHERE id NOT IN (SELECT id_chambre 
                         FROM reservation_client
                         where start < $en
                            or end >=$str)";

But the query gives me the rooms that are not in table reservation_client not the rooms available between $str and $en date

1
phpmysql
Possible duplicate of How do I query between two dates using MySQL? – hs-dev2 MR
You are using not in so that will get result not that range date – hs-dev2 MR
@hs-dev2MR i should replace it by IN ? – omayma uchiha
Yes also you need to use AND not or for date range – hs-dev2 MR
@omayamauchiha How about Qirel answer !! – hs-dev2 MR

1 Answers

2
votes

You can use a LEFT JOIN instead, and see where the reservations are NULL (meaning there were no left joins).

SELECT c.* 
FROM chambre AS c
LEFT JOIN reservation_client AS rc
  ON c.id = rc.id_chambre AND rc.start < '$en' AND rc.end >= '$str'
WHERE rc.id_chambre IS NULL

Though you should be using a prepared statement and bind your values through placeholders instead.