Multiple betweens in one statement

"SELECT * FROM reservation  WHERE roomnum = {$room['roomnum']} 
AND roomtype = {$room['roomtype']} 
AND (dateout NOT BETWEEN '$start' AND '$end' 
OR datein NOT BETWEEN '$start' AND '$end')"

When I run this query I get the following error:

You have an error in your SQL syntax;

check the manual that corresponds to your MySQL server version for the right syntax to use near 'AND (dateout NOT BETWEEN '2010-11-22' AND '2010-11-30' OR datei' at line 3

I have tried reformatting this query for a while (using () and removing the statements that come before the NOT BETW开发者_Go百科EEN statements). The end result is always a syntax error. Is there an issue with running two betweens?

Thanks, Ryan

I think you probably have to start debugging this kind of error by dumping the full sql and try it yourself using mysql client when you don't understand why it is not working.

I would do

$sql = "SELECT * FROM reservation  WHERE roomnum = {$room['roomnum']} 
AND roomtype = {$room['roomtype']} 
AND (dateout NOT BETWEEN '$start' AND '$end' 
OR datein NOT BETWEEN '$start' AND '$end')"; 

var_dump($sql);

and I think it's going to be obvious what is wrong.

Plus like I said in my comment if you don't escape the $room array, this code would suffer from sql injection vulnerabilities. You have better to use some parameterized queries, for your reference. Plus prepared statement performs better and make the code more readable in my honest opinion.

  SELECT *
    FROM reservation
   WHERE roomnum = {$room['roomnum']} 
     AND roomtype = {$room['roomtype']} 
 AND NOT ( datein BETWEEN '$start' AND '$end' 
     AND   dateout BETWEEN '$start' AND '$end' )

Having NOT x OR NOT y is essentially the same AS NOT (a AND b). This axiom is part of De Morgan's law. So if you are unsure about grouping use these and other boolean logic.

EDIT
As others pointed out before, the error seems to be an empty variable. So, to guarantee that your query won't break in case an empty roomtype or roomnum is given, you could single quote these paramaters which would result in a comparison against an empty string (NOTE: I do NOT recommend this strategy. Please check your variables BEFORE they're used in statements).

RageZ is probably right - rewrite it like this:

SELECT * FROM reservation WHERE
(roomnum = {$room['roomnum']} AND roomtype = {$room['roomtype']}) AND
(dateout NOT BETWEEN '$start' AND '$end' OR datein NOT BETWEEN '$start' AND '$end');

Can you try this

"SELECT * FROM reservation WHERE roomnum = {$room['roomnum']} AND roomtype = {$room['roomtype']} AND ((dateout NOT BETWEEN '$start' AND '$end') OR (datein NOT BETWEEN '$start' AND '$end'))"

Please use this syntax and check that $room['roomtype'] is not empty

SELECT * FROM reservation 
WHERE roomnum = '".$room['roomnum']."' AND roomtype = '".$room['roomtype']."'  
AND (dateout NOT BETWEEN '".$start."' AND '".$end."' OR datein NOT BETWEEN '".$start."' AND '".$end."')