Displaying many records within CFMail

3dColor · Feb 1, 2006

I am creating an alert feature for my visitors so they can signup for an alert when a used car comes available within their metro area.

Once a morning I would schedule a cfm page to run that would send out all the alerts for those who signed up for alerts for cars that were entered the previous day.

My question is what happens let's say if there were 10 new car listings from the day before for the same metro area?

I would like to place all of those new listings within one email for the person who signed up for the alert instead of sending 10 individual emails to one person.

If I query my listings table for yesterday's new cars and JOIN it with the Alerts table where the metro area is equal can I loop the 10 new listings within the CFMail?

Can you place a loop within a CFMail tag?

Code:

<cfquery name="GetNewAds" datasource="#DSN#">
  SELECT *
  FROM 	Listing INNER JOIN Alerts ON Listing.metroFk = Alerts.metroFk 
  WHERE	beginDate = #yesterday#
</cfquery>

<CFLOOP QUERY="GetNewAds">
	
  <CFMAIL To="#GetNewAds.email#"
       From="<support@example.com>"
       Subject="Notification of new ad(s)"
       server="mail.example.com">
Ads that were posted yesterday for the #GetNewAds.metroFk# metro area:

<-- how do I loop the results for the 10 new cars here? -->
#GetNewAds.carType#, #GetNewAds.address#, #GetNewAds.carCost#

Thanks

	</CFMAIL>
</CFLOOP>

marcovdo · Feb 2, 2006

Hi 3dcolor,

I have a example for you included:

Code:

<cfparam name="PageNum_recent" default="1">
<cfquery name="recent" datasource="spirilog">
SELECT *
FROM onderwerpen
WHERE datum = '#DateFormat(Now())#' ORDER BY id DESC
</cfquery>
<cfquery name="emailadressen" datasource="spirilog">
SELECT email
FROM inloggen
WHERE autoemail = 'Y' and taalid = 'ned'
</cfquery>
<cfquery name="artikelen" datasource="spirilog">
SELECT *
FROM onderwerpen
WHERE datum = '#DateFormat(Now())#' ORDER BY id DESC</cfquery>
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN" "[URL unfurl="true"]http://www.w3.org/TR/html4/loose.dtd">[/URL]

<html>
<head>
<meta http-equiv="Content-Type" content="text/html; charset=iso-8859-1">
<title>Aquariusage.coM</title>
<link href="../styles/style.css" rel="stylesheet" type="text/css">
<script language=VBScript>
Dim Bar, Line, SP
Bar = 0  
Line = ">"
SP = 100

Function Window_onLoad()
  Bar = 95
  SP = 10
End Function

Function Count()
  If Bar < 100 Then
    Bar = Bar + 1
    Window.Status = "Aquariusage.coM " & Bar & "%" &  " " & String(Bar, Line)
    setTimeout "Count()", SP
  Else
    Window.Status = "Aquariusage.coM"
    Document.Body.Style.Display = ""
  End If    
End Function

Call Count()
</script>
</head>
<body>
<cfmail query="emailadressen" type="html" from="INFO@AQUARIUSAGE.COM" to="#emailadressen.email#" bcc="" subject="Bewustzijn van Aquariusage.coM #DateFormat(Now())#" server="smtp.aquariusage.com">
 <a href="[URL unfurl="true"]http://www.aquariusage.com"><img[/URL] src="[URL unfurl="true"]http://www.aquariusage.com/images/png/emailonder.png"[/URL] alt="Aquariusage.coM - Nieuwsartikelen voor meer Bewustzijn !" name="pic" width="100%" height="2" border="0">
  <img src="[URL unfurl="true"]http://www.aquariusage.com/images/flashintromail.gif"[/URL] alt="Aquariusage.coM - Nieuwsartikelen voor meer Bewustzijn !" width="100%" height="70" border="0">
  <img src="[URL unfurl="true"]http://www.aquariusage.com/images/transparentSquare.gif"[/URL] alt="" name="pic" width="100%" height="2" border="0">
  <img src="[URL unfurl="true"]http://www.aquariusage.com/images/png/titel.gif"[/URL] alt="Aquariusage.coM - Nieuwsartikelen voor meer Bewustzijn !" name="pic" border="0"></a>
 <h5>BEWUSTZIJN VAN VANDAAG...</h5>
 <cfloop query="recent">
 <h5><a href="[URL unfurl="true"]http://www.aquariusage.com/pages/frameset.cfm?artikel.cfm?id=#recent.Id#&amp;spot=#recent.echtenaam#&;video=#recent.video#"[/URL] 
