How to implement check availability in hotel reservation system

[jyotiu]

Hi ppl

 

I am developing website for my friend's very small resort, please see www.shivgangaresorts.com/dev

 

this resort have 8 rooms in total and all rooms are of same type so every room is equally priced.

 

I just need code or logic to check availability of given no. of rooms on given check in and check out dates.

 

when somebody makes a reservation, details that go in reservation table is:

booking_id

customer name

email

check-in date

check-out date

no. of rooms

 

Please help this is very critical as i will be integrating paypal also, so there should be no confusions...

 

thanks in advance

[ignace]

Assuming your database looks like:

 

rooms (rooms_id, rooms_beds, rooms_price, ..)

customers (customers_id, customers_name, ..)

 

bookings (bookings_id, bookings_customers_id, bookings_rooms_id, bookings_reservation_date, bookings_check_in, bookings_check_out, ..)

 

Then the code would be something similar like:

 

SELECT count(*) as available_rooms
FROM rooms
WHERE rooms_id NOT IN (
    # select all rooms wich are currently in use or will be used soon
    SELECT bookings_rooms_id
    FROM bookings
    WHERE (bookings_reservation_date > NOW() AND bookings_reservation_date < NOW() + 14) # room reservation within 14 days (adjust if needed)
    OR bookings_check_out IS NULL # a customer is already using the room and has not yet checked_out
);