Ask Ben: Query Loop Inside CFScript Tags
Posted July 25, 2006 at 8:54 AM by Ben Nadel
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:
- <!--- 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.
- <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.
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.
Thanks for the help!! And long live Jenna!! :D
Thanks. This was a lot of help for a problem I was working on for work. Now, I just have to be sure to remember to remove all the porn references. :-P
@Anna,
Ha ha, no problem - glad it helped.
Thanks! btw, would this also work, or am I WAY off with this?:
- if(qPornStars.recordCount){
- recCount = qPornStars.recordCount;
- counter = 1;
- while(counter lt recCount) {
- WriteOutput(qPornStars[ "name" ][ intRow ] & " is a " & qPornStars[ "sex" ][ intRow ] & " that prefers doing " & qPornStars[ "preferred_category" ][ intRow ] & " movies." & "<br />");
- }
- counter = counter + 1;
- }
That is almost the same, except you are using a while loop instead of a for loop.
http://www.petefreitag.com/cheatsheets/coldfusion/cfscript/
I would reach for a better grasp of the for loop, as it is almost identical to javascript and many other languages.
Also the for loop allows you to define your starting point, condition, and iteration stepper all on the same line, so it is less lines of code.
Thanks for the insight, Steve! I am all about best practices and doing it the best way instead of just whatever works. The reason I initially did it this way is because I am in the process of converting a site from php to ColdFusion :-), and this was the way it was done in php but I am certainly not opposed to changing it.
Another method of looping a query in Coldfusion is to use a while loop with query.next(). Using this method eliminates the need to have an additional counter, and gives you access to the currentRow variable within the query object. While they are functionally the same, this is another option.
while (qPornStars.next()) {
qPornStars.currentRow
}
Thanks, James! :-) So, it would be WriteOutput(qPornStars[ "name" ][ qPornStars.currentRow ]
to access it this way?
Thanks!
Correct :)
Thanks, James! :-)
@Ben, doesn't Evil Ben need to be the one making that comment about the camera??? >:-}
Did Evil Ben hack into your account? >:-} <~~~~my attempt at making an evil smiley :-/
It would be super pimpin' if someone could post code for the equivalent of using the group= attribute of cfouput looping over a query. Probably need a UDF for that?
@Chris,
You need to do the set lastval = col
If lastval neq curVal then do new groupset
I came here because I needed the howto you provide, but before I even read it had to post this comment. Porn stars? WOW!!!! Now THAT's a way to get my full attention! You should be a school teacher!
@Larry,
Hehe and I can totally appreciate your posting name :P Lets see how many people have to look it up!
(My friend was just showing me the open source sierra project a few minutes ago and we were reliving some 1980's glory!)
Looks like:
- while (qry.next()) {
- qry.myFieldName[qry.currentRow]
- }
works as well.
@Phillip,
That rocks - thanks for sharing!
Best. Example Code. EVER.
In cf10, if you call a function that has:
- local.result = {};
- local.result.msg = "";
- local.svc = new query();
- local.svc.setSQL("SELECT * FROM...");
- local.obj = local.svc.execute();
- local.result.qry = local.obj.getResult();
- local.result.Prefix = local.obj.getPrefix();
- return local.result;
Then you can:
- myStars = myComponent.myFunction();
- for (qry in myStars.qry) {
- writeoutput(qry.Name);
- writeoutput(qry.Gender);
- writeoutput(qry.preferred_category);
- }
This has allowed me to have all my functions return the same structure (result) whether it's used for a remote (ajax) call or a regular ColdFusion call.
JavaScript will process RESULT.QRY.DATA.NAME[i] while ColdFusion will process myStars.qry.
Just don't forget:
- param name="url.returnformat" default="json";
- param name="url.queryformat" default="column";
