2 Replies Latest reply on Jul 7, 2006 1:56 PM by Newsgroup_User

    CFIF inside a SQL statment

    Level 7
      I have a search page that has 3 possible entries. Service, City, & Keywords.

      Service and City are list/menus and cannot be null, but Keywords is a text
      box and can be null.

      I need to write a SQL statement to take the 3 possible parameters and test
      them against the database. Since the first 2 are never null, I can write
      them right inside the SQL string, but how can I only check for the keywords
      if there is something inside the box?

      Can I use a <cfif> statement inside the sql string to only check the 3rd
      parameter if it is not NULL?

      I wrote this:

      Select company, name, phone, city, state, username FROM members WHERE
      city = Form.City AND state = Form.state AND keywords LIKE Form.keywords
      ORDER BY company ASC

      Also, since I am passing it from a search page to a results page, would it
      be URL.City verses FORM.City, etc.

      Also would like to do this in a component and pass the 3 variables as
      arguments with the city and service being required and the keywords not.
      However, I have no idea on what the procedure is for this.

      I wrote:
      <cfcomponent>
      <cffunction name="ServiceSearch" access="remote" returntype="query">
      <cfargument name="service" type="numeric" required="true">
      <cfargument name="city" type="numeric" required="true">
      <cfargument name="keywords" type="string" required="false">
      Select company, name, phone, city, state, username FROM members WHERE
      city = Form.City AND state = Form.state AND keywords LIKE Form.keywords
      ORDER BY company ASC
      <cfreturn ServiceResults>
      </cffunction>
      </cfcomponent>

      Do I invoke the component from the results page?

      Well, I need to solve the first issue before I attempt the second. Thanks!

      --
      Wally Kolcz
      Developer / Support
      ProjectProofing.com


        • 1. Re: CFIF inside a SQL statment
          Level 7
          Actually, my component looks like this: (I forgot the cfquery..lol)

          <cfcomponent>
          <cffunction name="ServiceSearch" access="remote" returntype="query">
          <cfargument name="service" type="string" required="true">
          <cfargument name="state" type="string" required="true">
          <cfargument name="keywords" type="string" required="false">
          <cfquery name="ServiceResults" datasource="#Request.MainDSN#">
          Select company, name, phone, city, state, username FROM members WHERE
          service = Form.service AND state = Form.state
          <cfif Form.keywords NEQ "">
          AND keywords LIKE Form.keywords
          </cfif>
          ORDER BY company ASC
          </cfquery>
          <cfreturn ServiceResults>
          </cffunction>
          </cfcomponent>


          • 2. Re: CFIF inside a SQL statment
            Level 7
            Yes you can put <cfif ...> statements inside a <cfquery...> tag to
            modify the SQL string.

            WHERE this = that AND
            <cfif len(trim(form.something))>
            AnotherThing = '#form.something#'
            </cfif>

            Is valid.

            Whether the form variables on the action page are URL or FORM depends on
            the method of your <form ...> tag. method="post" creates form variables
            on the action page, method="get" creates URL variables on the action page.

            You write <cfargument ...> tags for each argument you want to pass into
            a function.

            <cfargument name="city" required="true" ...>
            OR
            <cfargument name="foobar" required="false" default="" ...>

            You then use these in the function with the arguments scope.
            #arguments.city# and #arguments.foobar#

            You then pass these into the function from your calling page. There are
            numerous ways this can be done, here are a few.

            <cfinvoke .... city="Sacramento" foobar="george"/>

            <finvoke ... >
            <cfinvokeArgument name="city" value="Sacramento">
            <cfinvokeArgument name="foobar" value="george">
            </cfinvoke>

            <cfset myComponent = createObject("component","my.component.path.andName)>

            ...myComponent.aFunction("Sacramento","george")...
            ...myComponent.aFunction(city:"Sacrament",foobar:"george")...o

            Wally Kolcz wrote:
            > I have a search page that has 3 possible entries. Service, City, & Keywords.
            >
            > Service and City are list/menus and cannot be null, but Keywords is a text
            > box and can be null.
            >
            > I need to write a SQL statement to take the 3 possible parameters and test
            > them against the database. Since the first 2 are never null, I can write
            > them right inside the SQL string, but how can I only check for the keywords
            > if there is something inside the box?
            >
            > Can I use a <cfif> statement inside the sql string to only check the 3rd
            > parameter if it is not NULL?
            >
            > I wrote this:
            >
            > Select company, name, phone, city, state, username FROM members WHERE
            > city = Form.City AND state = Form.state AND keywords LIKE Form.keywords
            > ORDER BY company ASC
            >
            > Also, since I am passing it from a search page to a results page, would it
            > be URL.City verses FORM.City, etc.
            >
            > Also would like to do this in a component and pass the 3 variables as
            > arguments with the city and service being required and the keywords not.
            > However, I have no idea on what the procedure is for this.
            >
            > I wrote:
            > <cfcomponent>
            > <cffunction name="ServiceSearch" access="remote" returntype="query">
            > <cfargument name="service" type="numeric" required="true">
            > <cfargument name="city" type="numeric" required="true">
            > <cfargument name="keywords" type="string" required="false">
            > Select company, name, phone, city, state, username FROM members WHERE
            > city = Form.City AND state = Form.state AND keywords LIKE Form.keywords
            > ORDER BY company ASC
            > <cfreturn ServiceResults>
            > </cffunction>
            > </cfcomponent>
            >
            > Do I invoke the component from the results page?
            >
            > Well, I need to solve the first issue before I attempt the second. Thanks!
            >