Ask Ben: Query Loop Inside CFScript Tags
Posted July 25, 2006 at 8:54 AM
I know I can loop over a query using the loop tag, but is there a way to do a query loop inside of script tags?
Yes. The ColdFusion query object is a specialized record set structure, but really you can think of it as a glorified array of sorts. The trick here lies in the fact that, like an array or structure, you can access values in a ColdFusion query through the use of keys and indexes.
This idea of key/index accessing can be used when both looping over a query and creating one from scratch. To help demonstrate this, let's start by building a small query from scratch:
Launch code in new window » Download code as text file »
- <!--- Create a new query. --->
- <cfset qPornStars = QueryNew( "name, sex, preferred_category" ) />
-
- <!--- Add rows to this query. --->
- <cfset QueryAddRow( qPornStars ) />
- <cfset qPornStars[ "name" ][ 1 ] = "Jenna Jameson" />
- <cfset qPornStars[ "sex" ][ 1 ] = "Female" />
- <cfset qPornStars[ "preferred_category" ][ 1 ] = "Girl on Girl" />
-
- <cfset QueryAddRow( qPornStars ) />
- <cfset qPornStars[ "name" ][ 2 ] = "Peter North" />
- <cfset qPornStars[ "sex" ][ 2 ] = "Male" />
- <cfset qPornStars[ "preferred_category" ][ 2 ] = "Guy on Girl" />
Notice here that as we add rows to the query, we are setting cell values by directly accessing the cell positions. We are treating the query as a Struct of Arrays. Notice that the first structure notation is the name of the column and the second structure notation is the row in the record set. Together we get the exact location in the query that we wish to update.
Using that knowledge, we can easily loop over a query in a CFScript blocking using an index loop.
Launch code in new window » Download code as text file »
- <cfscript>
-
- // Loop over the records in the query.
- for (
- intRow = 1 ;
- intRow LTE qPornStars.RecordCount ;
- intRow = (intRow + 1)
- ){
-
- // Output the name some values. When doing so, access the
- // query as if it were a structure of arrays and we want
- // only get values from this row index.
- WriteOutput(
- qPornStars[ "name" ][ intRow ] & " is a " &
- qPornStars[ "sex" ][ intRow ] & " that prefers doing " &
- qPornStars[ "preferred_category" ][ intRow ] & " movies." &
- "<br />"
- );
- }
-
- </cfscript>
This will produce the following output:
Jenna Jameson is a Female that prefers Girl on Girl movies.
Peter North is a Male that prefers Guy on Girl movies.
As you can see, it takes a little more elbow grease, but looping over a query inside of a CFScript is more than doable. And as we saw above, understanding how to access query cells without CFLoop gives you the ability to build queries as well.
Download Code Snippet ZIP File
Comments (7) | Post Comment | Ask Ben | Permalink | Other Searches | Print Page
Calling Array Functions on ColdFusion Query Columns
If Fireworks Was A Woman, I Would Make Sweet Sweet Passionate Love To It
Reader Comments
Thanks for the post Ben. This helped me loop through an array in cfscript.
Ryan
Posted by Ryan on Oct 15, 2006 at 12:50 PM
Ryan,
Always glad to help. Yeah, when it boils down to it, there are really only two types of loops in CFScript... Index loops as you would do with an array or a query, or Collection loops as you would do with a Struct.
Drop me a line if you need help with anything else.
Posted by Ben Nadel on Oct 15, 2006 at 12:51 PM
What happens if Jenna comes back Girl on Girl and Girl on Guy?
Posted by Brian on Jul 31, 2007 at 7:47 PM
I don't care what happens so long as someone in the room has a video camera :D
Posted by Ben Nadel on Aug 1, 2007 at 7:15 AM
in cf7 at least while testing
for 46028 records,
109-125ms
<cfoutput query="qryTest">
#field1#
</cfoutput>
was faster than
172-219ms
<cfscript>
for(i=1; i lte qryGetAMFiles.recordcount; i=i+1) {
writeOutput(qryTest.field1[i] & " ");
}
</cfscript>
in 5 tests the mins/max were shown.
Posted by Steve on Jan 28, 2008 at 1:27 PM
@Steve,
I think that just goes to show you have to go with whatever you find easier / most readable. Thanks for dropping in the testing there.
Posted by Ben Nadel on Feb 4, 2008 at 9:17 PM
Thanks,
This is a only posting I could find that shows how to get at individual data fields from inside cfscript
Posted by pat capozzi on Jul 6, 2008 at 11:57 AM
