How can I dynamically reference column names while looping through a query's results?-CodePudding

I have successfully written a SELECT statement that uses dynamic variables. It uses variables from the page to know what columns to select from a big, messy table. It looks something like this.

<cfquery name="getCapabilityAndDescription" datasource="Stuff">
SELECT [EntryID],
<cfloop list="#JobLevelList#" index="JobLevelIndex">
   [#getJobDesc.Type##JobLevelIndex#Capability],
   [#getJobDesc.Type##JobLevelIndex#Description],
</cfloop>
[JobID]
FROM [JobCapabilityMatrix]
WHERE JobID = <cfqueryparam cfsqltype="CF_SQL_INTEGER" value="#URL.JobID#">
AND SkillID = <cfqueryparam cfsqltype="CF_SQL_INTEGER" value="#URL.SkillID#">
</cfquery>

Say for example, it dynamically selects a column called Baker3Description.

But...How would I be able to output that dynamically? I guess to sum it up, I need to create a dynamic variable and then put it in a query's scope.

So far I have tried setting it and calling it like this:

<cfset DescriptionVariable = #JobTitle# & #JobLevelIndex# & 'Description'>
<cfoutput>
#getCapabilityAndDescription.DescriptionVariable#
</cfoutput>

But it doesn't evaluate the way I want it to, and just tells me

Element DESCRIPTIONVARIABLE is undefined in GETCAPABILITYANDDESCRIPTION.

I know there's a way to actually return the content of Baker3Description from the query, maybe using array syntax? But I've messed around too much and need a little help. Please & thanks.

CodePudding user response：

It's possible using structure notation, but also requires a query row number. The general syntax is

#queryName[ "columnName" ][ rowNum ]# 
<!--- 
.. or specifically 
--->
#getCapabilityAndDescription[ DescriptionVariable ][ 1 ]#

To output all of the query columns dynamically. Use GetMetaData() to retrieve an array of query column properties (in select order). Then use the name property to output each column value:

<!--- Demo query --->
<cfset yourQuery = queryNew("EntryID,Baker3Description"
        , "integer,varchar"
        , [[1,"Descrip A"],[2,"Descrip B"]]
    )>

<cfset meta = getMetaData(yourQuery)>
<cfoutput query="yourQuery">
    <cfloop array="#meta#" index="props">
        #yourQuery[props.name][currentRow]#
    </cfloop>
    <br>
</cfoutput>

Though be careful building that kind of dynamic sql. If any of the values used to build the column names are user supplied (for example getJobDesc.Type) the query will be vulnerable to second order sql injection.