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
Post Comment | Ask Ben | Permalink | Other Searches | Print Page
Newer Post
Calling Array Functions on ColdFusion Query Columns
Older Post
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
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.
What happens if Jenna comes back Girl on Girl and Girl on Guy?
I don't care what happens so long as someone in the room has a video camera :D
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.
@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.
Thanks,
This is a only posting I could find that shows how to get at individual data fields from inside cfscript
Thanks Ben... as usual, your site pulls through.
@Jon,
Awesome my man, glad to help!
Thanks Ben... as usual, your site pulls out. (get it?)
@Luke,
Ha ha ha ha.
You need to backdate our points from kinky posts :P
err, umm, our kinky points from posts.
@Steve,
Yeah, at one point I just had to flip the switch. Unfortunately, I had no good way to retrofit the existing comments.
Your example is illustrative, but using porn to illustrate it is crude. Why not use examples of alcoholics, crackheads, and child molesters to illustrate ColdFusion? Porn is disgusting.
@Adam,
I don't find alcoholics, crackheads, or child molesters even remotely entertaining.
Ben, thanks for the helpful and cool blog. You're my hero.
@Grant,
Ha ha, no problem :) Glad to help.
