Evaluate within CFSET

[H3ath0r]

I'm looping through a list of sites. I've written queries to count the number
of live and pending articles in tables that begin with the site's abbreviation.
(See
[url]http://www.macromedia.com/cfusion/webforums/forum/messageview.cfm?catid=7&thread[/url]
id=1014489&forumid=1 for more details.)
The code #Evaluate(Abbrev &"Live.NumberOfLiveArticles")#+ gives me 0+ 8+ 0+
0+ 0+ 15+
but I can't get it to work within a CFSET tag where I'd like to get the total.

CODE:


<cfloop query="BBIPsites">
<cfoutput> #Evaluate(Abbrev &"Live.NumberOfLiveArticles")#+ </cfoutput>
</cfloop>
Where within the loop should I stick this?
<cfset LiveTotal = #Evaluate(Abbrev &"Live.NumberOfLiveArticles")#+>

[OldCFer]

You need to evaluate twice. Once to get the string "0+ 8+ 0+ 0+ 0+ 15" from
the
dynamic variable, then again to evaluate the value of the string, The second
evaluate has no quotes.

<cfset LiveTotal = Evaluate(Abbrev &"Live.NumberOfLiveArticles")>
<cfset LiveTotal = Evaluate(LiveTotal)>

You can't have that trailing "+" in there. I don't know what it's for but
it will cause errors trying to do math operations.

[Stressed_Simon]

You don't need Evalutate(), you should use

#VARIABLES[Abbrev & "Live.NumberOfLiveArticles"]#

instead of

#Evaluate(Abbrev &"Live.NumberOfLiveArticles")#

as for where to put <cfset LiveTotal = LiveTotal + #VARIABLES[Abbrev &
"Live.NumberOfLiveArticles"]#>, you should put that anywhere after the query
that gets the information but still within the loop.

Try never to use Evaluate() it is slow and can open up security risks, it is
very rare that with a bit of thought you cannot find an alternative.

[mxstu]

First to answer your question. You could declare a total variable before you
enter the loop
<cfset TotalCount = 0>

and then inside the loop append the count to the running total ..
<cfset TotalCount = TotalCount + Evaluate(Abbrev &"Live.RecordCount")>

In looking at your code again, if all you're doing in the loop is outputting
the totals, you may not actually need to use a dynamic query name at all. You
could use a single query name and avoid the call to Evaluate() altogether. It
might also be possible to create a single query to retrieve the counts and
avoid the separate query on each loop, but ... that depends on what else you're
doing in the page.






<!--- Using your original code. Not tested ---->
<cfloop query="BBIPsites">
<cfquery name="PendingArticles" datasource="BBN">
SELECT ID FROM #TableName#
WHERE Author = #Session.AuthorID# AND LIVE = 0
</cfquery>
<cfquery name="LiveArticles" datasource="BBN">
SELECT ID FROM #TableName#
WHERE Author = #Session.AuthorID# AND LIVE = 1
</cfquery>
<cfoutput>
<strong>#SiteName#</strong><br>
The number of live articles on #SiteName# is #PendingArticles.RecordCount#<br>
The number of pending articles on #SiteName# is #LiveArticles.RecordCount#<br>
<br>
</cfoutput>
</cfloop>

[Stressed_Simon]

mxstu is right you will need to set the LiveTotal equal to zero before your loop!

[mxstu]

Stressed_Simon,

Is that a feature of MX 7? I tried accessing the query name via the variables
scope under MX 6.1 and am getting "Element (element name) is undefined in a
Java object of type class coldfusion.runtime.VariableScope referenced as "
error.

[Stressed_Simon]

Really???? I am on MX 6.1 and I use that all the time!

Try this:-

<cfset q = QueryNew("test")>
<cfset QueryAddRow(q)>
<cfset QuerySetCell(q, "test", "Simon")>

<cfdump var="#q#">
<cfdump var="#VARIABLES.q#">
<cfdump var='#VARIABLES["q"]#'>

