CODEWITHSUNDEEP
mysqlsqlcoldfusion
Donnyschr
Donnyschr

Reputation: 111

More then one WHERE condition causes error: You have an error in your SQL syntax;

Well. Whenever i try to get more than one WHERE condition in a it just gives me a SQL syntax error.

The error:

Error Executing Database Query.

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 'read = '0'' at line 3

Resources:
Enable Robust Exception Information to provide greater detail about the source of errors. In the Administrator, click Debugging & Logging > Debug Output Settings, and select the Robust Exception Information option.
Check the ColdFusion documentation to verify that you are using the correct syntax.
Search the Knowledge Base to find a solution to your problem.
Browser     Mozilla/5.0 (Windows NT 6.3; WOW64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/38.0.2125.104 Safari/537.36
Remote Address      ::1
Referrer    
Date/Time   21-Oct-14 08:27 PM

The SQL query itself:

<cfquery name = "getUserAlerts" datasource = "#DSN#">
SELECT *
FROM user_alerts
WHERE client_id = '#clientUser.id#' 
AND read = '0'
</cfquery>

On my sight, there is nothing wrong with the SQL query. And it just should work, is there anyone who can see the mistake i written? Or possible give the fix?

Thanks in advance!

Edit:

Thanks for the quick answers you gave me. The backticks worked! Thanks guys!

Upvotes: 3

Views: 100

Answers (2)

Leigh
Leigh

Reputation: 28873

The error is probably due the fact that READ is a reserved word. Either rename the column entirely (preferable) or escape it using backticks.

As an aside, you should be using cfqueryparam on all variable query parameters. CFQueryparam offers a number of benefits (data type checking, improving query performance, etcetera). One of the most important is helping to prevent sql injection, which can be an issue even with quoted strings.

Upvotes: 7

Marc B
Marc B

Reputation: 360702

read is a reserved word: http://dev.mysql.com/doc/refman/5.5/en/reserved-words.html

You'll have to escape it:

WHERE ...
 AND `read` = '1'
     ^----^--

note that those are backticks, not single-quotes.

Upvotes: 4

Related Questions