0

Is there a way by which the cfoutput can select all columns from the table and display on the webpage using a HTML table? I have a database table with a large number of columns, and I do not want to list out each column name in either cfquery or cfoutput part of the cfm webpage.

Example: Let's say Table1 has 26 columns - A,B,C,D,E....X,Y,Z. I wish not to specify these column names explicity, in cfoutput while building the HTML table. Instead prefer to dynamically build the table so that all columns that are in Table1 are presented as a table. 

<CFQUERY NAME="query1" DATASOURCE="abcd">
select a,b,c,....,x,y,x from table1
</CFQUERY>

Now in the table part of webpage:

<TABLE>
                    <TR> 
                        <TH>A</TH> 
                        <TH>B</TH>
                        <TH>..</TH>
                        <TH>Y</TH>
                        <TH>Z</TH>
                    </TR>   

                    <CFOUTPUT query="query1">
                    <TR align="center">
                        <TD>#a#</TD>
                        <TD>#b#</TD>
                        <TD>#...#</TD>
                        <TD>#y#</TD>                        
                        <TD>#z#</TD>                        
                    </TR>
                    </CFOUTPUT>
                </TABLE>

My actual tables would have quite a large number of columns. It would be easy to build the table dynamically rather than manually listing it. Any suggestions, much appreciated!

kallakafar
  • 725
  • 3
  • 11
  • 27
  • 2
    This sounds like a duplicate of [how to loop over the tables of a database?](http://stackoverflow.com/questions/21711894/how-to-loop-over-the-tables-of-a-database/21713369#21713369). Ignore the reference to tables, it is the same concept. – Leigh Jan 30 '15 at 14:49
  • 1
    Specifically, look at the columnlist variable of cfquery. It's in Leigh's reference. – Dan Bracuk Jan 30 '15 at 15:16
  • @Leigh and dan - thanks! I see the comment in that post referenced: Query the INFORMATION_SCHEMA.COLUMNS view instead. To get the columns for a single table, just filter on the TABLE_NAME instead of schema. ie ...WHERE TABLE_NAME = 'Some Table'........ This would mean that i need two queries - one to fetch the column names, and another the actual query to display the value in those columns, right? Wonder how this can be done though. I'm just using HTML and CFM - no other languages for programmatic access. Any suggestions? Thanks again. – kallakafar Jan 30 '15 at 15:22
  • 1
    @kallakafar - No, you do not need it. Just look at the first code snippet starting with ``. Where "q" is your query name. That loop is all you need to output your query values dynamically. [Here is another example that does the same thing](http://stackoverflow.com/questions/9626417/getting-complex-object-error-when-trying-to-output-query-values/9626700#9626700) – Leigh Jan 30 '15 at 15:25

1 Answers1

1

Here is another way.

fields = "Field1,Field2,etc";
columnHeaders = "Header 1,Header 2,etc"'

<cfquery name = "yourQuery">
select #fields#
etc
</cfquery>

<tr><cfoutput><cfloop list = columnHeaders index = "header">
<th>#header#</th>
</cfloop></cfoutput></tr>
<cfoutput query = "yourQuery">
<tr>
<cfloop list = fields index = "field">
<td>#yourQuery[field][currentrow]#</td>
</cfloop>
</tr>
</cfoutput>

If you need to do things like format dates, right align numbers, and such, it gets a little complicated.

Dan Bracuk
  • 20,699
  • 4
  • 26
  • 43
  • thanks, Dan. This looks promising. Let me try it out in my version. Will let you know! – kallakafar Jan 30 '15 at 15:37
  • 1
    @kallakafar - One important caveat about the example above. It assumes *you* control the contents of `#fields#`. Otherwise, it may be unsafe and could expose your database to [sql injection](https://en.wikipedia.org/wiki/SQL_injection). That said, I do not think you need that part anyway. Just run your existing query, and use the loop code to output the column values dynamically. – Leigh Jan 30 '15 at 16:53
  • 1
    There's a UDF in CFLib from 2011 that does this. http://www.cflib.org/udf/queryToTableDump I've further enhanced it with features to add AHREF prefexed link to a column, override natural query sortorder, auto-format date/time values, ability to only show specified columns and add unique IDs/classes for automatic TableSorter JS integration. – James Moberg Jan 30 '15 at 17:30