Mysql – checking availability of rooms

MySQLphpmyadmin

I have 3 table in my sql database:

Table Room

roomnumber   roomtype    
  1           DeluxeRoom           
  2           DeluxeRoom  
  3           DeluxeRoom
  100         StandardRoom           
  101         StandardRoom  
  102         StandardRoom

Room is a table which store the room and their type. 

Table Reservation

reservationid  checkin       checkout
  1           2017-01-01     2017-01-03
  2           2017-01-10     2017-01-20
  3           2017-02-05     2017-02-08

Reservation is a table where users make their booking.

Table RoomReserve

reservationid    roomnumber
 1                 1
 2                 100
 2                 101
 3                 1
 3                 2
 3                 3

In RoomReserve, for each reservationid, I can input multiple values for roomnumber. I can also input same roomnumber for different reservationid if the checkin and checkout date are not the same.

I am trying to make a query to count the available rooms when user want to book rooms but the results i am getting is wrong.

Here are the codes i am trying:

SELECT  COUNT(roomnumber)
    FROM  room
    WHERE  roomtype='DeluxeRoom'
      AND  roomnumber NOT IN (
        SELECT  *
            FROM  roomreserve r,reservation e, room m
            WHERE  r.reservation_id=e.reservation_id
              AND  m.roomnumber=r.roomnumber
              AND  ('$checkin' BETWEEN start_date AND end_date)
              OR  ('$checkout' BETWEEN start_date AND end_date)
                          );
2) 
SELECT  COUNT(roomnumber)
    FROM  room
    WHERE  roomtype='DeluxeRoom'
      AND  roomnumber NOT IN (
SELECT  COUNT(roomnumber)
    FROM  roomreserve r,reservation e, room m
    WHERE  r.reservation_id=e.reservation_id
      AND  m.roomnumber=r.roomnumber
      AND  ('$checkin' BETWEEN start_date AND end_date)
      OR  ('$checkout' BETWEEN start_date AND end_date)
                          );

Best Answer

This is a somewhat common logical error.

('$checkin' BETWEEN start_date AND end_date) OR
('$checkout' BETWEEN start_date AND end_date));

The problem is not the way the query is written, it's that you're using the wrong criteria. A reservation can conflict while neither of these is true, simply by starting before and ending after the desired values.

What you actually need to find is the overlap.

A room is not available if it has any existing reservation with the following conditions true:

  • a check-in before your desired check-out and
  • a check-out after your desired check-in

Any reservation that starts before your desired end and ends after your desired start is a conflict.

Stated another way, a check-in after your desired check-out cannot possibly be a conflict, and a check-out before your desired check-in cannot possibly be a conflict. All others are conflicting.

So you want to identify these reservations:

(end_date > '$checkin') AND
(start_date < '$checkout');

Note also that you must never build queries with variable interpolation, as you have done here. It is an unsafe practice for which there are no genuine exceptions, only excuses. Use prepared statements and placeholders.

Related Question