Coldfusion - variable field name when looping through database query results

sql variables coldfusion

11,030

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

Solution 2

You are very close. Try this:

<cfset query.foo1 = "foo val 1">
<cfset query.foo2 = "foo val 2">
<cfset query.foo3 = "foo val 3">
<cfset query.foo4 = "foo val 4">

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

11,030

Author by

ClairelyClaire

I like to write code for funsies. I'm (reasonably) proficient in C# and PowerShell, know enough about PHP to get my hands dirty, and work with other languages as necessary, like Pascal and Lua. I also do graphic design and layout work and use both Adobe and Affinity products for those projects.

Updated on June 12, 2022

Comments

ClairelyClaire almost 2 years
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?
Tomalak almost 15 years

I know Evaluate() has been used traditionally (i.e. up to CF5) for this kind of stuff, but since CF6 it is unnecessary and generally not recommendable. Even Macromedia/Adobe discourage its use: livedocs.adobe.com/coldfusion/7/htmldocs/00000946.htm
ClairelyClaire almost 15 years

Unfortunately, neither of those work - ColdFusion tells me "the value foo1 cannot be converted to a number."
Tomalak almost 15 years

Oh, I see. Wait, there's a little catch about how to address query rows with the angle bracket syntax.
ClairelyClaire almost 15 years

I'm not seeing that in the documentation you linked to - any suggestions?
Mike Jr almost 15 years

Awesome, I didn't realize this. Thanks.
Tomalak almost 15 years

The link works for me. The CF documentation traditonally loads a bit slow, and I think you need JavaScript on.
ClairelyClaire almost 15 years

Oddly, I'm still getting the "cannot be converted to a number error", even after adding the [query.currentrow] syntax.
Tomalak almost 15 years

I've tested the above and it works as advertised. The error you get must be somewhere else.
ClairelyClaire almost 15 years

It's definitely happening with that line of code. The field in question is a number, the data type is set to integer. If I take out that line of code, the page renders perfectly fine.
ClairelyClaire almost 15 years

Anything I use with the queryname[fieldname][rownumber] syntax is trying to convert it to a number...
Tomalak almost 15 years

Then please post the portion that does not work for you, and a little context. I have no idea what's wrong, but the above code definitely works for CFMX 6.0 and higher.
ClairelyClaire almost 15 years

<cfloop index="i" from="1" to="4"> <th> <cfset foo = task["max" & i][task.CurrentRow]> </th> </cfloop> That's exactly what I have. ("task" is the name of my query results and "max" is the prefix of the column names in question)
ClairelyClaire almost 15 years

I'm on CF8 and MSSQL 2k5. The result set is coming from a pretty generic SQL query called to in a CFC - e.g. "SELECT TaskID, Task, Max1, Max2, Max3, Max4 FROM Tasks ORDER BY Task"
Tomalak almost 15 years

Okay, I tested mine on CF7.1, which should produce equal results for this. Does a static <cfset foo = task["max1"][task.CurrentRow]> work for you?
ClairelyClaire almost 15 years

For some bizarro reason, changing the name of the query results from "Task" to anything else (e.g. "Tasks" or "Tsk" makes it work - I didn't think "Task" was a reserved word in CF...)
Tomalak almost 15 years

I don't, either. But I assume you are seeing an odd scoping issue. I think the implicitly-scoped variable name "task" is matched against a higher-ordered scope, found there, and used without referring to your query result anymore.
ClairelyClaire almost 15 years

It does seem as though everything is working properly now - thanks so much for your help!!