3 Replies Latest reply on May 15, 2006 12:20 AM by The ScareCrow

    Best Practice

    Level 7
      i have the following query to perform

      <cfquery name="products" datasource="#client.dsn#"
      username="#client.username#" password="#client.password#">

      Select * from Products
      where product='#product_add1#'
      and product='#product_add2#'
      and product='#product_add3#'
      and product='#product_add4#'</cfquery>

      What is the best way to code this if all 4 variables may not always be
      present.

      For example there may be 1 - 4 variables.

      Can I do something like?

      Select * from Products
      where
      ('#product_add1#' IS NOT NULL and product='#product_add1#')
      and ('#product_add2#' IS NOT NULL and product='#product_add2#')
      and ('#product_add3#' IS NOT NULL and product='#product_add3#')
      and ('#product_add4#' IS NOT NULL and product='#product_add4#')


        • 1. Re: Best Practice
          The ScareCrow Level 1
          First you have to determine what logic the query is to follow
          With the query you have all variables have to match the product for any records to be returned.
          So, if one variable is not present then no records will be returned.
          I think you might be after an OR here.
          But you could also turn it into a list

          <cfset myList = "">
          <cfif Len(Trim(product_add1))>
          <cfset result = ListAppend(myList, #product_add1#)>
          </cfif>
          <cfif Len(Trim(product_add2))>
          <cfset result = ListAppend(myList, #product_add2#)>
          </cfif>
          <cfif Len(Trim(product_add3))>
          <cfset result = ListAppend(myList, #product_add3#)>
          </cfif>
          <cfif Len(Trim(product_add4))>
          <cfset result = ListAppend(myList, #product_add4#)>
          </cfif>
          Then the query

          Select * from Products
          where product IN (#ListQualify(myList, "'", ",", "All")#)

          Ken
          • 2. Re: Best Practice
            Level 7
            Thanks Ken for the tip. I think the simple use of the OR will work in this
            example.

            One question i do have is though. Say I use this query

            Select * from Products
            where product='#product_add1#'
            OR product='#product_add2#'
            OR product='#product_add3#'
            OR product='#product_add4#'

            and say

            products.recordcount EQ 2

            eg; customer enters 2 invalid part numbers

            Is there a way to determine which #product_add# variables were not found in
            the database. So i can present the user with the invalid numbers to reenter.



            "The ScareCrow" <info@krcaldwell.com> wrote in message
            news:e48uv8$odn$1@forums.macromedia.com...
            > First you have to determine what logic the query is to follow
            > With the query you have all variables have to match the product for any
            > records to be returned.
            > So, if one variable is not present then no records will be returned.
            > I think you might be after an OR here.
            > But you could also turn it into a list
            >
            > <cfset myList = "">
            > <cfif Len(Trim(product_add1))>
            > <cfset result = ListAppend(myList, #product_add1#)>
            > </cfif>
            > <cfif Len(Trim(product_add2))>
            > <cfset result = ListAppend(myList, #product_add2#)>
            > </cfif>
            > <cfif Len(Trim(product_add3))>
            > <cfset result = ListAppend(myList, #product_add3#)>
            > </cfif>
            > <cfif Len(Trim(product_add4))>
            > <cfset result = ListAppend(myList, #product_add4#)>
            > </cfif>
            > Then the query
            >
            > Select * from Products
            > where product IN (#ListQualify(myList, "'", ",", "All")#)
            >
            > Ken
            >
            >


            • 3. Re: Best Practice
              The ScareCrow Level 1
              You could do this by using the following cf functions

              First convert the query column to a list
              ValueList(query.column [, delimiter ])

              Then use
              ListContainsNoCase(list, substring [, delimiters ])
              to see which "products" were not in the list.

              Ken