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开发者_高级运维atabase to allow for users to search for the availability of a room (with a from and to date)? Also, what would be the best table structure for storing bookings?

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 Conflicts
select count(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)