reading dynamic sql columns in coldfusion

[Guest]

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?

[Adam Cameron.]

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

[Guest]

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

[ilssac]

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.

[Reed_Powell-ttnmOb · Accepted solution]

something like this:

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

[Adam Cameron.]

funandlearning333 wrote:

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

Well no.  Note what I suggested the syntax was: myQuery[myColumn][myRow].  No part of that is optional, for your requirement.

--

Adam

[Dan_Bracuk]

You are actually very close.  Since part of your column name is a variable, you do it like this:

queryname["static part" & variable part][row number]

But Adam's method is better.