Get Fields In Table Dynamically

[bweno]

I am trying to create a dynamic query that will allow me to query a table, pull all of the fields and dump all of the data into a text file: I have been able to make the dynamic query, but I am unable to display all of the data on the page when I loop through my fields... Does anyone know how to do this... Here is my query to get the fields dynamically... How would I go about actually querying the datasource and grabbing all of the data from the fields?

<cfquery name="getfields" datasource="#db#">
select *
from billing
</cfquery>
<cfset thecolumnlist = getfields.columnlist>
<cfloop list="#thecolumnlist#" index="i">
<cfoutput>
#i#
</cfoutput>
</cfloop>

Thanks to anyone who can help me!!!

[cf_dev2]

Do you want to output the query values or just the column names?

[bweno]

The query values... When I run the script as I have it, it will already output the column names... Now I need to make it actually look back at the query and dump all of the data in the table... When my tables change, I do not want to have to go back and revise my script, I just want it to update and dump the data when ever I change the mysql db... As it is right now it will show all the fields... now I want it to dump all the data from the table without me having to actually having to write the #fieldname# for each field I want to output... I would think that a loop script would loop through the list of field names and then generate an output script that could write all the data to a txt file... Thanks for you help!!!

[cf_dev2]

There are pros/cons to using SELECT *. Its easier typing, but more db work. Its also easier
to accidentally include columns like "Password" or "Salary".

You can output the query values using array syntax: #QueryName[ColumnName][rowNumber]# .

<cfoutput query="getFields">
<cfloop list="#getfields.ColumnList#" index="columnName">
#getFields[columnName][currentRow]#
</cfloop>
<br/>
</cfoutput>

There are a few functions at cflib.org for converting a query to a CSV file
http://www.cflib.org/udf.cfm?id=1197.
http://www.cflib.org/udf.cfm?ID=404

Or you could do it yourself. Loop through the query and append the values to a string variable, adding a newline after each record. Use cffile if you want to save the output to disk. Use cfcontent/cfheader if you want the output to be downloaded. This is a popular question so there are many examples. Google for ColdFusion CSV or ColdFusion CSV download

[bweno]

I looked at this udf at cflib.. Can you please help me make heads and tail with it concerning my issue...

Here is their script:

<cfscript>
mytestquery = QueryNew('FirstName,LastName,Title');
QueryAddRow(mytestquery, 3);
QuerySetCell(mytestquery, 'FirstName', 'Steve', 1);
QuerySetCell(mytestquery, 'LastName', 'Drucker', 1);
QuerySetCell(mytestquery, 'Title', 'CEO', 1);
QuerySetCell(mytestquery, 'FirstName', 'Dave', 2);
QuerySetCell(mytestquery, 'LastName', 'Watts', 2);
QuerySetCell(mytestquery, 'Title', 'CTO', 2);
QuerySetCell(mytestquery, 'FirstName', 'Dave', 3);
QuerySetCell(mytestquery, 'LastName', 'Gallerizzo', 3);
QuerySetCell(mytestquery, 'Title', 'VP', 3);
</cfscript>

<cfoutput>
<pre>
#CSVFormat(mytestquery, "'")#
</pre>
</cfoutput>

How would I plug that into what I am doing... I am really new to using these UDF's... By the way, thanks for your help, it has been really beneficial!!!

[cf_dev2]

You don't need the first part. The QueryNew/QuerySetCell is only meant to simulate a database query.

Usually you put all your UDF's in one location. But for now, just copy the UDF code to your page and use #CSVFormat(getfields, "'")#

Edit: Attached full example

[Newsgroup_User]

That was just a test|demo script building a query record set by hand to
show the UDF in action. The only important line to you is:

#CSVFormat(mytestquery, "'")#

Just place your query into the function call, and make sure you have
included the function code in your applicaiton and your will have CSV
formated data.

<cfquery name="getTables" datasource="#db#">
select *
from table
</cfquery>
...
CSVFormat(getTables,"'")

You will probably want to put the results of the function call into a
<cffile...> tag or some variable to be used later.

[Kronin555]

I know there's a way to do it without evaluate, so maybe someone else will chime in here...
Edit: Nevermind, cf_dev posted before me. I had the row and column array references swapped when I was trying it originally...
:-)

<table>
<tr>
<cfloop list="#thecolumnlist#" index="i">
<cfoutput>
<td>#i#</td>
</cfoutput>
</cfloop>
</tr>
<cfoutput query="getfields">
<tr>
<cfloop list="#thecolumnlist#" index="columnName">
<td>#getfields[columnName][currentrow]#</td>
</cfloop>
</tr>
</cfoutput>
</table>