Access database dynamic query output...

[CFSAVVY]

Dear CF Colleagues:

I need to run a loop for 4 times, to access four seperate values out of a data row in a test results table (tbl_results), where all four are in the row of the student (WHERE studentid = etc).

It looks like this...

  <cfset variables.TimeTotals = 0>

  <cfloop from="1" to="4" step="1" index="timrec">

     <cfquery datasource="mydsn" name="rs_time">

      SELECT Btime#timrec#

      FROM tbl_results

      WHERE studentid = 'jack';

     </cfquery>

<cfset variables.TimeTotals = variables.TimeTotals + (????? rs_time.Btime#timrec# value ?????)>

</cfloop>

*** QUESTION: How do I address that Btime#timrec# value? Not in this way, I know, but how?

Looking forward to your wisdom, with gratitude,

Hans

[Reed_Powell-ttnmOb · Accepted solution]

There are a couple of ways to attack this, but you're pretty close.  One way is to just include a column alias in the query, then use it to reference the results:

<cfset variables.TimeTotals = 0>

  <cfloop from="1" to="4" step="1" index="timrec">

     <cfquery datasource="mydsn" name="rs_time">

      SELECT Btime#timrec# AS thistime

      FROM tbl_results

      WHERE studentid = 'jack';

     </cfquery>

<cfset variables.TimeTotals = variables.TimeTotals + timerec.thistime>

</cfloop>

[CFSAVVY]

Dear Reed:

Thanks so much! That worked 100%. Never knew this trick, but it surely is a very very helpful one!

Thanks again!

Hans

[ilssac]

As Reed says, many ways to tackle this.

I would also point out the ability to ready all the BTIme... columns with one query then loop through the columns to total that time.... OR total them in the database.

These both have the advantage of only needing one network connection to the database, which is the slowest part of this process.

A)

<cfquery name rs_time ...>

   SELECT Btime1, Btime2, Btime3 Btime4

  ...

</cfquery>

<cfset variable.TimeTotals = 0>

<cfloop list="#rs_time.columnList#" index="col">

     <cfif left(col,5) EQ "BTIME"><cfset variables.TimeTotals = variable.TimeTotals + rs_time[col][1]>

</cfloop>

B)

<cfquery name="rs_time"...>

     SELECT Btime1 + Btime2 + Btime2 + Btime4 AS totalTime

     ...

</cfquery>

OF COURSE

I would be remis if I did not point out that you would have a more expandable database design if you normalized this so that the test times where not columns in the tbl_resluts table but rather rows.

[CFSAVVY]

Excellent input. Thanks for this, Ilssac!

I agree that the list of columns is less preferable and less normalized than having the data stored in rows. However, we have a specific reason for doing it this way. I considered the logical alternative, but it creates more issues in the results querying.

Very grateful, though, for your demo on how to solve this challenge. I've now gained extra tools for my database toolbox.

Thanks!

Hans