sql - Coldfusion - variable field name when looping through database query results

Question

I have a set of column names in a table - e.g. foo1, foo2, foo3, foo4. I want to refer to these column names dynamically through a loop:

<cfloop index="i" from="1" to="4">
  <cfset foo = Evaluate("query.foo" & i)>
</cfloop>

The above doesn't work - ColdFusion throws a "variable not defined" error, even though query.foo1 is a valid reference to the query results. How else can I do this?

Answer 1

Don't use Evaluate() for things like that! It's slow and should be avoided.

<cfloop index="i" from="1" to="4">
  <cfset foo = query["foo" & i][query.CurrentRow]>
</cfloop>

Or, if you like:

<cfloop index="i" from="1" to="4">
  <cfset foo = query["foo#i#"][query.CurrentRow]>
</cfloop>

Evaluate() is for evaluating bits of code. Don't use it for things that can be solved more elegantly in language-integrated, more appropriate ways.

EDIT:

When accessing Query objects with the "angle bracket"-syntax, you must append the (1-based) row number index (query["foo#i#"][RowNum]). When using the traditional "dot"-syntax (query.foo1), the current row is implicit.

To access the current row explicitly, use the QueryObject.CurrentRow property. But it could be any positive integer up to QueryObject.RecordCount. A range check is advised for anything other than CurrentRow.

This opens an interesting field: You can start to use query objects with "random access". Previously (before CFMX) all you could do was iterate them from start to end, pulling out the things that you look for. Now it's like a nested struct/array data structure that you can use in different ways.