Skip to main content
R
rjd49
Participant
February 12, 2014
Question

Translation of Variable Output From CFQUERY

  • February 12, 2014
  • 3 replies
  • 927 views

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!

    This topic has been closed for replies.

    3 replies

    R
    rjd49Author
    Participant
    February 13, 2014

    Thank you to all!  It's working!

    R
    rjd49Author
    Participant
    February 15, 2014

    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
    Community Expert
    BKBKCommunity Expert
    Community Expert
    February 16, 2014
    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>

    D
    duncancumming
    Participating Frequently
    February 13, 2014

    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. 

    Fernis
    Fernis
    Inspiring
    February 13, 2014

    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

    Terms & ConditionsAccessibility statement
    Using the community Terms of use Privacy Cookie preferences Do not sell or share my personal information ad choicesAdChoices