[thelist] CFQuery sub search...

[Susan Wallace]

Me again,

First of all, thank you VERY much to rudy, .jeff and Joshua for the helpful 
replies!

Rudy gave me the answer to what I was trying to figure out, but as you all 
pointed out, there is a "catch". I found the limitation with the IN clause, 
but because of this data, I haven't been able to narrow it to less than 255 
yet (to get that "Magic Number")  to see how to break up the list into 
chunks... I'm using MSSQL Server 7.

>parsing the list of pks into 255 unit chunks on the fly.  besides, it'd have
>to be a pretty hefty database of material if you're going to encounter a
>chunk of records over 255 that meet the initial search criteria.

This is a reasonable assumption, but because of the data that I am working 
with here, the initial search almost always returns a list well over 255 - 
sometimes in the thousands. (I can describe the app if you think it would 
help, I don't know of a better way to do this given the data I am working 
with...)

So, if I use the tip below, (chunkArray), where is the "catch"...

If I follow you, it will take what I currently have:

<input type="hidden" name="thepks" 
value="<cfoutput>#ValueList(Instrument.Title_ID)#"></cfoutput>

and instead of a hidden form field, make a variable and then make chunks, 
using this syntax:

WHERE id IN (#chunkList#)

put it back into my search.

How does that "string" all of them back together so that you are actually 
searching *all* of the results?

I need to mention that my Array skills are limited at best, so if I am 
asking you to restate the obvious, I apologize. :)




><cfscript>
>   chunkSize = 255;
>   chunkDelimiter = ')' & Chr(10)& '  AND id IN (';
>
>   for(i = chunkSize; i LT ArrayLen(chunkArray); i = i + chunkSize)
>     chunkArray[i] = chunkArray[i] & '^';
>   chunkList = ArrayToList(chunkArray);
>   chunkList = Replace(chunkList, '^,', chunkDelimiter, 'ALL');
></cfscript>
>
>then, use the chunked up list in your cfml:
>
>WHERE id IN (#chunkList#)

Thank you Again!
Susan