Search for Room Availability in Bookings [PHP/MySQL]

Question

Hi,

I am programming a Hotel Reservation module. Now I have to connect an existing application to get the available rooms on a given range of dates. Please help me in this.

I am here posting the DB schema of the existing app (where the rooms and bookings info were stored).

rooms:

id
room_type
room_count
people
hotel_id

bookings:

id
from
to
name
email
people
dt
hotel_id

bookings_rooms

booking_id
room_id
quantity

I will give 3 inputs

1. Hotel ID
2. From Date
3. To Date

What I need is a list of room_id and max_qty_available

Answer 1

I`m guessing what some of this parameters are, but here it goes:

Select id as room_id, room_Count as max_qty_available From Rooms Where id Not in (Select room_id From Bookings_rooms Where booking_id In (Select id From bookings Where from>=@FromDate And to <=@ToDate And hotel_id=@HotelID) ) And hotel_id = @HotelID

You can replace IN and NOT IN with Exists and Not exists if there is a performance issue. But not sure if Exists are supported in mysql.

Answer 2

So, rooms aren't actually rooms, they are a type with a count. Does clear up a few things, although by rooms not being seperate entities it is very hard to prevent a change of rooms being eligable.

SELECT r.id, r.room_count  - SUM(br.quantity ) AS max_qty_available
FROM rooms r
LEFT JOIN bookings b
ON b.hotel_id = r.hotel_id
    AND b.from < <Date END>
    AND b.to   > <Date START>         
LEFT JOIN booking_rooms br
ON br.room_id = r.id 
    AND br.booking_id = br.id
WHERE r.hotel_id = <Hotel ID>