Dynamically accessing unknown query fields
Ever found yourself in a situation where you need to output values from a query, but you're unable to explicitly reference the columns by name? Perhaps the query is a "Select *", or the column names are being dynamically aliased within the sql based on the date. Whatever the reason, never fear, there are solutions to the challenge.
The key lies in the fact that queries can also behave like structures. (they can also behave as one dimensional arrays, if the field names are known ahead of time). To leverage this behavior, we're going to use the column name as the first key and the rownumber as the second key. Like so:
For the query "myQuery", the value of "Firstname" in the first record would have been output.
In comparison, if we had known the field name ahead of time and wanted to grab that field's value from a specific record, we could have treated our query as a one dimensional array and referenced it like
outputting the value for firstname in the second record of the data set.
(We're using QuerySim to fabricate our data set. If you don't have a copy you can get it here.):
One's first instinct may be to call upon the trusty ol' Evaluate function, which will definately work. But what would your friends say if they saw you using a function that has such an evil reputation as a process hog? Here's at least one alternative way to output a query when you don't know the column names ahead of time.
The key lies in the fact that queries can also behave like structures. (they can also behave as one dimensional arrays, if the field names are known ahead of time). To leverage this behavior, we're going to use the column name as the first key and the rownumber as the second key. Like so:
<cfset fldname = "firstname">
<cfoutput>#myQuery[fldname][1]#</cfoutput>
<cfoutput>#myQuery[fldname][1]#</cfoutput>
In comparison, if we had known the field name ahead of time and wanted to grab that field's value from a specific record, we could have treated our query as a one dimensional array and referenced it like
<cfoutput>#myQuery.firstname[2]#</cfoutput>
outputting the value for firstname in the second record of the data set.
One final example
Following is an example that outputs a query in table format, without knowning anything about the query ahead of time(We're using QuerySim to fabricate our data set. If you don't have a copy you can get it here.):
<cf_querysim>
UserInfo
userID,firstName,lastName,userGroups
100|Stan|Cox|33
200|Joe|Blow|35
300|Doug|Boude|21
400|Jim|Pickering|15
</cf_querysim>
<cfoutput>
<table>
<tr>
<cfloop
list="#UserInfo.columnlist#"
index="h">
<th>#h#</th>
</cfloop>
</tr>
<cfloop
from="1"
to="#UserInfo.recordcount#"
index="i">
<tr>
<cfloop
list="#UserInfo.columnlist#"
index="c">
<td>#UserInfo[c][i]#</td>
</cfloop>
</tr>
</cfloop>
</table>
</cfoutput>
UserInfo
userID,firstName,lastName,userGroups
100|Stan|Cox|33
200|Joe|Blow|35
300|Doug|Boude|21
400|Jim|Pickering|15
</cf_querysim>
<cfoutput>
<table>
<tr>
<cfloop
list="#UserInfo.columnlist#"
index="h">
<th>#h#</th>
</cfloop>
</tr>
<cfloop
from="1"
to="#UserInfo.recordcount#"
index="i">
<tr>
<cfloop
list="#UserInfo.columnlist#"
index="c">
<td>#UserInfo[c][i]#</td>
</cfloop>
</tr>
</cfloop>
</table>
</cfoutput>
Subscription Options
You are not logged in, so your subscription status for this entry is unknown. You can login or register here.
No comments found.