CODEWITHSUNDEEP
coldfusion
brandoonjen
brandoonjen

Reputation: 111

How to deal with variable in recordcount

I have a query called "getvalues". This query will loop a varying amount of times, each time adding a number to the end of it (to create multiple queries).
ex: getvalues1, getvalues2, getvalues3, getvalues(so on). That being said, I'm trying to get a total record count of all these queries. The variable totalrecords is set to 0, outside the loop. After that, each time the loop runs, the record count of the current query will be added to totalrecords.

The issue I'm having here is with the placement of the octothorpes. If I set it up as:

<cfset totalrecords = totalrecords + #getvalues1.recordCount#>

It runs with no issues, so I know for sure that this is the problem. 

<cfloop from="1" to=#numberOfTables# index="q">
<cfquery name="getvalues#q#" datasource="mydb"
</cfquery>
<cfset totalrecords = totalrecords + #getvalues#q#.recordCount#>
</cfloop>

Sorry if I worded this question weird. I'm not sure how to ask it, but I hope I got my question across. Any help would be greatly appreciated.

Upvotes: 1

Views: 253

Answers (3)

cfEngineers
cfEngineers

Reputation: 674

    <cfquery name="getTotals" dbtype="query">
    <cfloop from="1" to="#numberOfTables#" index="idx1">
        select count(pkID) from getvalues#idx1# as recCount
        <cfif idx1 lt numberOfTable>
            UNION
        </cfif>
    </cfloop>
</cfquery>

<cfquery name="getGrandTotal" dbtype="query">
    select sum(recCount) as GrandTotal from getTotals
</cfquery>

<cfoutput>#getGrandTotal.GrandTotal#</cfoutput>

Upvotes: 1

Jules
Jules

Reputation: 2019

You can use evaluate():

<cfset totalrecords = totalrecords + #evaluate('getvalues' & q & '.recordCount')#>

Upvotes: 1

Robert Munn
Robert Munn

Reputation: 798

Is there some reason you can't/do not want to make a single query against the database that returns all of the data you need? You could move the loop inside the query and create UNION queries in a single SQL statement. Alternatively, depending on your database, you could write a stored procedure/function that did the same thing. Making a single call to the database and returning all the data you need, aggregated however you need it, is a lot more efficient than looping a variable number of times and returning a segment of the data each time.

Upvotes: 3

Related Questions