Are you absolutely, positively certain that you have to put a query within a loop, and that it cannot be done with UNION and/or JOIN?
Putting a loop in a query is processor intensive, memory hogging, and could bottleneck your network. The _only_ situation where I would use that kind of process is if I'm pulling data from different schemas and the security is so tight that it prevents cross-schema querying. Where I work, that's common.
If it cannot be done with UNION / JOIN statements, I think using a CURSOR would be preferential to a looped query.
Just my two cents.
Well I don't want to have a query inside a loop, which is why I want to do it with a struct or an array. I just don't know what syntax I need to loop through the results.
On the page that has the struct with a single result, the page rendering time is pretty much instant, since it's pulling the info from the query that's in memory. I just can't figure out how to get more than the one row.
I've had some luck doing this too:
<cfset qgetMaterialsbyID = ArrayNew(2)> <cfloop query="qgetallbarriermat"> <cfset qgetMaterialsbyID[currentrow] = barrierid> <cfset qgetMaterialsbyID[currentrow] = material> </cfloop>
But again, I'm not sure how to get all the rows.
The problem with changing the master query is that I then I have to go to the other 22 pages where it's used and redo those. I mean I'd really rather figure out how to do it this way than redoing the entire app.