Double evaluation - How do I access my query column based on a variable that holds the column name?

Question

I have a query and a list of field/column names. I want to do a sort of double loop - loop through each record in the query, and then loop through the list of field/column names and output each corresponding field. The loops should be something like this:

<table>
    <cfoutput query="myQuery">
        <tr>
            <cfloop list="#cols#" index="col">
                <td>?</td>
            </cfloop>
        </tr>
    </cfoutput>
</table>

The problem is what to put where the question mark is... I've tried #myquery[col]#, but this didn't work. I need to get the variable indicated by the string name in the variable col... And obviously, #col# will just return the column name. I need to figure out some way to double-evaluate the string... something like ##col##, which of course won't work either. How can I accomplish this?

score 7 · Accepted Answer · answered Jan 26 '11 at 12:13

7

When referencing column names as a structure, you need to also tell the query which row you want to get. You should also make sure that you check that the column name exists if you didn't get the cols variable via myQuery.ColumnList.

Use the following code to dynamically reference each column in your loop:

<table>
    <cfoutput query="myQuery">
        <tr>
            <cfloop list="#cols#" index="col">
                <td>#myQuery[col][CurrentRow]#</td>
            </cfloop>
        </tr>
    </cfoutput>
</table>

answered Jan 26 '11 at 12:13

Dan Short

9,598
2
28
53

hmmm. for some reason this is causing errors that the evaluate method (see my answer below) is not. I get an error that the table is not indexable by a certain column, even though that column is listed as part of the table in the error message, and I know it's there. When I use the evaluate method it finds the data. Any clue what might be causing this? – froadie Jan 26 '11 at 15:53
I've noticed that the column it's failing on is blank for the first record. Could this be why it's failing? Perhaps the evaluate automatically converts it to an empty string? – froadie Jan 26 '11 at 16:03
@Al Everett - I'm not sure as the query is being done in a separate function, but I can check. Would this affect anything? – froadie Jan 26 '11 at 17:01
Query caching sometimes gets funky if you add/remove columns from a table that you're using select * on. How are you generating your "cols" value? Are you using `queryname.columnList`? – ale Jan 26 '11 at 17:04
@Al Everett - nope, it seems to be a hardcoded subset of columns – froadie Jan 26 '11 at 17:08
The "..not indexable by column.." error usually means you are using a column name that does not exist in the query ie #myQuery["ThisDoesNotExist"][someRow]# Try using #myQuery.columnList# instead of whatever you are using for #cols# – Leigh Jan 26 '11 at 17:46
You need to verify that your list is good against the columns that are actually part of your recordset. I would loop through your #cols# variable and ensure that all values in that list are present in myQuery.ColumnList to avoid trying to ask for a column that doesn't exist. – Dan Short Jan 26 '11 at 19:06
Yes, that is what I was suggesting. It is very likely one of the column names is spelled incorrectly.. – Leigh Jan 26 '11 at 19:38

duncan · Answer 2 · 2011-02-01T17:22:30.790

-1

You can still use Sergii's approach with your own columnlist:

<cfloop list="#cols#" index="col">
   <cfif StructKeyExists(myQuery, col)>
      <td>#col# = #myQuery[col][myQuery.CurrentRow]#</td>
   </cfif>
</cfloop>

edited Feb 01 '11 at 17:22

answered Jan 26 '11 at 11:09

duncan

31,401
13
78
99

Same as Sergii's solution, this will error with "Complex object types cannot be converted to simple values." – Dan Short Jan 26 '11 at 13:58
In other words, you are missing the row number. – Leigh Jan 26 '11 at 19:37

score -2 · Answer 3 · answered Jan 26 '11 at 10:52

-2

Got it!! :)

#evaluate(evaluate("col"))#

answered Jan 26 '11 at 10:52

froadie

79,995
75
166
235

I'd think `evaluate(DE("col"))` would be better, but one should still avoid evaluate() except as a last resort. – ale Jan 26 '11 at 14:43
Wow, seems like this meets with a lot of disapproval. I recall hearing that evaluate is not the best method... Anyone care to explain? – froadie Jan 26 '11 at 14:50
The evaluate function forces ColdFusion to compile the expression being evaluated every time you call it. Not good for performance (compilation is largely single-threaded) and you can almost always avoid it. – ale Jan 27 '11 at 17:37

Double evaluation - How do I access my query column based on a variable that holds the column name?

3 Answers3