www.webdeveloper.com
Results 1 to 6 of 6

Thread: Dynamic Query in CF

  1. #1
    Join Date
    Feb 2006
    Location
    Ft Lauderdale
    Posts
    299

    Dynamic Query in CF

    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
    PHP Code:

    <CFSET SQL "SELECT * FROM precontract ">
    <
    CFSET SQL_Coubr >
        <
    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 >
        </
    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 >
        </
    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 >
        </
    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 >
        </
    cfif
    But it gives me a error that reads Context validation error for tag cfif.

    Thanks for all the help

  2. #2
    Join Date
    Feb 2006
    Location
    Ft Lauderdale
    Posts
    299
    aparently i had a extra slash on the else </cfelse> instead of <cfelse>

  3. #3
    Join Date
    Feb 2006
    Location
    Ft Lauderdale
    Posts
    299
    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

    PHP Code:
    <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

    Code:
    <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%'

  4. #4
    Join Date
    Mar 2008
    Posts
    123
    Just output the sql instead of "cfset'ing" it
    Code:
    <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>
    ?

  5. #5
    Join Date
    Feb 2006
    Location
    Ft Lauderdale
    Posts
    299
    Thanks that helps alot

  6. #6
    Join Date
    Dec 2002
    Location
    St. Louis, MO, USA
    Posts
    1,582
    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.

    ^_^

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
HTML5 Development Center



Recent Articles