problem with SELECT WHERE IN

[Dax_Trajero]

I'm having a problem with a SELECT clause...

the variables...

<cfset MyString=#getProducts.matches_with#>

<cfset myArrayList = ListToArray(MyString)>

the query...

SELECT *

FROM pricelist

WHERE supplier_code IN ('#myArrayList[1]#' , '#myArrayList[2]#')

The problem is, the select clause is only ever retrieving one row, despite their being several matches

The clause works fine when I change the WHERE line to

WHERE supplier_code IN ('E1775' , 'R1771')

What am I doing wrong here ?

[ilssac]

Have you output the variables myString and|or myArrayList to confirm that they contain the values you think they do?

You probably want to look at some other CFML functionality that could simplify what you are trying to do.

I.E.

valueList() and listQualify() or the ultimate, <cfqueryparam value="..." list="yes"...>

[rab890]

Greetings,

I will be out of the office on Thursday April 29th and Friday April 30th, returning on Monday May 3rd. I will return all emails when I return. If you require immediate assistance, please contact Rob Panico at (518) 827-8282, rob_panico@altsystem.com.

Thanks,

Ron Barth

Alternative Systems

www.altsystem.com

603.537.9473

[Dax_Trajero]

Yes, I've output the variables and the array they're fine.

The WHERE clause works when I manually type in values like 'E1212' , but when I change that to a variable like '#myVar#' it doesn't work

The problem definitely seems to be in the WHERE clause

Any ideas ?

[ilssac]

What does the generated SQL statement look like.

As part of the record set object and|or part of the error message is the actual SQL string sent by ColdFusion to the database.  What does this look like?

[Dax_Trajero]

there's no error being reported

when I output the contents of the resulting SELECT clause I know it's not working as it's only finding 1 row when it should be finding several.

I've got a feeling it could be something to do with preservesinglequotes which I'm now looking into

http://www.adobe.com/livedocs/coldfusion/6.1/htmldocs/functa53.htm

[ilssac]

P.S.  outputing the sql of the <cfquery...> result structure would show you the sql statement that was actauly sent to the database to generate the recrod set.

I.E.

<cfquery name="myQry" result="debugStuff" ...>
...
</cfquery>
<cfoutput>#debugStuff.sql#</cfoutput>

[Dax_Trajero]

ah, OK - I'll remember that debug tip!

[Dax_Trajero]

Just wanted to say thanks all for the advice - I'm back on track now.

As you know I had a field 'matches_with' in the merchandise table which contains a comma separated list, which I now accept is bad design.

I wrote the following code to take each field and put it into a new table called matches_with, which contains two fields

product_code , matches_with_product_code. All done.

<cfloop query="getData">

    <!--- create an array based on the csv list of items in matches_with field --->

    <cfset myArray = ListToArray(#getData.matches_with#)>

     <!--- skip if no items in array --->   

    <cfif #ArrayLen(myArray)# gt 0>

        <!--- loop through item in the array --->

        <cfloop from="1" to="#ArrayLen(myArray)#" index="i">

            <cfquery name="insert" datasource="foo">

                <!--- insert each array item into SQL table --->

                INSERT INTO 00_matches_with (product_code, matches_with_product_code)

                VALUES ('#getData.product_code#' , '#myArray#')

            </cfquery>

        </cfloop>

    </cfif>

</cfloop>

[ilssac]

Just to keep you on track to learn all the Coolness of ColdFusion.

The <cfloop...> tag as many forms, including a list form that will loop over a delimited list.

I.E (I am sure getting a lot of use from this in this thread)

<cfloop list="#getData.matches_with#" index="matchProdID">
  <cfquery name="insert" datasource="foo">
    <!--- insert each array item into SQL table --->
    INSERT INTO 00_matches_with (product_code, matches_with_product_code)
    VALUES ('#getData.product_code#' , '#matchProdID#')
  </cfquery>

</cfloop>

[Dax_Trajero]

thanks Ian

Is there a quick way to run through each field in a table and remove spaces in it's string ?

[ilssac]

Well, if you are looking at quick you should be looking at tools in your database management system of choice.

But if you just want easy and maybe not so quick but you're only doing it once, cfml can be used if the data isn't too large.

In CFML you have the trim() function that will trim spaces from the beginning and ending of the string, but not in the middle.

For all spaces, that would be the rereplace() function that allows the use of regex.  Something like reReplace(theString,"/s","ALL")

[Dax_Trajero]

Thanks Ian

Will give Trim a try, although I'm using PremiumSoft Navicat MySQL

[Dan_Bracuk]

There are two ways to improve this.  One will make it run faster, the other might.

First, since you can loop through a list, converting it to an array doesn't gain you anything, so why bother.

Second, you can put the loop inside the query instead of having the query inside the loop.  You'll have to test it.  Sometimes it's faster, sometimes it's slower.  The syntax is:

<cfquery>

insert into the table (field1, field2, etc)

select something, something, etc

from SomeSmallTable

where 1 = 2

<cfloop>

union

select #value1#, #value2#, etc

from SomeSmallTable

</cfloop>

</cfquery>

[Dan_Bracuk]

Your specific problem is most likely with this line:

<cfset MyString=#getProducts.matches_with#>

dump that variable and see if it is what you expect.

If the getproducts query and the next query are from the same database, you can avoid the problem altogether by joining tables in the getproducts query.  If you don't know how, I've heard good things about the book, Teach Yourself SQL in 10 Minutes by Ben Forta.

[Dax_Trajero]

To answer you point, I've dumped that variable and it contains what it's supposed to eg. E7171, A1211, F3232

Think of a jewellery site, and you're viewing a page about a single piece of jewellery

On that same page, there's three empty boxes at the base in which each should display details of matching jewellery

So the merchandise table contains two important fields:

product_code (each item of jewellery has a unique product code)

matches_with (each item of jewellery has a field containing a comma separated list of product codes that match this item of jewellery

I'm simply trying to grab that matches_with field, turn it into an array and then run a select clause to go and get details on each product in that array

Am I missing a simpler more elegant solution ?

[ilssac]

Dax Trajero wrote:

Am I missing a simpler more elegant solution ?

WHERE supplier_code IN (<cfqueryparam value="#getProducts.matches_with#" list="yes" cfsqltype="CF_SQL_VARCHAR">)

This does assume that getProducts.matches_with contains the entire list you expect it to.

P.S.  The advice to peruse a resource such as "Teach Yourself SQL in 10 minutes" will help explain why having a field in a database that contains a comma delimited list of foreign key values is a bad database design and how to properly do it with a relational table.

[Dax_Trajero]

You're kidding me ?!?

You know, I really should check out this CFQUERYPARAM I've been hearing about!

Ian, I'll give this a go and report back!

[Dax_Trajero]

Ian, about your point of the csv list in a field - first time I've done it, and I regretted it!

How's about I create a new table called product_matches

field1, product_code

field2, matching_product_code

is that what you mean ?

then I just join that table with the main merchandise table and pull the data out that way?

[ilssac]

That would be the much more normalized way to do it.

Then you would just have to add the valuelist() function to make a list out of all the values of a specificed column in the returned record set from your joined SQL statement.

I.E.

WHERE supplier_code IN (<cfqueryparam value="#valueList(getProduct.prodMatchID)#" list="yes" cfsqltype="CF_SQL_VARCHAR">)