Ben Nadel
On User Experience (UX) Design, JavaScript, ColdFusion, Node.js, Life, and Love.
I am the chief technical officer at InVision App, Inc - a prototyping and collaboration platform for designers, built by designers. I also rock out in JavaScript and ColdFusion 24x7.
Meanwhile on Twitter
Loading latest tweet...
Ben Nadel at cf.Objective() 2010 (Minneapolis, MN) with:

Ask Ben: Query Loop Inside CFScript Tags

Posted 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.

Tweet This Interesting post by @BenNadel - Ask Ben: Query Loop Inside CFScript Tags Thanks my man — you rock the party that rocks the body!



Reader Comments

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.

Reply to this Comment

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.

Reply to this Comment

@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.

Reply to this Comment

Thanks,

This is a only posting I could find that shows how to get at individual data fields from inside cfscript

Reply to this Comment

You need to backdate our points from kinky posts :P
err, umm, our kinky points from posts.

Reply to this Comment

@Steve,

Yeah, at one point I just had to flip the switch. Unfortunately, I had no good way to retrofit the existing comments.

Reply to this Comment

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.

Reply to this Comment

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

Reply to this Comment

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;
  • }

Reply to this Comment

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.

Reply to this Comment

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.

Reply to this Comment

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
}

Reply to this Comment

Thanks, James! :-) So, it would be WriteOutput(qPornStars[ "name" ][ qPornStars.currentRow ]
to access it this way?

Thanks!

Reply to this Comment

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 :-/

Reply to this Comment

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?

Reply to this Comment

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!

Reply to this Comment

@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!)

Reply to this Comment

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";

Reply to this Comment

Post A Comment

?
You — Get Out Of My Dreams, Get Into My Comments
Live in the Now
Oops!
Comment Etiquette: Please do not post spam. Please keep the comments on-topic. Please do not post unrelated questions or large chunks of code. And, above all, please be nice to each other - we're trying to have a good conversation here.