[mxstu]

Maybe I just need coffee but ...

<cfset Abbrev = "ThisNameIsDynamic">
<cfquery name="#Abbrev#Live" datasource="myDSN" maxrows="1">
SELECT ID AS NumberOfLiveArticles
FROM MyTable
</cfquery>

<!--- this works -->
<cfdump var="#Abbrev#Live">

<!--- this works -->
<cfoutput>
<cfdump var="#VARIABLES[Abbrev & 'Live']['NumberOfLiveArticles']#">
</cfoutput>

<!--- this doesn't work -->
<cfoutput>
#VARIABLES[Abbrev & "Live.NumberOfLiveArticles"]#
</cfoutput>

Element ThisNameIsDynamicLive.NumberOfLiveArticles is undefined in a Java
object of type class coldfusion.runtime.VariableScope referenced as

[H3ath0r]

By combining your answers I got a result that works!

MXSTU, yesterday I ended up using your suggesting to use COUNT(ID) so the
queries are a bit different than the ones posted here (see code below).

Now I'll have to investigate the ramifications of using Evaluate() or
VARIABLES as Stressed_Simon suggests. I wasn't aware this was a concern. The
whole application, however, is in a secure area where users are logged in. So
I'm not too worried about security overall.

Thanks a tonne for all your help! My code has gotten messier than I like
because of scope creep. Originally there were only a few sites so I set up the
database with the articles for each site in a separate table. But now that
number is growing so I'm finding myself having to loop through all these
separate tables and wishing all the articles were in ONE table. Ah, growing
pains.

CODE:

<cfset LiveTotal = 0>
<cfloop query="BBIPsites">
<cfoutput>
<cfquery name="#Abbrev#Live" datasource="BBN">
SELECT COUNT(ID) AS NumberOfLiveArticles
FROM #TableName#
WHERE Author = #Session.AuthorID# AND LIVE = 1
</cfquery>
<cfquery name="#Abbrev#Pending" datasource="BBN">
SELECT COUNT(ID) AS NumberOfPendingArticles
FROM #TableName#
WHERE Author = #Session.AuthorID# AND LIVE = 0
</cfquery>
<cfset LiveTotal = LiveTotal + Evaluate(Abbrev &"Live.NumberOfLiveArticles")>
</cfoutput>
</cfloop>

[Stressed_Simon]

Yeah sorry that should have been:-

#VARIABLES[Abbrev & "Live"]["NumberOfLiveArticles"]#

I think I might join you for a coffee!

[mxstu]

H3ath0r,

Yes, as Stressed_Simon mentioned, evaluate() it is slower among other things,
so using the variables scope would probably be a better option.

On the upside, if you do decide to combine the data into one table all you
have to do is add an INSERT statement to CFLOOP script ;-)

Stressed_Simon,

Good thing there is enough coffee left in the pot to go around!

[H3ath0r]

You're both right! See code.
Now, explain to me why I should go through my code and replace my Evaluate()
instances with VARIABLE where possible....

Code:


<cfset Abbrev = "ThisNameIsDynamic">
<cfloop query="BBIPsites">
<cfoutput>
<cfquery name="#Abbrev#Live" datasource="MyDSN" maxrows="1">
SELECT COUNT(ID) AS NumberOfLiveArticles
FROM #TableName#
WHERE Author = #Session.AuthorID# AND LIVE = 1
</cfquery>

<!--- this works --->
<cfdump var="#Abbrev#Live">

<!--- this works --->
<cfoutput>
<cfdump var="#VARIABLES[Abbrev & 'Live']['NumberOfLiveArticles']#">
</cfoutput>

<!--- this also works --->
<cfoutput>
<cfdump var="#VARIABLES[Abbrev & "Live"]["NumberOfLiveArticles"]#">
</cfoutput>

</cfoutput></cfloop>

[OldCFer]

You can't refer to query variables using the variables scope. You may be able to do a
cfdump using that, but nothing else. Am I missing something?

