Booking System - How to deal with double bookings

[pritz01]

Hi newbie here
I’m currently making a hotel booking system in mysql/php, but I’m having problems with overlapping dates/double bookings.

For example if a user attempted to book room1 from the 1st august to the 5th august, but the room is already booked on the 3rd and unavailable on the 5th it would book the 1st,2nd, & 4th . I would like it to check the availability of other rooms available during the chosen period if the dates cannot be entirely fulfilled. For example Room 2 is free on the 3rd so the user is offered this alternative.

Below is the table data. If the status is ‘2’ then the room is booked a status of ‘1’ means the room is available and a status of ‘3’ means the room is unavailable.

Caldate RoomID status BookingID
2010-08-01 1 1 Null
2010-08-02 1 1 Null
2010-08-03 1 2 Null
2010-08-04 1 1 Null
2010-08-05 1 3 Null
2010-08-01 2 2 Null
2010-08-02 2 2 Null
2010-08-03 2 1 Null
2010-08-04 2 2 Null
2010-08-05 2 1 Null

My current update query is:

UPDATE calendar
SET status=’2’, BookingID=’’
WHERE RoomID=’1’ 
AND status =’1’ 
AND caldate BETWEEN  ‘2010-08-01’ AND ‘2010-08-05’

Any help would be appreciated. Thanks

[tesuji]

Hi

This problem cannot be solved with one single sql select statement. You need some procedural code, e.g. programmed in PSM, C, Java or PHP. I would design a user defined function or stored procedure in PSM to carry out this task. The procedure may function that way:

Given a guest's request for booking consecutive dates starting with startdate up to enddate, the procedure tentatively allocates rooms. If booking period cannot be covered by only one room, UDF tentatively books alternative rooms. The result will be shown to guest/user. If he decide to book the suggested rooms changes to booking table will be committed, if not, then rollbacked.

Inside the procedure this query may be used to get an overview on the booking situation:

select caldate, roomID, bookingid from booking 
 where rstatus = 1 and caldate between '2010-08-01' and '2010-08-05'
  group by caldate, roomID, bookingid order by caldate, roomID;

-- where '2010-08-01' and '2010-08-05' are examples for startdate and enddate which
-- should be replaced by variables depending on your programming environment.

Attention: This code not tested

-- tesu