Dynamic WHERE (more complex)

[raulriera]

As you can see for this code im not that good with SQL hehe but I manage...
works perfect until I try to pass two or more words to it (its a search engine
code and the variables are passed via querystring as so word1+word2+word3+etc)

Any suggestions??

The preserveSingleQuotes functions doesnt seem to work with 2 or more words in
it


<cfquery name="buscar" datasource="metroBD">
SELECT FotoLocal.urlThumb, [Local].idLocal, [Local].idRubroLocal,
[Local].nombre,
[Local].telefono
FROM FotoLocal INNER JOIN [Local] ON FotoLocal.idLocal = [Local].idLocal

<!--- Buscar por palabra clave --->
<cfif isDefined("nombreLocal") AND nombreLocal IS NOT "">

<!--- Loop sobre las palabras creadas por el usuario para formar la linea
SQL
que realizar? la b?squeda del local por nombre --->
<cfset lineaSQL = "0=1">

<cfloop index="nombreLocal" list="#nombreLocal#" delimiters = " :/">
<cfset lineaSQL = lineaSQL & " OR [Local].nombre LIKE " & "'%" &
nombreLocal & "%'">
</cfloop>

WHERE (#PreserveSingleQuotes(lineaSQL)#)

<!--- Buscar por rubros --->
<cfelseif isDefined("idRubro") AND idRubro GT 0>
WHERE [Local].idRubroLocal = <cfqueryparam cfsqltype="cf_sql_numeric"
value="#idRubro#">

</cfif>
</cfquery>

[jdeline]

It would help if you could print out lineaSQL after it has been assembled.
That way we could see what you're dealing with. If you are able to print the
actual instance of the SQL that is causing the error, all the better.

[raulriera]

the SQL is this one

0=1 OR [Local].nombre LIKE '%word1%' OR [Local].nombre LIKE '%word2%' OR
[Local].nombre LIKE '%word3%'

but when using it in a cfquery it returns an error.. even if I use
preserveSingleQuotes

[coderWil]

Your error message indicates that each variable is surrounded by double
single-quotes.

if your variables are called 'word2' and 'word2' it appears that the code
previous to the query is causing the problem. The variable output to the query
seems to be ''word1''.

Please post the entire page so we can help better.

[raulriera]

Hmmm well yes the error is that one, but the thing is that I dont know what is
causing it.. since as you can see by the code im not using doble quotes on the
variables and none of the words typed in contains quotes either...

About posting the whole page.. that is all... you have the code, the "desired"
output using cfdump and the error page

[jdeline]

"since as you can see by the code im not using doble quotes on the variables ..." CoderWil suggests you have two consecutive single quotes around word2, not double quotes.

[cf_menace]

PreserveSingleQuotes() adds a single quote wherever one is found in order to
preserve the full string in SQL. Since you're already starting your WHERE
statement with ' 0=1', you can just create the SQL dynamically in the loop
without involving a variable:

[raulriera]

Thanks thats is a perfect answer.. the thing is that if I use just #lineaSQL#
without the function I still get the error.. I solved it with WHERE
#Replace(lineaSQL,"''","'","All")# which is "dumb" to do but works.. ur
solution was the other way I was going to do it (the right way since involves
the <cfqqueryparam> tag but I didnt want to rewrite the little thing hehe

Thanks for ur help.. I will use ur code sample next time