Room Availability Check SQL Query

Question

I am designing a hotel booking system. My tables are as follow:

rooms:

id, room, room_type, hotel_id

room_type:

id, type, hotel_id

booking

id,room_id,type_id, hotel_id, checkin, checkout

I am trying to make a room availability form which checks the booking table and gets all the rooms under selected room type with the status i.e whether the room is available or not on the given dates.

Here are have three conditions to check:

checkin_date < booking table checking date

Or

checkin_date > booking table checkout date

Or

room_id (with hotel it and room type as specified) should not be available in booking table i.e it has never been booked.

Hence, I designed the following query but it failed:

SELECT * FROM rooms where room_type = '$room_types' & 'hotel_id' = '$hotel_ids' AND `id` IN (SELECT room_id FROM booking WHERE (`hotel_id`='$hotel_id' AND `room_type`='$room_types' AND ( `checkin` > '$checkout' OR `checkout` < '$checkin' ) ) ) or 'id' NOT IN (SELECT room_id FROM booking WHERE `hotel_id`='$hotel_id' AND `room_type`='$room_types' )

here

$checkin refers to checkin date selected by user.
$checkout refers to checkout date selected by user.
$hotel_id is hotel id.
$room_type is room type like deluxe etc.

Any advice on what is correct way of doing this?

Try to use `AND` or `&&` (logical operator) instead of simple `&` (binary operator) — Camille, Sep 26 '17 at 14:17
@Sohail: What exactly do you mean by "the query failed"? Does it return the wrong result or does it throw an error? — Imanuel, Sep 26 '17 at 14:47
@Pharaoh Its not giving the correct output.. no errors as such — GoSSDHosting, Sep 26 '17 at 14:48
In these situations echo out the query, copy paste into something like mysql workbench. Then try and work out what's going on. — Richard Housham, Sep 26 '17 at 15:34

Room Availability Check SQL Query

0 Answers0