Cfquery results to an array?

Get Started
1

I have a <cfquery> that outputs 3 columns (ct_pass (integer), ct_fail (integer), notes (varchar)). Seems as if it should be a simple matter to output the results to an array, then reference that array by row number and column number… but NOT. :nono: I’ve tried using code from several Google queries on “coldfusion, query, array”, but to no avail.

  1. How should I populate the results of a <cfquery> into an array?
  2. How do I then reference subsequent items within that array?
  3. OR… Is there a way for me to reference <cfquery> results directly (by row and column)?

Thanks in advance!

2

Option #3 is simplest. A query already is like an array. You can reference values using array notation: #queryName[“columnName”][rowNumber]#

Example, to display the “ct_pass” value in row 2 (assuming that row exists …)
<cfoutput>
#yourQueryName[“ct_pass”][2]#
</cfoutput>

3

OK, this does output the given column and row. When I try to reference the given column and row within <cfset> though, I get the error “[Table (rows N columns … ] is not indexable by 1”. Here’s what I’m trying to do:

<cfquery name="traceResults" datasource="#ds#">
    SELECT DISTINCT ij_pass.ct_pass,ij_fail.ct_fail,t.notes_trans FROM ...
</cfquery>
<cfif traceResults.recordcount EQ 2>
    <cfoutput>
        <cfset lastTracePassCt = #traceResults["ct_pass"][2]# />
        <cfset lastTraceFailCt = #traceResults["ct_fail"][2]# />
        <cfset lastTraceTotCt = lastTracePassCt + lastTraceFailCt />
    </cfoutput>
    ...
</cfif>

I have tried surrounding the expression with quote marks and also using the evaluate() function; still, no success. Interestingly enough, when I surround the expression in quotes AND use evaluate()…

<cfset lastTracePassCt = '#evaluate(traceResults["ct_pass"][1])#' />

… the error changes to “String index out of range: 0”. Any ideas? TIA again!

4

try this –


<cfset lastTracePassCt = #traceResults.ct_pass[2]# />
<cfset lastTraceFailCt = #traceResults.ct_fail[2]# />
5

“[Table (rows N columns … ] is not indexable by 1”. Here’s what I’m trying to do:

That usually means one or more of the column names you’re using can’t be found. But looking at your code, are you trying to calculate the total passed and failed for each record? Which db are you using? Because you can probably do that in your SQL OR in a QoQ. Assuming the fields are numeric, something like

<cfquery name=“traceResults” dbtype=“query”>
SELECT ct_pass, ct_fail, notes_trans, ct_pass + ct_fail AS TotalPassFail
FROM traceResults
</cfquery>

<cfif traceResults.recordcount EQ 2>
<cfoutput>
<cfset lastTracePassCt = #traceResults[“ct_pass”][2]# />
<cfset lastTraceFailCt = #traceResults[“ct_fail”][2]# />
<cfset lastTraceTotCt = lastTracePassCt + lastTraceFailCt />
</cfoutput>
…
</cfif>

Also

  1. The row number 2 was just an example. It may not be right for what you’re trying to do
  2. It won’t cause an error, but if you’re not actually displaying anything, you don’t need the cfoutput OR the # signs.