Insert if there are available rooms using mySQL

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?