Member Avatar for 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

  • 2 Contributors
  • 1 Reply
  • 903 Views
  • 1 Day Discussion Span
  • Latest Post Latest Post by tesuji

Dani AI

Generated

A single UPDATE over a date range will happily flip every “available” day and leave holes where days are already taken. is right: this is an application-level allocation problem (stored procedure or PHP/PSM code) rather than one plain set-based SQL statement. The goal is either 1) find a single room free for the whole span, or 2) build a sequence of room assignments whose union covers every night in the requested interval, present those alternatives, and only commit when accepted.

A simple, practical algorithm is greedy: start at the booking start date, pick the room that is free for the longest contiguous run beginning that day, reserve that block, advance to the next uncovered date and repeat. That minimizes the number of room changes and is easy to implement and explain to guests.

function allocate(start, end, rooms):
  d = start
  allocations = []
  while d <= end:
    bestRoom = null
    bestEnd = d - 1
    for r in rooms:
      freeEnd = free_run_end(r, d, end)   // query calendar/app state
      if freeEnd > bestEnd:
        bestRoom = r; bestEnd = freeEnd
    if bestRoom == null: return failure
    allocations.append({room:bestRoom, from:d, to:bestEnd})
    d = next_day(bestEnd)
  return allocations

Important operational notes: perform tentative allocation inside a transaction and lock the involved calendar rows (InnoDB: SELECT ... FOR UPDATE) or insert short-lived “hold” rows so concurrent requests cannot double-book the same slots. Decide and document whether end dates are inclusive or exclusive (nights vs. calendar dates) — off-by-one errors are the most common bug. For long-term maintainability consider a bookings table with start/end per booking and derive a calendar view from it; overlap checks then become straightforward (and UI-friendly) while the allocation logic above still applies.

Member Avatar for 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

Edited by tesuji because: n/a
Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.