Query variables from a list ??? Please help !!! I beg. 1

[rlatham]

HI, I would greatly appreciate any help on this issue.
If I have this code:

<cfquery name=&quot;names&quot; datasource=&quot;workshop&quot; dbtype=&quot;ODBC&quot;>
Select name, email, #listClassCodes#
From InsAsst
Order by name
</cfquery>

How can I reference the names.fieldname and obtain their values?

Thanks

 

[pcorreia]

One thing to be careful of -- in the example you're giving, #listClassCodes# is a variable that should hold the name of a column you want to query. If listClassCodes is the name of a column in your DB, omit the pound signs.

Other than that, the syntax for accessing these variables is easy:

<table border=&quot;1&quot;>
  <tr>
     <td>name</td>
     <td>email</td>
     <td>#listClassCodes#</td>
  </tr>

  <cfoutput query=&quot;names&quot;>
  <tr>
     <td>#name#</td>
     <td>#email#</td>
     <td>#Evaluate(listClassCodes)#</td>
  </tr>
  </cfoutput>

</table>

Everything inside the <cfoutput query=&quot;names&quot;> tag will repeat once for each record in the query result set. Variables inside a <cfoutput> tag that has the query=&quot;&quot; attribute are automatically scoped to that query.

 

[rlatham]

Hi,
In my example, listClassCodes variable is a list containing the names of the columns I want to query.
So, I looped over it to obtain one column name at a time and its value.

I do not understand the Evaluate function, that is why I could not figure out how to obtain the column names and their values from the list in the query. I had no idea I could use it for my problem.

Thank you very much, it works great !

 

[pcorreia]

If listClassCodes is a list of column names (stupid me for not realizing it, since it's named listClassCodes), my example won't work exactly. You'll have to loop over the list within each record and do an Evaluate() on the list item.

A little explanation on Evaluate() is obviously in order. Consider this example:

   <cfset myVar = &quot;foo&quot;>
   <cfset myVarName = &quot;myVar&quot;>

Obviously, one way to get the value of myVar is #myVar#. But if I don't know what the name of the variable is going to be (which is the case here), I can't do that. I know that the name of the variable is stored in the variable #myVarName#, so I need a way to say to the ColdFusion engine, &quot;Take the value of the variable 'myVarName', then evaluate that as the name of another variable&quot;. Since ColdFusion doesn't speak english, exactly, we have the Evaluate() function to tell it to do that. One way to accomplish the above example would be:

   #Evaluate("#myVarName#")#

Which says (from inside out):
[ol][li]Extract the value of the variable myVarName (which is a string).[/li]
[li]Treat that string like it was the name of a variable, and get the value of that variable.[/li][/ol]
In ColdFusion documentation, this is known as Dynamic Evaluation. There's a simpler way to write the Evaluate() function, which takes advantage of the fact that anywhere a function or expression expects a string in quotes, it will take an unquoted variable name instead:

   #Evaluate(myVarName)#

So this evaluates not the string &quot;myVarName&quot;, but the value of the variable &quot;myVarName&quot;.

Hope this helps out.