Translation of Variable Output From CFQUERY

[rjd49]

I'm not highly technical, and this is probably something easy, but here is my

dilemma.

First, define some variables:

<cfset datasource = '#MyDatabase#'>
<cfset query_tbl = 'MyDatabaseTable'>
<cfset field1 = 'actual_fieldname'><!--- a field in MyDatabaseTable --->

Then, run a query using those variables:

<cfquery name="cfqGetItems" datasource="#datasource#">
  SELECT *
  FROM #query_tbl#
  ORDER by #field1# ASC
</cfquery>

Then, attempt to display the query output:

<cfoutput>
  <p>#cfqGetItems.field1#
</cfoutput>

Insead of the values for "actual_fieldname", I get an error message: "field1 is not defined in query cfqGetItems".

I realize that defining the output as "#cfqGetItems.field1#" is incorrect, but how can a "translation" be done?  What is the correct way to get the output to generate the values for "actual_fieldname" instead of thinking it is still dealing with the variable "field1"?

Thank you very much for any help!

[Fernis]

Ok, so here's what you would do usually, as you know:

<cfoutput>#cfqGetItems.actual_fieldname#</cfoutput>

Logically this is wrong, but ColdFusion behaviour won't give you an error if your query returns several rows - instead you just see the result of the first returned row. This is how CF operates when using the basic syntax.

If you want to evaluate the field name, say goodbye to that shortcut, and address the query columsn as arrays (of row values).

<cfoutput>#cfgGetItems[field][1]#</cfoutput> Would give you similarly the result of the first row,

When looping over a query, (which is actually what you want to do, I'm sure) you have a couple of options, which both are shown below:

<cfoutput query="cfqGetItems">

#evaluate(field1)# = #cfgGetItems[field1][currentrow]#<br>

</cfoutput>

HTH, -Fernis

[duncancumming]

Of course if you're looping over your query using <cfoutput query="foo">  then you don't need to specify the [currentrow] attribute too (although there's no harm in doing so, it's just overkill).

Also please don't use evaluate(), it's not generally considered good practice these days.

You can simply do:

<cfoutput query="cfqGetItems">

   #cfgGetItems[field1]#<br>

</cfoutput>

Using the square brackets notation also lets you deal with things like where your query returns column names with spaces. 

[rjd49]

Thank you to all!  It's working!

[rjd49]

I spoke too soon.  This worked in one case, but not in the following case:

I'm not highly technical, and this is probably something simple, but here is my

dilemma, in a slightly different setting where I am attempting to retrieve a single record.

First, I define some variables:

<cfset datasource = 'MyDatabase'>
<cfset query_tbl = 'MyDatabaseTable'>
<cfset field1 = 'actual_fieldname1'><!--- FIRST field in MyDatabaseTable --->
<cfset field2 = 'actual_fieldname2'><!--- SECOND field in MyDatabaseTable --->
<cfset ID = #Form.ID#<!--- ONE particular record I want to retrieve, passed from a form --->

I do a test display to ensure all variables are set, and everything checks out fine.

Then, to retrieve this single record, I run a query using those variables:

<cfquery name="cfqGetItem" datasource="#datasource#">
  SELECT *
  FROM #query_tbl#
  WHERE ID = #ID#
</cfquery>

Then, I attempt to display the query output:

EITHER AS

<cfoutput>
  <p>#cfqGetItem.field1#
</cfoutput>

OR, AS

<cfoutput>
  <p>#cfqGetItem[column1_field][currentRow]#
</cfoutput>

In each case, I get a similar CF error message: "Element field1 is not defined in query cfqGetItem", or "Variable currentrow is not defined".

How do I get the query output to generate the actual values for the record?

How can a "translation" be done?  What would be the correct way?

Thank you very much for any help!

[BKBK]

rjd49 wrote:

Then, I attempt to display the query output:

EITHER AS

<cfoutput>
  <p>#cfqGetItem.field1#
</cfoutput>

OR, AS

<cfoutput>
  <p>#cfqGetItem[column1_field][currentRow]#
</cfoutput>

Should be

<cfoutput query="cfqGetItem">
  <p>#cfqGetItem[field1][currentRow]#
</cfoutput>

or

<cfoutput>
  <p>#cfqGetItem[field1][1]#
</cfoutput>