Room availability not functioning...someone please give guidance

Of course there are various ways to do it, I’m sure. But if you treat all bookings and rooms closures as plain old bookings (just that they are booked to the owner if closed), then the status of a room will always be consistently in the bookings table and you won’t have any use for availability in the rooms table.

If an availability check is made and you query the bookings table using SpacePhoenix’s where clause or similar (I didn’t check its detail), you can check if there is a room number, which is not booked on or between the dates you want and, which therefore is neither closed nor booked. That means it would be available.

hth

bazz

I’ll post some faux/pseudo code to help explain what I meant there:


select
        room number
      , room_type
  FROM rooms AS r
INNER
   JOIN room_types AS rt
    ON r.room_number = rt.room_number
  AND rt.room_type = 'room_type_sought'
and NOT EXISTS (
                     SELECT b.room_number
                     FROM booked AS b
                     WHERE b.room_number = rt.room_number
                     and b.check_out_date >= ?   # arrival_requested
                     and b.check_in_date <=  ?    # departure_requested
                       )  

WHERE -- other conditions here --


bazz