Looping over a query without knowing the headers 1

[ksoong]

I'm trying to create a reporting tool for a system that I'm working on that is supposed to take a Custom Database Table that a user plugs into the system and spit out all the data from that database Table in an HTML table format.

However, the structure of the custom database table is not known. It could be anything. Is there a way to do this?

I've looked over CFTABLE tags but you need to know the structure for that too. And there is also the possibility of using a key, value array loop. But I'm stuck beyond that.

 

[ecobb]

I've done this before, but it's been a while. I remember that you need to use the variable "query_name.columnList". This will return a comma-delimited list of the query columns.

I'll see if I can find an example of how I used it.



Hope This Helps!

Ecobb

"My work is a game, a very serious game." - M.C. Escher

 

[ecobb]

Ok, here we go, I found it.

<CFQUERY NAME="get_all_records" DATASOURCE="#dsn#">
SELECT *
FROM   TableName
</CFQUERY>

<table border="1">
<!--- This first row is going to output the actual Name of each table column --->
<tr>
    <CFOUTPUT>
    <CFLOOP list="#get_all_records.ColumnList#" index="sColumnName">
        <td>#sColumnName#</td>
    </CFLOOP>
    </CFOUTPUT>
</tr>
<!--- This second row is going to loop through and output each of the records returned from the query. --->
<CFOUTPUT query="get_all_records">
    <tr>
        <CFLOOP list="#get_all_records.ColumnList#" index="sColumnName">
            <CFSET sColumnValue = get_all_records[sColumnName]>
            <td>#sColumnValue#</td>
        </CFLOOP>
    </tr>
</CFOUTPUT>
</table>

Hope This Helps!

Ecobb

"My work is a game, a very serious game." - M.C. Escher

 

[TruthInSatire]

I've used that little trick too. it's very handy. I created a "build your own query" page on one of our apps at work that uses the same logic when outputting the requested fields when you ONLY want to show the requested fields.

Human beings, who are almost unique in having the ability to learn from the experience of others, are also remarkable for their apparent disinclination to do so.
-Douglas Adams (1952-2001)