mysql query for a booking system

Question

I have spent more than 14 hours trying to find a way to accomplish this but no luck!

I'm working on a booking system , I have 2 tables :

Rooms
---------
ID - ROOM_TYPE - NAME - ROOM_COUNT
1       1   single room      6
2       2   double room      4
3       3   studio           2


Booking
---------
ID - ROOM_TYPE - Customer_NAME  - CHECK_IN  - CHECK_OUT
1       3         John   A           1-1-2013      4-1-2013
2       3         John   B           2-1-2013      5-1-2013             
3       2         John   C           8-1-2013      9-1-2013

after the user put 2 dates to search for an available room , I make a query to display all type rooms that I have , I have 3 types (single/double/studio), each type has number of rooms available in the hotel (ex: the hotel has 6 single rooms by default)

I end up with this query to get all available type rooms

            SELECT * 
            FROM rooms              
             where id NOT IN 
                 ( SELECT room_type FROM  booking 
                    WHERE "'.$check_in.'"  <= check_out
                    AND "'.$check_out.'"  >= check_in                                             
                  )

I need to make another condition that WHERE count(room_type) from booking LESS than the value saved in ROOM_COUNT field in ROOMS table ..

depend on the table structures above , the hotel has just two studios and all aren't available in 4-1-2013 .. so, the query shouldn't display studio type room when somebody search in that date ..

I need something like count how many bookings for each room type then display the results ..

Thank you in advance

Your logic is flawed anyway - if someone checks out of a particular room type just after the start of the search range, and someone else checks in just before the end of it, then those two bookings could be accommodated in the same room, but your logic would count that as two rooms un available for that period. I think you might need a rethink anyway... — David M
actually I'm trying to make it simple as much as possible with just 2 table.. if I don't get answers here I'll try other ways : ( , thank you David. — Ahmad
As the number of available rooms may change from day to day, you will have to include a table of dates in your query, so that you can find the number of rooms availalbe on each of the dates. — Terje D.

Gordon Linoff Gordon Linoff · Accepted Answer · 2013-01-27T16:02:24

Here is the idea. You need to know the maximum number of rooms taken during the period. You can get the number of rooms taken on any given day by looking at the number of checks ins prior to that day minus the number of checkouts.

Next, to get the max, we only need to look at the check in day (since check outs reduce the maximum value). The following gets the maximum on any day that has checkins:

select r.room_type, r.room_count, r.room_count - RoomsTaken
from Rooms r join
     (select room_type, max(Num_CheckOuts - num_CheckIns) as RoomsTaken
      from (select b.*,
                   (select count(*) from booking b2 where b2.room_type = b.room_type and b2.check_in <= b.check_out
                   ) as num_CheckIns,
                   (select count(*) from booking b2 where b2.room_type = b.room_type and b2.check_out <= b.check_in
                   ) as Num_CheckOuts
            from booking b
           ) b
      where $check_in <= check_out and $check_out >= check_in
      group by room_type
     ) b
     on r.room_type = b.room_type

(Note: this query is not tested so it may have syntax errors.)

It uses correlated subqueries to get the cumulative counts. In other databases, window functions might be used for this purpose.

mysql query for a booking system

1 Answers