If in a 开发者_开发知识库mysql table RESERVATIONS there are ROOM_NUMBER, DATE_ARRIVAL and DATE_DEPARTED
With this I find the today free rooms
SELECT RPOM_NUMBER
FROM RESERVATIONS
WHERE (CURRENT_DATE() < DATE_ARRIVAL) OR (CURRENT_DATE() > DATE_DEPARTED)
How do I insert a record in RESERVATIONS if there is an available room in my preferable date of arrival and departure?
SELECT RPOM_NUMBER
FROM RESERVATIONS
WHERE (CURRENT_DATE() < DATE_ARRIVAL) OR (CURRENT_DATE() > DATE_DEPARTED)
If this query returns more than 0 rows, there are free rooms available.
Then just use INSERT statement like this
INSERT INTO reservations SET room_number = ROOM_NUMBER_FROM_PREVIOUS_QUERY, [other fields of reservation table...]
What you want is a conditional insert...look at this: http://forums.mysql.com/read.php?97,164551,164551
SQL generally does only part of the job you want to do. You want to write a program that will find available rooms then create a reservation for one of those rooms. SQL can do the finding part (with SELECT) and the creating part (with INSERT). But the part where you offer the list of rooms to the user, let the user select a room, and make a decision whether to create the reservation usually happens in some procedural language that issues SQL statements to affect the database.
What other language are you using in this project?
精彩评论