Skip to main content
Ben Nadel at CFCamp 2023 (Freising, Germany) with: Grant Shepert
Ben Nadel at CFCamp 2023 (Freising, Germany) with: Grant Shepert

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

By
Published in Comments (13)

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.

Want to use code from this post? Check out the license.

Reader Comments

21 Comments

And, the CF 10+ way too

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

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

62 Comments

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

15,811 Comments

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

62 Comments

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.

50 Comments

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.

15,811 Comments

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

I believe in love. I believe in compassion. I believe in human rights. I believe that we can afford to give more of these gifts to the world around us because it costs us nothing to be decent and kind and understanding. And, I want you to know that when you land on this site, you are accepted for who you are, no matter how you identify, what truths you live, or whatever kind of goofy shit makes you feel alive! Rock on with your bad self!
Ben Nadel