Skip to main content
J
jimfid45
Known Participant
June 8, 2011
Answered

Using cfoutput query

  • June 8, 2011
  • 1 reply
  • 1339 views

Hello,

I came across the code below:

<html> 
<head> 
<title>Employee List</title> 
</head> 
<body> 
<h1>Employee List</h1> 
<cfquery name="EmpList" datasource="cfdocexamples"> 
    SELECT * 
    FROM Employee 
</cfquery> 
</body> 
</html>

So the code selects all of the rows(records) and columns(fields) in table Employee and stores this info in EmpList

The question I have is how do you use the same * notation to ouput all of the columns for each row in the returned query? Meaning something like:

<cfoutput query="EmpList" >
* <BR>
</cfoutput>

Thanks,

Jim

This topic has been closed for replies.
Correct answer Owainnorth

There's no simple way of doing that, as there's little point CF having functions that just output what is essentially formatted data with no control over you format it.

For what purpose are you doing this? Just for debugging, or for an Application? If it's debugging, use CFDUMP.

However there is always a way, and the key is the secret [QueryName].ColumnList, over which you can iterate like any other list.

Fully working example:

<cfscript>

    // knock up a "fake" query for testing
    qEmployees = queryNew('Name, Age, Position', 'VarChar, Integer, Varchar' ) ;
   
    queryAddRow(qEmployees) ;
    querySetCell(qEmployees, 'Name', 'Dave') ;
    querySetCell(qEmployees, 'Age', 21) ;
    querySetCell(qEmployees, 'Position', 'Staff') ;
   
    queryAddRow(qEmployees) ;
    querySetCell(qEmployees, 'Name', 'Phil') ;
    querySetCell(qEmployees, 'Age', 42) ;
    querySetCell(qEmployees, 'Position', 'Manager') ;
   
    queryAddRow(qEmployees) ;
    querySetCell(qEmployees, 'Name', 'Barry') ;
    querySetCell(qEmployees, 'Age', 71) ;
    querySetCell(qEmployees, 'Position', 'Sitting down') ;
</cfscript>

<cfoutput query="qEmployees">
    <cfloop from="1" to="#listLen(qEmployees.ColumnList)#" index="iCol">
        #qEmployees[listGetAt(qEmployees.ColumnList, iCol)][CurrentRow]#<br />
    </cfloop>
</cfoutput>

1 reply

Owainnorth
OwainnorthCorrect answer
Inspiring
June 8, 2011

There's no simple way of doing that, as there's little point CF having functions that just output what is essentially formatted data with no control over you format it.

For what purpose are you doing this? Just for debugging, or for an Application? If it's debugging, use CFDUMP.

However there is always a way, and the key is the secret [QueryName].ColumnList, over which you can iterate like any other list.

Fully working example:

<cfscript>

    // knock up a "fake" query for testing
    qEmployees = queryNew('Name, Age, Position', 'VarChar, Integer, Varchar' ) ;
   
    queryAddRow(qEmployees) ;
    querySetCell(qEmployees, 'Name', 'Dave') ;
    querySetCell(qEmployees, 'Age', 21) ;
    querySetCell(qEmployees, 'Position', 'Staff') ;
   
    queryAddRow(qEmployees) ;
    querySetCell(qEmployees, 'Name', 'Phil') ;
    querySetCell(qEmployees, 'Age', 42) ;
    querySetCell(qEmployees, 'Position', 'Manager') ;
   
    queryAddRow(qEmployees) ;
    querySetCell(qEmployees, 'Name', 'Barry') ;
    querySetCell(qEmployees, 'Age', 71) ;
    querySetCell(qEmployees, 'Position', 'Sitting down') ;
</cfscript>

<cfoutput query="qEmployees">
    <cfloop from="1" to="#listLen(qEmployees.ColumnList)#" index="iCol">
        #qEmployees[listGetAt(qEmployees.ColumnList, iCol)][CurrentRow]#<br />
    </cfloop>
</cfoutput>

J
jimfid45Author
Known Participant
June 8, 2011

Thanks Owain,

For what purpose are you doing this? Just for debugging, or for an Application? If it's debugging, use CFDUMP

Still working on CFC's and ran across a DB based CFC example. Like you said you have no control over the output so

the * would not make much sense. I did use the cfdump, but just wondered if a cfdump was avail via the cfoutput query

Something like:

<cfoutput query="EmpList"  mode="debug"> without having to use cfdump.

Thanks for the answer though,

Jim

Owainnorth
Owainnorth
Inspiring
June 8, 2011
I did use the cfdump, but just wondered if a cfdump was avail via the cfoutput query

No, I don't think the two ever really coincide. There is, however, nothing to stop you writing your own function that dumps out a query in a way you want to see it, even if it is just for your own debugging purposes.

A less heavyweight option is to do <cfdump var="#qEmployees#" format="text" />, which is a lot quicker to run and sometimes preferable.

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