Please Someone Help me

[sayeed11]

Hi,

I am creating a hotel reservation system using php and mysql, the problem i have is how to avoid double booking of a room.
Say that there is one room available and 2 people ty to book the room at the same time, i want it to avoid clashes

has it got to do something with concurrency and lock? please let me know as soon as possible
thanx

[bradgrafelman]

What you could do is create a table solely for reservations.

Assuming that one room can never be occupied by two different entities at any time, you could add a unique key to the "room_id" field.

Alternatively, you could lock the table for writing (see this page in the MySQL manual), insert the reservation, and then unlock the table. I'm no SQL guru, and this might actually be the preferred method, so... perahps someone with more DB experience can comment on these two methods.

[Sxooter]

First off, use innodb tables as you need transactional integrity and possibly foreign keys here.

Create a table that has the two columns reservation_date and room_number and put a unique key on those two fields together, and a reference to another table with the customer information (name number, etc...). Then, when you want to make a reservation you check that table. If the day room is free, you try to insert. If the insert fails, then another reservation agent must have grabbed it underneath you, and you thrown an error and try another room.