This content has been marked as final. Show 7 replies
Do you want to output the query values or just the column names?
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!!!
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]# .
<cfloop list="#getfields.ColumnList#" index="columnName">
There are a few functions at cflib.org for converting a query to a CSV file
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
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...
<cfloop list="#thecolumnlist#" index="i">
<cfloop list="#thecolumnlist#" index="columnName">
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:
mytestquery = QueryNew('FirstName,LastName,Title');
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);
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!!!
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
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:
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
<cfquery name="getTables" datasource="#db#">
You will probably want to put the results of the function call into a
<cffile...> tag or some variable to be used later.