target="mainframe" 
title="Lees #recent.titel#

#Left(recent.tekst,160)#..." border="0"><img src="[URL unfurl="true"]http://www.aquariusage.com/images/transparentSquare.gif"[/URL] width="2" height="2" border="0">#recent.titel#</a></h5>
</cfloop>
<cfloop query="artikelen">
<cfquery name="aantal" datasource="spirilog">SELECT * FROM pageviews WHERE onderwerpid = #artikelen.id#</cfquery>
<cfquery name="hoofdtitel" datasource="spirilog">SELECT * FROM hoodcategorien WHERE titel = '#artikelen.hoofdcatid#'</cfquery>

<img name="pic" src="[URL unfurl="true"]http://www.aquariusage.com/images/png/emailonder.png"[/URL] width="100%" height="2" alt="">
<cfif artikelen.RecordCount GT 0>
  <h5>GEPLAATST OP #ARTIKELEN.DATUM# OM #ARTIKELEN.TIJD# * #AANTAL.RECORDCOUNT# HITS * GROEP: #HOOFDTITEL.TITELVERTAALD# *</h5>
  #ARTIKELEN.TITEL# <br>
      <br>
  <cfif #artikelen.foto# is not 'geenfoto.gif'><table width="1%"  border="0" align="left">
          <tr>
            <td><a href="[URL unfurl="true"]http://www.aquariusage.com/pages/frameset.cfm?artikel.cfm?id=#artikelen.id#&spot=#artikelen.echtenaam#&video=#artikelen.video#"><img[/URL] name="pic" src="[URL unfurl="true"]http://www.aquariusage.com/pages/#artikelen.foto#"[/URL] width="40" height="40" border="0" alt="Klik hier om #artikelen.titel# te lezen"></a></td>
          </tr>
  </table></cfif>
  #Left(artikelen.tekst,800)#... <a href="[URL unfurl="true"]http://www.aquariusage.com/pages/frameset.cfm?artikel.cfm?id=#artikelen.id#&spot=#artikelen.echtenaam#&video=#artikelen.video#">>>></a>[/URL]
      <br>
      <br>
   #ARTIKELEN.BRON#
</cfif>
<cfif artikelen.RecordCount EQ 0>
    <h5>SORRY ER ZIJN VANDAAG GEEN NIEUWE ARTIKELEN</h5>
    Heb je ergens een interessant verhaal gelezen of gehoord? Deel het met de rest van de bezoekers! <a href="[URL unfurl="true"]http://www.aquariusage.com/pages/frameset.cfm?subcatagorietoevoegen.cfm">Plaats[/URL] jouw eigen artikel...</a>
  </cfif>
</cfloop><br><br><br>
Vriendelijke groeten,
<br>
Het team van <a href="[URL unfurl="true"]http://www.aquariusage.com">Aquariusage.coM</a>[/URL]
<br><br>
NOTE
<br><br>
<a href="[URL unfurl="true"]http://www.aquariusage.com/pages/afmeldenauto.cfm?status=#emailadressen.email#">Verwijder[/URL] mij uit de E-mail lijst</a>
<br><br>
<a href="[URL unfurl="true"]http://www.aquariusage.com/pages/frameset.cfm?subcatagorietoevoegen.cfm">Zelf[/URL] bewustzijn invoeren</a><br><br>
<a href="[URL unfurl="true"]http://www.aquariusage.com/pages/frameset.cfm?mailen.cfm">Mail[/URL] ons</a><br><br>
<img name="pic" src="[URL unfurl="true"]http://www.aquariusage.com/images/png/emailonder.png"[/URL] width="100%" height="2" alt="">
</cfmail>
<cflocation url="cfmmailautoeng.cfm">


<!-- Start of StatCounter Code -->
<script type="text/javascript" language="javascript">
var sc_project=1039689; 
var sc_invisible=1; 
var sc_partition=9; 
var sc_security="5e496495"; 
</script>

<script type="text/javascript" language="javascript" src="[URL unfurl="true"]http://www.statcounter.com/counter/frames.js"></script><noscript><a[/URL] href="[URL unfurl="true"]http://www.statcounter.com/"[/URL] target="_blank"><img  src="[URL unfurl="true"]http://c10.statcounter.com/counter.php?sc_project=1039689&amp;amp;java=0&amp;security=5e496495&amp;invisible=1"[/URL] alt="free hit counter script" border="0"></a> </noscript>
<!-- End of StatCounter Code --></body>
</html>

Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

Displaying many records within CFMail

3dColor

Programmer

marcovdo

Programmer

Similar threads

Part and Inventory Search

Sponsor