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.

1. member
2. Email
3. category
4. 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.

^_^