[Stressed_Simon]

This works?

<cfquery name="test" datasource="#REQUEST.data#">
SELECT TestimonialText, TestimonialPerson
FROM Testimonials
</cfquery>

<table>
<cfoutput query="VARIABLES.test">
<tr>
<td>#TestimonialText#</td>
<td>#TestimonialPerson#</td>
</tr>
</cfoutput>
</table>

[mxstu]

It seems like you can. Try it.

<cfquery name="TEST" datasource="myAccessDSN">
SELECT ID AS NumberOfLiveArticles
FROM YourTable
</cfquery>

<cfoutput>
#VARIABLES.TEST.NumberOfLiveArticles#<br>
</cfoutput>

[mxstu]

H3ath0r,

While we're all questioning the reality of whether or not you can and/or
should access query variables using the variables scope (myself included ;-) ...

The primary reason to avoid Evaluate() is efficiency. Evaluate() requires
some additional processing, so it usually more efficient to retrieve the values
from an available structure, such as retrieving form field values from the FORM
structure, rather than using evaluate().

See
[url]http://livedocs.macromedia.com/coldfusion/7/htmldocs/wwhelp/wwhimpl/common/html/[/url]
wwhelp.htm?context=ColdFusion_Documentation&file=0 0000946.htm

[H3ath0r]

Using the variable scope you suggested WORKS, but it means an additional query
for each site in the loop. This is because my tablename is a dynamic variable.
Compare the CFSET for LiveTotal versus PendingTotal at the end of this code.
Both work but I still don't understand why I'd choose one over the other.
I'll make another pot of coffee for all who've lasted with me this far...
Code:


<cfset LiveTotal = 0>
<cfset PendingTotal = 0>
<cfloop query="BBIPsites">
<cfoutput>
<cfquery name="#Abbrev#Live" datasource="BBN">
SELECT COUNT(ID) AS NumberOfLiveArticles
FROM #TableName#
WHERE Author = #Session.AuthorID# AND LIVE = 1
</cfquery>
<cfquery name="#Abbrev#Pending" datasource="BBN">
SELECT COUNT(ID) AS NumberOfPendingArticles
FROM #TableName#
WHERE Author = #Session.AuthorID# AND LIVE = 0
</cfquery>

<cfquery name="TEST" datasource="BBN">
SELECT COUNT(ID) AS NumberOfLiveArticles
FROM #TableName#
WHERE Author = #Session.AuthorID# AND LIVE = 1
</cfquery>

<!-- This works -->
<cfoutput>
Test output is #VARIABLES.TEST.NumberOfLiveArticles#<br>
</cfoutput>

<cfset LiveTotal = LiveTotal + #VARIABLES.TEST.NumberOfLiveArticles#>
<cfset PendingTotal = PendingTotal + Evaluate(Abbrev
&"Pending.NumberOfPendingArticles")>

</cfoutput>
</cfloop>

[H3ath0r]

Okay... so given what I read on
[url]http://livedocs.macromedia.com/coldfusion/7/htmldocs/wwhelp/wwhimpl/common/html/[/url]
wwhelp.htm?context=ColdFusion_Documentation&file=0 0000946.htm, then I guess
since we showed above that I CAN use the variable scope in this case, then I
SHOULD go ahead and do so.

Phew. You boys are thorough.
*smeck* a kiss on the cheek for all!
Heather

[OldCFer]

CF stores the whole query in the variables scope, so
#VARIABLES.TEST.NumberOfLiveArticles#<br>
is the same as
#TEST.NumberOfLiveArticles#<br>
so I guess it's implied, but how do you refer to
#VARIABLES.TEST.NumberOfLiveArticles#<br>
in bracket notation to eliminate using Evaluate?

[mxstu]

<cfloop query="Variables.TEST">
#Variables['TEST']['NumberOfLiveArticles'][CurrentRow]#<br>
</cfloop>