Best Practice

[Newsgroup_User]

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#')

[The_ScareCrow]

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

[Newsgroup_User]

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
>
>

[The_ScareCrow]

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