Dynamic RecordCount

[H3ath0r]

I am looping through a list of sites and counting the number of articles live
and pending. I'd like to be able to refer to the RecordCount for each. But
when I put hacks around the AbbrevLive.RecordCount variable, it doesn't work.
How do you call to a recordcount when the name of the query is a dynamic
variable?

CODE:

<cfquery name="BBIPsites" datasource="BBN">
SELECT *
FROM Sites
ORDER BY SiteName
</cfquery>

<cfloop query="BBIPsites">
<cfoutput>
<strong>#SiteName#</strong><br>
<cfquery name="#Abbrev#Pending" datasource="BBN">
SELECT ID FROM #TableName#
WHERE Author = #Session.AuthorID# AND LIVE = 0
</cfquery>
<cfquery name="#Abbrev#Live" datasource="BBN">
SELECT ID FROM #TableName#
WHERE Author = #Session.AuthorID# AND LIVE = 1
</cfquery>
The number of live articles on #SiteName# is #Abbrev#Live.RecordCount<br>
The number of pending articles on #SiteName# is #Abbrev#Pending.RecordCount<br>
<br>
</cfoutput></cfloop>

[mxstu]

Use Evaluate()

<cfoutput> RecordCount: #Evaluate(Abbrev &"Live.RecordCount")# </cfoutput>

If you only need the total count, try using the COUNT() function. This avoids
retrieving records you don't need.



<cfquery name="#Abbrev#Live" datasource="yourDSN">
SELECT COUNT(ID) AS NumberOfLiveArticles
FROM YourTable
<!--- your where clause here ... --->
</cfquery>

<cfoutput>
Number of Live Articles: #Evaluate(Abbrev &"Live.NumberOfLiveArticles")#
</cfoutput>

[H3ath0r]

Both methods work great. Thanks so much for your suggestion to use the
Count() Function. That's a good idea. And I can use your other Evaluate
string in other instances where I'm looping through the data looking for things
other than the count.