CODEWITHSUNDEEP
mysqlcoldfusion
Marthinus Strydom
Marthinus Strydom

Reputation: 267

Output MySQL dynamically without column names using ColdFusion

I want to dynamically output a table and it's data. I have the data source and the Table Name.

I was looking at using <cftable> but I am stuck with the dynamic column names.

This is basically what I want to do - it obviously does not work, but here for illustration purposes.

<cfset datasource = "test">
<cfset tablename = "mytable">

<!--- first get the columns from the table --->
<cfquery name="getcolumns" datasource="#datasource#">
SELECT COLUMN_NAME, DATA_TYPE
FROM INFORMATION_SCHEMA.COLUMNS 
WHERE TABLE_NAME = '#mytable#';
</cfquery>

<!--- Now select all from the table --->
<cfquery name="selectall" datasource="#datasource#">
SELECT      *
FROM        #mytable#
</cfquery>

<!--- Output all rows from the table --->
<cftable query="selectall">
  <cfoutput query="getcolumns">
     <cfcol text="#COLUMN_NAME#" header="#COLUMN_NAME#">
</cfoutput>
</cftable>

Upvotes: 2

Views: 191

Answers (1)

Dan Bracuk
Dan Bracuk

Reputation: 20794

There are two approaches you can use. Documented feature queryName.columnlist gives you a list of all the column names in alphabetical order. Undocumented feature queryName.getcolumnlist() gives you an array of all the column names in the order they appear in the select clause.

This allows you to do something like this.

<cfoutput query="queryName">
<cfloop list = "#queryName.columnlist#" index = "column">
#queryName[column][currentrow]#
closing tags

Upvotes: 3

Related Questions