I am working on a project for a client that is running ColdFusion MX7 on their servers and ran into a situation today where a search feature that worked perfectly in my local development environment (which, admittedly is running CF 8) produced some very inconsistent results when run on the testing server.
This particular search has to combine results from not only two different databases, but two different database systems (MySQL and Oracle). In making this work, I had written queries to extract the required information from each database separately, then created a query-driven loop based on one of the queries that was nested inside a second query-driven loop based on the other.
The code I originally wrote was similar to what is below (the client-spefic parts of the original code have been removed).
<cfloop query="queryA"> <cfloop query="queryB"> <cfif queryA.someField EQ queryB.someField> <cfset foo = queryA.someOtherField> <cfset bar = queryB.someOtherField2> ...do some more stuff... <cfbreak> </cfif> </cfloop></cfloop>This led to getting the correct number of rows generated on the “view” page, however all the rows displayed the same information (as if I had 175 rows of the exact same data) which was obviously wrong. After reading the last comment on this page on Adobe Livedocs, I updated the code to what is below and resolved the problem.
<cfloop query="queryA"> <cfloop query="queryB"> <cfif queryA.someField[queryA.currentRow] EQ queryB.someField[queryB.currentRow]> <cfset foo = queryA.someOtherField[queryA.currentRow]> <cfset bar = queryB.someOtherField2[queryB.currentRow]> ...do some more stuff... <cfbreak> </cfif> </cfloop></cfloop>It seems that in a nested loop configuration such as that, ColdFusion MX7 sometimes “forgets” where it is in each respective loop and that implicitly specifying the currentRow attribute of the query forces it back to its correct spot.
It took a while to nail down just what was causing the problem, but thankfully it was a pretty easy fix.