7 Replies Latest reply on Jul 10, 2007 10:12 AM by Newsgroup_User

    Get Fields In Table Dynamically

    bweno Level 1
      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
      <cfset thecolumnlist = getfields.columnlist>
      <cfloop list="#thecolumnlist#" index="i">

      Thanks to anyone who can help me!!!
        • 1. Re: Get Fields In Table Dynamically
          cf_dev2 Level 1
          Do you want to output the query values or just the column names?

          • 2. Re: Get Fields In Table Dynamically
            bweno Level 1
            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!!!
            • 3. Get Fields In Table Dynamically
              cf_dev2 Level 1
              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">

              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

              • 4. Get Fields In Table Dynamically
                Kronin555 Level 1
                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">
                <cfoutput query="getfields">
                <cfloop list="#thecolumnlist#" index="columnName">
                • 5. Re: Get Fields In Table Dynamically
                  bweno Level 1
                  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');
                  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);

                  #CSVFormat(mytestquery, "'")#

                  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!!!
                  • 6. Get Fields In Table Dynamically
                    cf_dev2 Level 1
                    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
                    • 7. Re: Get Fields In Table Dynamically
                      Level 7
                      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

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