Problem with looping over a SQL Query in Coldfusion

I am building a ColdFusion-based search results page where the visitor can select a checkbox next to each result for comparing two or more records on the page.

Once they click the submit button I need to be able to show just those records that were selected on a new page so that the visitor can compare the results against each other. The checkbox passes the ID of that record to a SELECT statement to build the query.

The problem I am having is each checkbox on the results page is called "compare" and passes each unique ID in the URL string to the comparison page as the same variable. So, how do I loop through each ID, pass it to my query, and output the result set on the new page?

One thought I had was to store each ID in an array and loop over the SQL query for each record in the array. But then how do I get that into one recordset that I can output to my comparison page?
kenjpeteAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

_agx_Commented:
You shouldn't need to do anything (no loops or arrays).  Just pass that list of ID's into your query via an IN clause. The database will return all records matching those id's in a single query.  Then output the results as usual.

Assuming your db column is type INT, use something like this:

SELECT Columns
FROM   YourTable
WHERE  YourIDColumn IN 
( 
<!--- assumes URL.compare contains a list of numeric id's like "1,12,34,519,16" --->
<cfqueryparam value="#URL.compare#" cfsqltype="cf_sql_integer" list="true"> 
)

Open in new window

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
kenjpeteAuthor Commented:
I changed my query as you recommended but for some reason I am getting the following error:

[ODBC Microsoft Access Driver] Syntax error (comma) in query expression 'GrantID = 468,195,659'.
kenjpeteAuthor Commented:
If I didn't mention it previously, I'm using a MS Access database and my ID field is set to Autonumber.
kenjpeteAuthor Commented:
Ok I found the problem, I had an old query buried at the top of the page....disregard that syntax error problem. It works perfectly!
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
ColdFusion Language

From novice to tech pro — start learning today.