Skip to main content
A
Anonymous
June 8, 2010
Answered

reading dynamic sql columns in coldfusion

  • June 8, 2010
  • 1 reply
  • 2818 views

I have a query which has static as well as dynamic columns. something like below:

<cfset vMarks = "marks1, marks2, marks3">
<cfquery name="querymarks" datasource = "abc">

SELECT firstname, lastname,
<cfloop from="1" to="listlen(vMarks)" index="index">
    marks_#index# <cfif #index# NEQ listlen(vMarks)>,</cfif>
</cfloop>
</cfquery>

The query result set will look like below:

firstname lastname  marks1 marks2   marks3 ...
abc        abc         112        113        114
def        def           121        122        123

So when I am using coldfusion to display the above resultset I am doing the below:


<table>
  <tr>

    <td>FIRST NAME</td>
    <td>FIRST NAME</td>
    <td>FIRST NAME</td>

  </tr>
  <cfoutput query="querymarks">
  <tr>

    <td>querymarks.firstname</td>
    <td>querymarks.lastname</td>
    <cfloop from="1" to="listlen(vMarks)" index="index">
     <td>querymarks.marks_#index#</td>
    </cfloop>
  </tr>
  </cfoutput>
</table>

The number of marks column is dynamic but I have a variable which stores the list of marks. I am facing problem in displaying the marks with coldfusion. Can anyone let me know if this can be done?

This topic has been closed for replies.
Correct answer Reed_Powell-ttnmOb

something like this:

    <cfloop from="1" to="listlen(vMarks)" index="index">
     <td>#querymarks[index][querymarks.currentRow]#</td>
    </cfloop>

1 reply

A
Adam Cameron.
Inspiring
June 8, 2010

It's not immediately apparent in the docs, but one can use associative array notation with queries, eg:

myQuery[myColumn][myRow]

Where myQuery is the name of a query variable, myColumn is a column name or a string containing a column name, and myRow is the row number (positive integer) you want.

You can also get query metadata with getMetatdata(myQuery), which lists the columns in the order they were queried for (unlike myQuery.columnList which alphabeticised it, for some stupid reason best known to some Allaire developer of yore).

--
Adam

A
Anonymous
June 8, 2010

Using either #querymarks[marks_index]# or querymarks[marks_#index#] is not helping

ilssac
ilssac
Inspiring
June 8, 2010

If you use array notation, you have to fully name the variable, including the row.

The short cuts provided by the <cfoutput...>and <cfloop...> query loops allow you to not do this.  But when you go it on your own, the fully qualified referernce is query["column"][row].

I think you are looking for something like this.

#querymarks["marks_" & index][1]#

Where 1 would show you the value for the first row.

Terms & ConditionsAccessibility statement
Using the community Terms of use Privacy Cookie preferences Do not sell or share my personal information ad choicesAdChoices