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

"

"

X vBulletin 4.2.2 Debug Information

  • Page Generation 0.16713 seconds
  • Memory Usage 2,906KB
  • Queries Executed 13 (?)
More Information
Template Usage (34):
  • (1)SHOWTHREAD
  • (1)ad_footer_end
  • (1)ad_footer_start
  • (1)ad_global_above_footer
  • (1)ad_global_below_navbar
  • (1)ad_global_header1
  • (1)ad_global_header2
  • (1)ad_navbar_below
  • (1)ad_showthread_firstpost_sig
  • (1)ad_showthread_firstpost_start
  • (1)ad_thread_first_post_content
  • (1)ad_thread_last_post_content
  • (2)bbcode_code
  • (2)bbcode_php
  • (1)footer
  • (1)forumjump
  • (1)forumrules
  • (1)gobutton
  • (1)header
  • (1)headinclude
  • (1)headinclude_bottom
  • (6)memberaction_dropdown
  • (1)navbar
  • (4)navbar_link
  • (1)navbar_moderation
  • (1)navbar_noticebit
  • (1)navbar_tabs
  • (2)option
  • (6)postbit
  • (6)postbit_onlinestatus
  • (6)postbit_wrapper
  • (1)spacer_close
  • (1)spacer_open
  • (1)tagbit_wrapper 

Phrase Groups Available (6):
  • global
  • inlinemod
  • postbit
  • posting
  • reputationlevel
  • showthread
Included Files (26):
  • ./showthread.php
  • ./global.php
  • ./includes/class_bootstrap.php
  • ./includes/init.php
  • ./includes/class_core.php
  • ./includes/config.php
  • ./includes/functions.php
  • ./includes/functions_navigation.php
  • ./includes/class_friendly_url.php
  • ./includes/class_hook.php
  • ./includes/class_bootstrap_framework.php
  • ./vb/vb.php
  • ./vb/phrase.php
  • ./includes/functions_facebook.php
  • ./includes/functions_calendar.php
  • ./includes/functions_bigthree.php
  • ./includes/class_postbit.php
  • ./includes/class_bbcode.php
  • ./includes/functions_reputation.php
  • ./includes/functions_notice.php
  • ./packages/vbattach/attach.php
  • ./vb/types.php
  • ./vb/cache.php
  • ./vb/cache/db.php
  • ./vb/cache/observer/db.php
  • ./vb/cache/observer.php 

Hooks Called (70):
  • init_startup
  • friendlyurl_resolve_class
  • init_startup_session_setup_start
  • database_pre_fetch_array
  • database_post_fetch_array
  • init_startup_session_setup_complete
  • global_bootstrap_init_start
  • global_bootstrap_init_complete
  • cache_permissions
  • fetch_threadinfo_query
  • fetch_threadinfo
  • fetch_foruminfo
  • load_show_variables
  • load_forum_show_variables
  • global_state_check
  • global_bootstrap_complete
  • global_start
  • style_fetch
  • global_setup_complete
  • showthread_start
  • showthread_getinfo
  • strip_bbcode
  • friendlyurl_clean_fragment
  • friendlyurl_geturl
  • forumjump
  • cache_templates
  • cache_templates_process
  • template_register_var
  • template_render_output
  • fetch_template_start
  • fetch_template_complete
  • parse_templates
  • fetch_musername
  • notices_check_start
  • notices_noticebit
  • process_templates_complete
  • friendlyurl_redirect_canonical
  • showthread_post_start
  • showthread_query_postids
  • showthread_query
  • bbcode_fetch_tags
  • bbcode_create
  • showthread_postbit_create
  • postbit_factory
  • postbit_display_start
  • bbcode_parse_start
  • postbit_imicons
  • bbcode_parse_complete_precache
  • bbcode_parse_complete
  • postbit_display_complete
  • memberaction_dropdown
  • tag_fetchbit_complete
  • forumrules
  • navbits
  • navbits_complete
  • build_navigation_data
  • build_navigation_array
  • check_navigation_permission
  • process_navigation_links_start
  • process_navigation_links_complete
  • set_navigation_menu_element
  • build_navigation_menudata
  • build_navigation_listdata
  • build_navigation_list
  • set_navigation_tab_main
  • set_navigation_tab_fallback
  • navigation_tab_complete
  • fb_like_button
  • showthread_complete
  • page_templates