Dynamic Query in CF

[moiseszaragoza]

Ok what i am trying to do is a search funnction. I have 4 fields in my DB that i want to search on.

member
Email
category
ContractNumber


But i only want to search them if someone type something in those fields on the interface.

so my code like like


<CFSET SQL = "SELECT * FROM precontract ">
<CFSET SQL_Coubr = 0 >
<cfif form.member neq "">
<cfif SQL_Coubr eq "0">
<CFSET SQL = SQL& "WHERE ">
</cfelse>
<CFSET SQL = SQL& "OR ">
</cfif>
<CFSET SQL = SQL& " name LIKE '%#form.member#%' " >
<CFSET SQL_Coubr = 1 >
</cfif>
<cfif form.Email neq "">
<cfif SQL_Coubr eq "0">
<CFSET SQL = SQL& "WHERE ">
</cfelse>
<CFSET SQL = SQL& "OR ">
</cfif>
<CFSET SQL = SQL& " Email LIKE '%#form.Email#%' " >
<CFSET SQL_Coubr = 1 >
</cfif>
<cfif form.category neq "">
<cfif SQL_Coubr eq "0">
<CFSET SQL = SQL& "WHERE ">
</cfelse>
<CFSET SQL = SQL& "OR ">
</cfif>
<CFSET SQL = SQL& "OR category LIKE '%#form.category#%' " >
<CFSET SQL_Coubr = 1 >
</cfif>
<cfif form.ContractNumber neq "">
<cfif SQL_Coubr eq "0">
<CFSET SQL = SQL& "WHERE ">
</cfelse>
<CFSET SQL = SQL& "OR ">
</cfif>
<CFSET SQL = SQL& "OR ContractNumber LIKE '%#form.ContractNumber#%' " >
<CFSET SQL_Coubr = 1 >
</cfif>



But it gives me a error that reads Context validation error for tag cfif.

Thanks for all the help

[moiseszaragoza]

aparently i had a extra slash on the else </cfelse> instead of <cfelse>

[moiseszaragoza]

Now my problem is rrunning my quesry i know is good as i have tested on the server on the query analiser but i cant get it to run from the web site.

the original code that runes is


<cfquery datasource="mssqlcf_modelprod" dbname="modelprod" name="get_prememb">


SET QUOTED_IDENTIFIER OFF
SELECT *
FROM precontract
where name Like '#form.member#%'
ORDER BY name

</cfquery>



But I want it to run more like


<cfquery datasource="mssqlcf_modelprod" dbname="modelprod" name="get_prememb">


SQL

</cfquery>

as the var SQL = SELECT * FROM precontract WHERE name LIKE '%m%' OR email LIKE '%1%' OR phone LIKE '%2%' OR conID LIKE '%3%'

[sushi]

Just output the sql instead of "cfset'ing" it

<CFSET SQL = "SELECT * FROM precontract ">
<cfquery datasource="mssqlcf_modelprod" dbname="modelprod" name="get_prememb">
<CFSET SQL_Coubr = 0 >
<cfif form.member neq "">
<cfif SQL_Coubr eq "0">
<CFSET SQL = SQL& "WHERE ">
</cfelse>
<CFSET SQL = SQL& "OR ">
</cfif>
<CFSET SQL = SQL& " name LIKE '%#form.member#%' " >
<CFSET SQL_Coubr = 1 >
</cfif>
<cfif form.Email neq "">
<cfif SQL_Coubr eq "0">
<CFSET SQL = SQL& "WHERE ">
</cfelse>
<CFSET SQL = SQL& "OR ">
</cfif>
<CFSET SQL = SQL& " Email LIKE '%#form.Email#%' " >
<CFSET SQL_Coubr = 1 >
</cfif>
<cfif form.category neq "">
<cfif SQL_Coubr eq "0">
<CFSET SQL = SQL& "WHERE ">
</cfelse>
<CFSET SQL = SQL& "OR ">
</cfif>
<CFSET SQL = SQL& "OR category LIKE '%#form.category#%' " >
<CFSET SQL_Coubr = 1 >
</cfif>
<cfif form.ContractNumber neq "">
<cfif SQL_Coubr eq "0">
<CFSET SQL = SQL& "WHERE ">
</cfelse>
<CFSET SQL = SQL& "OR ">
</cfif>
<CFSET SQL = SQL& "OR ContractNumber LIKE '%#form.ContractNumber#%' " >
<CFSET SQL_Coubr = 1 >
</cfif>
</cfquery>
?

[moiseszaragoza]

Thanks that helps alot

[WolfShade]

I would avoid (as much as possible) using conditionals between CFQUERY tags. If you must, you must, but I prefer to set the SQL variable before getting to the CFQUERY, then just use #SQL# inside.

^_^