Hotel room booking/reservation

Question

I'm working on a reservation/booking system for a small hotel. I'm pretty good with PHP but not so good with SQL... I made a form where you enter your information, number of rooms and select arrival date and check-out date using a calendar.

Now everything went good until I got to the point where you have to check which rooms are available and it's giving me a headache. There are 10 rooms you can book.

I currently have one table in MySQL storing the information, dates, booking-ID and room-ID/number.

How would you make the SQL for checking which rooms that are available and not?

Should it look something like

"SELECT * FROM bookings WHERE checkinDate >= '$formCheckin' 
 AND checkoutDate <= '$formCheckout' "

and then get the roomID and count them?

Any help is very appreciated!

You should really make a rooms table and assign an index which marks them as occupied or not, this would really ease up everything for you. — Akshay
Have you looked at some of the Related questions on the right hand side of this page? They may give you some ideas? — Ryan Vincent
syntax error here checkinDate => you need to reverse those symbols. => doesn't do what you think it does. — Funk Forty Niner

Gordon Linoff Gordon Linoff · Accepted Answer · 2015-10-10T14:57:49

If you want to know if a room is available during a period, then the logic looks like:

SELECT r.*
FROM rooms r 
WHERE NOT EXISTS (SELECT 1
                  FROM bookings b
                  WHERE b.roomid = r.roomid AND
                        b.checkinDate <= $formCheckOut AND
                        b.checkoutDate >= '$formCheckIn
                 );

I'm not sure if the equality comparison is needed for both of these. The logic is that a room is available if there are no bookings that start before the checkout date and that end after the check in date.

However, for ten rooms, I might suggest that you just keep a table of each room by day, say for the next ten years (add another year once per year). Such a table isn't very big and it is probably easier to understand how to use it. Plus, you can handle things like a couple reserve the room for a week, but only one person is in the room for the first 3 days.

Hotel room booking/reservation

1 Answers