Online Hotel Room Booking Concept In Php

I am working with online hotel room booking concept room(table) roomId name Price Totalrooms 1 A 100 10 2 B 150 20 How could I build the d

Solution 1:

You need another table with bookings

  ID      roomId        checkInDate          checkOutDate
  1       1             4/6/2011 12:00PM     4/9/2011 11:00AM
  2       2             4/2/2011 12:00PM     4/3/2011 11:00AM
  3       1             4/9/2011 12:00PM     4/11/2011 11:00AM

Then when you add a new booking for each room, you need to make sure the following query results in a zero 0, or else the room is already booked.

//Count Scheduling Conflictsselectcount(roomid) from bookings where 
    bookings.checkOutDate > YouNewBookingCheckInDate and
    bookings.checkInDate < YouNewBookingCheckOutDate

So if your new booking request for Room 1 is on 4/5/2011 1:00PM, record #1 will be counted and the query will result in a 1 (indicating 1 conflict)