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
WHERE studentid = 'jack';
<cfset variables.TimeTotals = variables.TimeTotals + timerec.thistime>
1 person found this helpful
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.
<cfquery name rs_time ...>
SELECT Btime1, Btime2, Btime3 Btime4
<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]>
SELECT Btime1 + Btime2 + Btime2 + Btime4 AS totalTime
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.
Thanks so much! That worked 100%. Never knew this trick, but it surely is a very very helpful one!
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.