Possibly a Newbie Question ... Nested Loops and Querys

[wilsonpr]

I'm new to Cold Fusion but not DBs or Programming .... I have the following code ...

<cfoutput>
<cfloop index=&quot;eAddrs&quot; list=&quot;#myStr#&quot; delimiters=&quot;,&quot;>
<cfquery name=&quot;bums&quot; datasource = &quot;#app.datasource#&quot;>
SELECT Status, BadEmail FROM UserDetails WHERE
UserID = (SELECT UserID FROM Users WHERE Email
= '#eAddrs#')
</cfquery>
<cfloop query=&quot;bums&quot;>
<cfif #BadUserEmails.recordCount# LT 1>
#curEmailAddress# <b>>>>></b> #NotFound#
<cfelseif #BadUserEmails.recordCount# GT 1>
#curEmailAddress# <b>>>>></b> #Duplicate#
<cfelse>
#curEmailAddress# <b>>>>></b>
Status = bums.Status# <b>>>>></b>
BadEmail = #bums.BadEmail#
</cfif>
</cfloop>
<br>
</cfloop>
</cfoutput>

... What I want it to do is &quot;for each member in the list run the query and do some work (in this case just printing the results ... baby steps)&quot; ... what it's doing is running the query once and doing the work for the first member of the list then just looping through the list and never running the query again .... I can't seem to find an example of this type of opperation or I'm not filling in the search boxes with Cold Fusion Terms

 

[SidianConsulting]

<!--- myStr is already a comma-delimited string variable --->

<!--- The listqualify() function in the below query takes your myStr list and qualifies each item with a single quote; this way
the database does the work by using the IN clause on the list of email addresses and subsequently using the IN clause on
the list of userid's retrieved --->

<cfquery name=&quot;bums&quot; datasource = &quot;#app.datasource#&quot;>
SELECT Status, BadEmail FROM UserDetails WHERE
UserID IN (SELECT UserID FROM Users WHERE Email
IN (#ListQualify(myStr,&quot;'&quot;,&quot;,&quot;,&quot;ALL&quot;)#))
</cfquery>
<!--- When outputting query data, use cfoutput, not cfloop. --->
<cfoutput query=&quot;bums&quot;>
<cfif BadUserEmails.recordCount LT 1>
#curEmailAddress# <b>>>>></b> #NotFound#
<cfelseif BadUserEmails.recordCount GT 1>
#curEmailAddress# <b>>>>></b> #Duplicate#
<cfelse>            
#curEmailAddress# <b>>>>></b>
Status = #bums.Status# <b>>>>></b>
BadEmail = #bums.BadEmail#
</cfif>
</cfoutput>
<br>

Note a couple of things... I didn't test the code, but it SHOULD be right. For future reference, look up the cfqueryparam function (which is used inside the cfquery tags); it's something you should be using unless you plan on moving your cfquery code to stored procedures down the road. Actually, either way, check it out.

-Tek

 

[imstillatwork]

i think the reason most people do not use the cfqueryparam tag is because by default it is not listed in CFStudio 5.

 

[csteinhilber]

I don't use it mainly because I validate all my data prior to the SELECT/INSERT, giving me the opportunity for a nice, friendly error message rather than CFQUERYPARAMs errors. Hope it helps,
-Carl

 

[wilsonpr]

Got it, thanks for all the suggestions!

 

[SidianConsulting]

Carl,

Using cfqueryparam is not only useful for data validation (I validate beforehand as you do), but can boost performance. Cfqueryparam creates &quot;SQL bind parameters&quot; for the SQL statement:

http://livedocs.macromedia.com/cfmxdocs/CFML_Reference/Tags-pt219.jsp#1102474

Here is an article on cfqueryparam by Ben Forta which explains things in more detail:

http://www.macromedia.com/desdev/articles/ben_forta_faster.html

-Tek