but take out the cfoutputs from within the cfquery. Try commenting out all
your code then one by one introduce the where clauses to see if you can spot
the one causing the problem. I think you might have misunderstood what was
meant by using the parenthesis. The conditional operators OR and AND have a
precedence which you sometimes need to change, by enclosing certain
statements in parenthesis you can group conditions accordingly.
Start with the basic SQL statement then slowly add the different clauses and
you should spot the problem.
Ade
-----Original Message-----
From: Trevor Smith [mailto:[EMAIL PROTECTED]
Sent: 28 May 2004 07:59
To: CF-Newbie
Subject: SQL sytax not jiving with Coldfusion
Hello,
I'm trying to make one part of a SQL statement occur before the rest of
the statement. I read on this site here:
http://www.1keydata.com/sql/sqlandor.html that one could use parenthesis to
do just that, but it does not seem to be working for me. Everything works
fine if I do not use the parenthesis, but as soon as I add them it doesn't
work right. Here's the site: stiglercenter.org And here's the code:
<cfif NOT IsDefined("Form.Submit")>
<cflocation url=""> <cfelse>
<cfset whereText = "0 = 0">
<cfif Form.authorFirstName NEQ "">
<cfset whereText = whereText & " AND (authorOneFirstName LIKE " & "'%" &
"#form.authorFirstName#" & "%'" & " OR authorTwoFirstName LIKE " & "'%" &
"#form.authorFirstName#" & "%')">
</cfif>
<cfif Form.authorLastName NEQ "">
<cfset whereText = whereText & " AND (authorOneLastName LIKE " & "'%" &
"#form.authorLastName#" & "%'" & " OR authorTwoLastName LIKE " & "'%" &
"#form.authorLastName#" & "%')">
</cfif>
<cfif Form.year NEQ "">
<cfset whereText = whereText & " AND articleYear = " & #form.year#>
</cfif>
<cfif Form.title NEQ "">
<cfset whereText = whereText & " AND articleTitle LIKE " & "'%" &
"#form.title#" & "%'">
</cfif>
</cfif>
<cfquery datasource="sti_datasource" name="searchQuery">
SELECT * FROM articles WHERE
<cfoutput>#preserveSingleQuotes(whereText)#</cfoutput>
</cfquery>
<html>
<head>
<title>search results test</title>
<meta http-equiv="Content-Type" content="text/html; charset=iso-8859-1">
</head>
<body>
<cfoutput>#preservesinglequotes(whereText)#</cfoutput>
<cfoutput query="searchQuery">
#articleTitle# - #authorOneLastName#, #authorOneFirstName# -
#authorTwoLastName#, #authorTwoFirstName# - #articleYear# <br> <br>
</cfoutput>
</body>
</html>
Thanks in advance,
Trevor Smith
[Todays Threads] [This Message] [Subscription] [Fast Unsubscribe] [User Settings]
