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 TechCrunch Disrupt (New York, NY) with:

Referencing ColdFusion Query Columns In A Loop Using Both Array And Dot Notation

By Ben Nadel on
Tags: ColdFusion

This is a super short blog post, and might be completely obvious to most people; but, I know that my understanding of ColdFusion query column references has evolved over time, so I thought there might be some people who don't know this yet. When it comes to ColdFusion queries, column names can be referenced using both array-notation and dot-notation. In certain cases, such as in CFScript, I find that the dot-notation makes my code more readable by cutting down on "syntactic noise."


 
 
 

 
  
 
 
 

When you first get into ColdFusion, one of the earliest and coolest things that you'll learn is how to pull data out of a database and display it on the page. It makes you feel awesome! Now, after you have your query object, you can simply use a CFLoop[query] and output each value using simple dot-notation:

myQuery.someColumnName

Here, the CFLoop context already increments the row index for each loop iteration and the appropriate record value is made available implicitly. But sometimes, you have to, or want to, reference a particular row of a given query. Such an example would be performing a query loop inside of CFScript before the for-in loop was introduced in ColdFusion 10.

In such a situation, you have to explicitly supply the record index using array-notation. But, when it comes to the column name reference, you can use either array-notation or dot-notation. To see this in action, take a look at the following code:

  • <!--- Gather a query for testing. --->
  • <cfquery name="friends" datasource="testing">
  • SELECT
  • f.id,
  • f.name
  • FROM
  • friend f
  • ORDER BY
  • f.id ASC
  • </cfquery>
  •  
  • <cfscript>
  •  
  •  
  • // First, let's loop over the collection and output each record
  • // using our standard array-notation for both the column name
  • // and record index.
  • writeOutput( "Using Array-Notation For Column Name" );
  • writeOutput( "<br />" );
  •  
  • for ( i = 1 ; i <= friends.recordCount ; i++ ) {
  •  
  • writeOutput( friends[ "id" ][ i ] );
  • writeOutput( " : " );
  • writeOutput( friends[ "name" ][ i ] );
  • writeOutput( "<br />" );
  •  
  • }
  •  
  •  
  • // Now, let's loop over the collection and output each record
  • // using dot-notation to reference the column name and array-
  • // notation to reference the record index.
  • writeOutput( "<br />" );
  • writeOutput( "Using Dot-Notation For Column Name" );
  • writeOutput( "<br />" );
  •  
  • for ( i = 1 ; i <= friends.recordCount ; i++ ) {
  •  
  • writeOutput( friends.id[ i ] );
  • writeOutput( " : " );
  • writeOutput( friends.name[ i ] );
  • writeOutput( "<br />" );
  •  
  • }
  •  
  •  
  • </cfscript>
  •  
  • <cfoutput>
  •  
  •  
  • <!---
  • And, let's just demonstrate that this dot-notation / array-
  • notation mix works in tags as well.
  • --->
  • <br />
  • Using Dot-Notation For Column Name In Tags
  • <br />
  •  
  • <cfloop query="friends">
  •  
  • #friends.id[ friends.currentRow ]# :
  • #friends.name[ friends.currentRow ]#
  •  
  • <!---
  • And, of course, inside a CFLoop/query we can actually
  • just reference the column itself as the internal iterator
  • points to the appropriate row automatically.
  • --->
  • ( #friends.name# )
  • <br />
  •  
  • </cfloop>
  •  
  •  
  • </cfoutput>

As you can see, we are looping over a ColdFusion query several times, using explicit row indicies. For each loop, I am demonstrating a different form of query-column reference: array-notation and dot-notation. And, when we run the above code, we get the following page output:

Using Array-Notation For Column Name
1 : Sara
2 : Nicole
3 : Kim
4 : Joanna
5 : Tricia
Using Dot-Notation For Column Name

1 : Sara
2 : Nicole
3 : Kim
4 : Joanna
5 : Tricia
Using Dot-Notation For Column Name In Tags

1 : Sara ( Sara )
2 : Nicole ( Nicole )
3 : Kim ( Kim )
4 : Joanna ( Joanna )
5 : Tricia ( Tricia )

As you can see, the various approaches to query column references all result in the same output. That's why I've started to gravitate toward the dot-notation (when I'm in CFSCript) - I find it easier to read.

NOTE: If you are trying to use a query column outside of a query context, the column reference becomes much less flexible.




Reader Comments

And, the CF 10+ way too

  • for (var rec in LOCAL.myQuery) {
  • WriteOutput("this column " & LOCAL.rec.thisColumn & " at " & LOCAL.myQuery.currentRow);
  • }

Reply to this Comment

That's what I get for not reloading the page first;) (had this up in a tab for...a while)

Reply to this Comment

@Cutter the var setting only works if you do that inside a function. Just wanted to point that out because if you try that in a regular output it will fail.

Reply to this Comment

@Giancarlo, @Cutter,

It's all good - the more people share the good stuff about CF10, the better!

Reply to this Comment

I think this is a great blog post. I've been programming in ColdFusion for years and have not used the "for ( i = 1" method at all I don't think.

That's the preferred method for looping in JavaScript because "for each" is frowned upon.

Now, recently, I started a project from scratch and allowed AJAX to set the ground rules for how to return query results. In AJAX, you return an object that has both the query and anything else (for instance, an error message if the query didn't execute correctly). This object might be called "result" in JavaScript. So I let that be my driving force in determining how to return all queries: They are returned in an object that contains: result.qry, result.msg, and result.prefix (from the .getPrefix method of the execute command).

Now, I used to name my query variables qryFriend, but now Friend is an object that contains .qry, .msg and .prefix.

So now instead of looping over qryFriend, I loop over Friend.qry. I used to reference qryFriend.Recordcount, but not I reference Friend.Prefix.Recordcount. And now I have Friend.msg, whereas before, the return value was either a query or an error message, which I never liked anyway because that made qryFriend be something different if not isQuery(qryFriend).

Reply to this Comment

@Phillip,

The one thing that you gotta be mindful of, when serializing queries in ColdFusion, is that serializeJson() actually allows for two completely different formats of query output, depending on the optional second argument - "serializeQueryByColumns". That thing always trips me up.

Reply to this Comment

Hmmm. I've never had to use serializeQueryByColumns before - that's new to me.
I put

  • param name="url.returnformat", default="json";
  • param name="url.queryformat", default="column";

at the top of my function and it converts it to JSON for me. Maybe that's doing the same thing.

Hey, not to obsess on this blog post, but JavaScript loops would go

  • for ( i = 0 ; i < friends.recordCount
  • instead of
  • for ( i = 1 ; i <= friends.recordCount

I only mention it because I perceive that Adobe is trying to make cfscript an easy on ramp for people who know JavaScript already.
There's no solution for it though - ColdFusion is 1 based and JavaScript is 0 based.

I remember from my BASIC days we had an

  • OPTION BASE

statement that affected the whole program. I wonder if such a thing could be put into Application.cfc.
That would freak people out.

Reply to this Comment

You do a really good job of pointing out the various approaches to looping over queries. I think I first learned about array notation when I was looking at a few examples on CFLib.org. This was back on CF 5!

I'm currently working on an app that's on CF 9 with no plans of upgrading to CF 10. I've been able to use the following for CFScript query loops.

  • while (qry.next())
  • {
  • writeOutput(qry.col[qry.currentRow] & "<br>");
  • }

I think the for-in syntax in CF 10 is much more elegant, but this one's not too shabby either.

Reply to this Comment

@Phillip,

The base-difference in JavaScript and ColdFusion had definitely messed me up once or twice. I'm usually good about starting at either 1 or 0; it's the ending with "<" vs. "<=" that will get me! And, suddenly, my loop never hits the last item!

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.