How to display the column names from a query.

OK, here is the problem.

I have a query:

<cfquery name="AllListings" datasource="Home4SaleByMe" cachedwithin = "#CreateTimeSpan(0, 6, 0, 0)#">
SELECT * FROM user975947.Listing ORDER BY #SortBy#
</cfquery>

Now I never know how many columns there are, so I want to be able to loop through and list the actual column names from the database, like this (or something similar - you get the point).  I would also like to see if the column equals a known name and do something.:

<cfloop ...>
  <cfif AllListings.columnName(i) = "ID">
      This is the ID Column
  <cfelse>
    #AllListings.columnName(i)#
 </cfif>
</cfloop>
LVL 2
turbosigAsked:
Who is Participating?
 
danrosenthalCommented:
This should do it:

<cfquery name="AllListings" datasource="Home4SaleByMe" cachedwithin = "#CreateTimeSpan(0, 6, 0, 0)#">
SELECT * FROM user975947.Listing ORDER BY #SortBy#
</cfquery>

<CFLOOP list="#AllListings.columnlist#" index="column_name">
      <CFIF column_name eq "ID">
            This is the ID column
      <CFELSE>
            <CFOUTPUT>#column_name#</cfoutput>
      </cfif>
      <BR>
</cfloop>
0
 
jonnygo55Commented:
Assuming you know the name of the table you could do this:

<cfquery name="columns" datasource="Home4SaleByMe">
SELECT     sc.name
FROM         sysobjects so INNER JOIN
                      syscolumns sc ON so.id = sc.id
WHERE     (so.name = '#yourTableName#') AND (sc.autoval IS NULL)
ORDER BY sc.colid
</cfquery>

<cfoutput query="columns">
  <cfif columns.name = "ID">
        This is the ID Column
    <cfelse>
      #columns.name#
   </cfif>
</cfoutput>
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.