Using cfoutput query

Report · Jun 08, 2011

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:

Thanks,

Jim

Report · Jun 08, 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:

    // 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>

Report · Jun 08, 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

Report · Jun 08, 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.

Report · Jun 08, 2011

Owain thanks for the info on using <cfdump var="#qEmployees#" format="text" /> . This output is in a format that I was hoping to see the data.

Jim

Using cfoutput query

1 Correct answer