sql query to check availability

Anon

Hi,

I am developing a hotel reservation system using PHP and MySQL. The query to check availability is causing problems: I have a reservation, room, rates and customer table.
Room contains room type and room no. Reservation contains cust id, room no, date from, date to etc..

The customer enters room type ($type), date from ($arrival_date) and date to, and availability is checked in the reservation table. I am using the following query:

SELECT room.room_no, reservation.reservation_id FROM room LEFT JOIN reservation ON room.room_no = reservation.room_no WHERE $arrival_date NOT BETWEEN reservation.arrival_date AND reservation.dept_date AND room.type = '$type'

If a room is available on the arrival night, I then check the number of nights they want to stay, and loop through the query for each night, to see if the room is still available.
If so the reservation is made.

But the query is returning unexpected results sometimes. I have tried many variations, but to no use. Please help in any way.

factory21

a record in reservation may not exist and thus, all fields associated with the table will be NULL.

Thus, in your where clause, you're basically comparing to null values which sometimes produces un-intuitive results!

In this case, your BETWEEN clause will evaluate to an effective FALSE whenever a matching reservation is not found for the room. I don't think this is what you want.

It usually makes sense (and is potentially more efficient) to move statements which only involve the joined table to the JOIN clause. If you do this, the SELECT will return the room and all you need to do is check one of the reservation fields (ideally, the primary key) to see if it's already booked.

Anon

Thanks for the reply! I am not sure what exactly you are saying I should do. I would prefer to use just one query. Could you please show me the sql query I should be using? The logic I am using is: If an empty set is returned, there are no rooms available of that type on that date. If a room is available, the room no. will be returned. If 2 rooms are available, 2 room no's will be returned. Also, I had to populate my reservation table with fake reservations with all room types initially, to get the query working. Else it just returned an empty set.

Thanks,
Louise