Referencing ColdFusion Query Columns In A Loop Using Both Array And Dot Notation
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 Name1 : Sara
2 : Nicole
3 : Kim
4 : Joanna
5 : Tricia
Using Dot-Notation For Column Name In Tags1 : 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.
Want to use code from this post? Check out the license.
Reader Comments
Hey Ben,
In CF10 you can also do this now.
@Giancarlo,
Word up! One of the best features in CF10!
And, the CF 10+ way too
That's what I get for not reloading the page first;) (had this up in a tab for...a while)
@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.
@Giancarlo,@Ben :: It's nice to see CF improving the rate of implementing jdk features more quickly than previously ...
@Giancarlo, @Cutter,
It's all good - the more people share the good stuff about CF10, the better!
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).
@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.
Hmmm. I've never had to use serializeQueryByColumns before - that's new to me.
I put
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
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
statement that affected the whole program. I wonder if such a thing could be put into Application.cfc.
That would freak people out.
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.
I think the for-in syntax in CF 10 is much more elegant, but this one's not too shabby either.
@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!
@Jose,
Very cool!