Coldfusion - cfmail query question

Railo4, windows server, mysql5.1

I wrote the below code so that a user on my real estate site could send their agent a list of their saved favorite properties.

Lets say I have 4 saved favorites, what is happening is that instead of sending one email with links to my 4 favs, it sends 4 emails. The first email has one fav, the 2nd has 2 favs, the 3rd has 3, etc..

Any help getting this debugged is appreciated!



<cfquery datasource="myds" name="sendtoAgent">
Select fagents.Agent,fagents.AgentEmail,usr.AgentID,usr.email,usr.first,usr.last,usr_residential_fav.ListID
FROM usr INNER JOIN fagents ON fagents.Agent = usr.AgentID INNER JOIN usr_residential_fav ON usr.pk_usr = usr_residential_fav.k_usr where usr.pk_usr = <cfqueryparam value="#URL.usrID#" cfsqltype="cf_sql_integer">
</cfquery>

<CFMAIL query="sendtoAgent"
FROM="#sendtoAgent.email#"      
TO="#sendtoAgent.AgentEmail#"
SUBJECT="My Favs"      
Type="html">
<h2>Here is a list of my Favs...</h2>

<ul>

<cfoutput>
<li><a href="http://mysite/details.cfm?ListID=#ListID#">http://mysite/details.cfm?ListID=#ListID#</li>
</cfoutput>

</ul>

</cfmail>
Bang-O-MaticAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Brijesh ChauhanStaff IT EngineerCommented:
Can you send the dump of your query (say 20 records) ?

<cfquery datasource="myds" name="sendtoAgent" maxrows="20">
      Select fagents.Agent,fagents.AgentEmail,usr.AgentID,usr.email,usr.first,usr.last,usr_residential_fav.ListID
      FROM usr INNER JOIN fagents ON fagents.Agent = usr.AgentID INNER JOIN usr_residential_fav ON usr.pk_usr = usr_residential_fav.k_usr where usr.pk_usr = <cfqueryparam value="#URL.usrID#" cfsqltype="cf_sql_integer">
</cfquery>

<cfdump var="#sendtoAgent#">
0
Bang-O-MaticAuthor Commented:
screenshot of dump
I only had 7 records so I dumped those.

I think I have found the issue https://groups.google.com/forum/#!topic/railo/84qrtSd0kOs

and it is a bug in Railo.

In the technote it was recommended to move the query attribute from the mail tag but since my to and from fields are populated by the query, how could I adjust for this?

"if you query does not contain email recipients - do not use it in query attribute in cfmail tag:
<cfmail to="..." from="..." subject="...">
...
<cfoutput query="qName">
...
</cfoutput>
</cfmail>"

Thanks!
0
Brijesh ChauhanStaff IT EngineerCommented:
You are getting multiple rows for same agentID , that is why you are having multiple emails. What you need to do is GROUP your results ....

<cfoutput query="sendtoAgent" group="AgentID">

<CFMAIL
FROM="#email#"      
TO="#AgentEmail#"
SUBJECT="My Favs"      
Type="html">
<h2>Here is a list of my Favs...</h2>

<ul>
<li><a href="http://mysite/details.cfm?ListID=#ListID#">http://mysite/details.cfm?ListID=#ListID#</a></li>
</ul>

</cfmail>

</cfoutput>
0
Build an E-Commerce Site with Angular 5

Learn how to build an E-Commerce site with Angular 5, a JavaScript framework used by developers to build web, desktop, and mobile applications.

Brijesh ChauhanStaff IT EngineerCommented:
Try this, it should work..

-----------------------------

<cfoutput>
<cfloop query="sendtoAgent" group="AgentID">

<CFMAIL
FROM="#email#"      
TO="#AgentEmail#"
SUBJECT="My Favs"      
Type="html">
<h2>Here is a list of my Favs...</h2>

<cfloop>
<ul>
<li><a href="http://mysite/details.cfm?ListID=#sendtoAgent.ListID#">http://mysite/details.cfm?ListID=#sendtoAgent.ListID#</a></li>
</ul>
</cfloop>
</cfmail>
</cfloop>

</cfoutput>

-----------------------------------------------------------------------------

Here is a sample code that is working...


<cfset sendtoAgent = QueryNew('AgentId,ListID')>
<cfset queryAddRow(sendtoAgent,1)>
<cfset querySetCell(sendtoAgent,'AgentId',116)>
<cfset querySetCell(sendtoAgent,'ListID','A119356')>
<cfset queryAddRow(sendtoAgent,1)>
<cfset querySetCell(sendtoAgent,'AgentId',116)>
<cfset querySetCell(sendtoAgent,'ListID','A122783')>
<cfset queryAddRow(sendtoAgent,1)>
<cfset querySetCell(sendtoAgent,'AgentId',116)>
<cfset querySetCell(sendtoAgent,'ListID','B103100')>
<cfset queryAddRow(sendtoAgent,1)>
<cfset querySetCell(sendtoAgent,'AgentId',116)>
<cfset querySetCell(sendtoAgent,'ListID','B110249')>
<cfset queryAddRow(sendtoAgent,1)>
<cfset querySetCell(sendtoAgent,'AgentId',117)>
<cfset querySetCell(sendtoAgent,'ListID','B110250')>

<cfoutput>
<cfloop query="sendtoAgent" group="AgentId">
      <cfloop>
          #sendtoAgent.ListID# <br/>
    </cfloop>
</cfloop>
</cfoutput>

-----------------------------------------
- B
0
Bang-O-MaticAuthor Commented:
Hi brijeshchauhan,

I changed the code to group by agentID and it correctly sends one email, but only one of seven favs is populated in the email.
0
Bang-O-MaticAuthor Commented:
I just saw your code update, let me try that. Thanks
0
Brijesh ChauhanStaff IT EngineerCommented:
Try the second code I posted..which is below..this will send all the lisID's...

<cfoutput>
<cfloop query="sendtoAgent" group="AgentID">

<CFMAIL
FROM="#email#"      
TO="#AgentEmail#"
SUBJECT="My Favs"      
Type="html">
<h2>Here is a list of my Favs...</h2>

<cfloop>
<ul>
<li><a href="http://mysite/details.cfm?ListID=#sendtoAgent.ListID#">http://mysite/details.cfm?ListID=#sendtoAgent.ListID#</a></li>
</ul>
</cfloop>
</cfmail>
</cfloop>

</cfoutput>
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Bang-O-MaticAuthor Commented:
Problem SOLVED!
0
Brijesh ChauhanStaff IT EngineerCommented:
:-).. Happy to help you..
0
Bang-O-MaticAuthor Commented:
Pure genius! You got my project back on track and I appreciate it!
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
ColdFusion Language

From novice to tech pro — start learning today.