listing rooms available[hotel reservation]

Question

In the not in query it is still returning the roomname with cancelled status but when I removed the query for trapping of dates the status !='cancelled' is working. Is there something wrong with the query for trapping of dates?

$tbl_comp=mysql_query("SELECT * FROM roomlist 
WHERE type='$roomtype' and status = '1' 
and name NOT IN(select name from roomreservation where status  !='cancelled'

and 
arrival between '$arrival' and '$departure'
or departure between '$arrival' and '$departure'
or '$arrival'  between arrival and departure
or '$departure' between arrival and departure)");

Can you give us the table structure with sample data and expected output on _www.sqlfiddle.com_, so it would be easier for us to work on? — Orangecrush, Feb 07 '13 at 05:46
It looks like you're doing a double negative. do you want the rooms that aren't cancelled or the ones that are? If you want the ones that aren't, take off the boom (!) — Scotch, Feb 07 '13 at 05:49
Also, it's hard to know for sure what you meant, but you might be getting tripped up by AND having a higher precedence than OR. Try surrounding the four BETWEEN conditions with parentheses. — JeffB, Feb 07 '13 at 05:52
@Scotch He seems to be looking for vacant rooms, i.e. rooms with no booking that are both active and overlapping on dates. — Terje D., Feb 07 '13 at 06:37

score 0 · Accepted Answer · answered Feb 07 '13 at 06:32

The main problem with the query is that status != 'cancelled' applies only to the first part of the date test.

Another problem is that BETWEEN does an inclusive comparation, while you probably accept new arrivals on the same day as the departure of another booking. The you need to compare the dates using < and > instead of BETWEEN. However, the test in the inner query can be simplified to this:

SELECT name FROM roomreservation
WHERE status != 'cancelled'    -- if cancelled: no conflict 
AND '$arrival' < departure     -- if $arrival >= departure: no conflict
AND '$departure' > arrival     -- if $departure <= arrival: no conflict

score 0 · Answer 2 · answered Dec 02 '15 at 09:13

0

its work for me

SELECT
    *
FROM
    reservation
WHERE
    room = "3" AND
    status = 1 AND
    '2015-12-03' BETWEEN arrival AND departure OR
    '2015-12-06' BETWEEN arrival AND departure

answered Dec 02 '15 at 09:13

Bruno Ribeiro

1,280
16
21

listing rooms available[hotel reservation]

2 Answers2

Linked