Skip to main content
N
Newsgroup_User
Inspiring
May 15, 2006
Question

Best Practice

  • May 15, 2006
  • 2 replies
  • 351 views
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#')


This topic has been closed for replies.

2 replies

N
Newsgroup_UserAuthor
Inspiring
May 15, 2006
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
>
>


T
The_ScareCrow
Inspiring
May 15, 2006
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
T
The_ScareCrow
Inspiring
May 15, 2006
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
Terms & ConditionsAccessibility statement
Using the community Terms of use Privacy Cookie preferences Do not sell or share my personal information ad choicesAdChoices