Posted by ad on 10/13/56 11:46

Hi,
I creating a reservation style system which books out rooms for periods
of time. I need way of seeing which rooms are currently booked out.

At the moment I have the following - a table for the accomodation and a
booking table (student_accom with foriegn key) This query finds all the
rooms that are not booked - sort of...

Problem I'm having is:
If a room is booked from 2005-05-16 to 2005-06-22 and a someone tries
to make a reservation for dates outside both the checkin and checkout
times, then the room is shown as free..e.g. checkin 2005-05-14 to
2005-06-27 will show the room that was booked above as free. Can anyone
assist? I'm sure this has been done many times before.

Appreciate any assistance
Thanks

SELECT accomodation.* FROM accomodation left join student_accom
ON accomodation.accom_id = student_accom.accom_id AND
(
('" . $checkin_rs_accom_avail . "' between student_accom.start_date and
student_accom.leave_date) or
('" . $checkout_rs_accom_avail . "' between student_accom.start_date
and student_accom.leave_date)
)
WHERE (student_accom.start_date IS NULL or student_accom.leave_date IS
NULL) and accomodation.town = '" . $accomtown_rs_accom_avail . "' order
by rand()"

• Next in forum: How can I get all enum values of field SMTH?
• Prev in forum: Re: How can I specify 'NO' as default value for datetime value or convert default value into 'NO' in query?
• Thread view: between dates sql/php

[Reply to this message]