0
"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 BETWEEN statements). The end result is always a syntax error. Is there an issue with running two betweens?

Thanks, Ryan

Haim Evgi
  • 123,187
  • 45
  • 217
  • 223
Ryan
  • 5
  • 1
  • 3
    Most probably because $room['roomtype'] is empty. This SQL is wrong `roomtype = AND`, mysql wait for something after `=` – RageZ Nov 29 '10 at 06:35
  • If you could assign the query to a variable and print it, it would help us debug better. – Nigel Nov 29 '10 at 06:41
  • plus I am not really sure but it seems your code would suffer from SQL injection .... you probably want to start to use parameterized sql queries. http://www.codinghorror.com/blog/2005/04/give-me-parameterized-sql-or-give-me-death.html – RageZ Nov 29 '10 at 06:41
  • 2
    Please post the query **after** the values of variables have been substituted into the SQL if you want a definitive answer instead of just lots of guesses. – Mark Byers Nov 29 '10 at 06:59
  • @Mark Byers: when he will get plain sql - he will be able to solve this issue himself ;-) The question is about debug "art" for now :-) – zerkms Nov 29 '10 at 07:03

5 Answers5

2

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.

Community
  • 1
  • 1
RageZ
  • 26,800
  • 12
  • 67
  • 76
  • @RageZ: happy times, when trivia question about placeholders could collect 120+ upvotes :-)) – zerkms Nov 29 '10 at 07:05
  • @Zerkms: yes, I wasn't on stackoverflow at that time. Too bad! I could have reach the 50k plus reputation ;-) – RageZ Nov 29 '10 at 07:06
  • @RageZ: Though prepared statements are wounderful they can be a major PITA once you try to switch to another database vendor. I think you should point out this issue as it's not obvious. – aefxx Nov 29 '10 at 07:10
  • @Aefxx: that's true after most of the time you have some DB abstraction layer like PDO to deal with this kind of things. – RageZ Nov 29 '10 at 07:14
  • @aefxx: the main issue in this case will be using vendor-specific sql dialects, not php at all. – zerkms Nov 29 '10 at 07:18
  • @zerkms Right, that's causing even more problems. Combine both - prepared statements that use vendor-specific features - and you'll be banging your head against the wall. – aefxx Nov 29 '10 at 07:21
  • The problem was one of the $room array variables was empty... I'm up to late working on a silly school project for a database class. The instructor said we didn't have to worry about security but I did read up on those links and found them interesting. Thanks everyone for your help!! – Ryan Nov 29 '10 at 07:27
  • @aefxx: and i still don't see reasons to limit myself (not using specific dialect is obviously limiting). When you worry about non-sql-92 query, you write the simpler ones, you don't use database features, and as a result you get bad berformance. So you decide to migrate to another DBMS, which should be faster. But it will not, because you still don't use its features. Sorry for being captain obvious ;-) – zerkms Nov 29 '10 at 07:28
  • @Ryan: school project late night, just sound like me in old days ;-) good luck. – RageZ Nov 29 '10 at 07:35
  • @zerkms There a many more reasons to migrate: cost, availability, support, simple preference ... just to name a few. Nevertheless, you should note that I'm PRO prepared statements and additions to the standard. One should simply be aware of their drawbacks. Captain Obvious to the rescue ^^ – aefxx Nov 29 '10 at 07:36
1
  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).

aefxx
  • 24,835
  • 6
  • 45
  • 55
  • 1
    Yep, you're right, but he asked whether the use of multiple `BETWEENS` could cause the error. As you will probably agree the above statement is perfectly legal, which in turn would show the OP that his error is to be found within the parameters rather than the statement itself. – aefxx Nov 29 '10 at 06:55
  • yeah, but he asked that because he don't know how to debug. So in these cases I prefer to answer about the **actual** issue, which will help OP to get a working code, regardless the *wrong question* ;-) – zerkms Nov 29 '10 at 07:01
0

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');
simon
  • 15,344
  • 5
  • 45
  • 67
  • 1
    And how this will help to non-existent variable? One more braces will magically bring some value to $room['roomtype']? ;-) – zerkms Nov 29 '10 at 06:44
  • same here ;-) that doesn't anything a good old `var_dump($sql)` would help – RageZ Nov 29 '10 at 06:46
  • the point is to isolate the first clause, to prevent the syntax error. Based on the OP's example, fixing the logic is a little beyond the scope... – simon Nov 29 '10 at 07:23
0

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'))"
Poonam Bhatt
  • 10,154
  • 16
  • 53
  • 72
  • @zerkms +1 Braces are a programmer's bread and butter. Since they are free it's always good to have like a gazillion of them :D – aefxx Nov 29 '10 at 07:13
0

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."')
RageZ
  • 26,800
  • 12
  • 67
  • 76
Pradeep Singh
  • 3,582
  • 3
  • 29
  • 42