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!

Register Log in
  • Congratulations bkrike on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Only sending email to one person in database

Status
Not open for further replies.
cfmaster

cfmaster

Technical User
Aug 9, 2002
8
GB
Hi folks,

I have a property website which I am working on, when a new property is added I want it to email registered users new listings based on their registered property requirements. I have it working however, it only emails to one user at any given time. I want it to email all users who's preferences match. Heres my code:- Thanks in advance.

<cfinclude template="security_check.cfm">
select Max(rowid) from table;

<cflock name="insertrecord" timeout="10">

<cfif price EQ ""><cfset price = 0.00></cfif>
<cfif beds EQ ""><cfset beds = 0></cfif>
<cfif baths EQ ""><cfset baths = 0></cfif>

<cfif brochure NEQ "">
<cffile action="upload"
destination="#brochure_path#"
nameconflict="makeunique"
filefield="brochure">
</cfif>

<cfquery name="add_listing" datasource="#datasource#">
INSERT INTO listings(property_type,
property_num,
property_address,
property_area,
property_postalcode,
active,
price,
year_built,
beds,
baths,
acreage,
description,
features,
sqft,
realtor
<cfif brochure NEQ "">, brochure</cfif>
)
VALUES(
<cfqueryparam cfsqltype="cf_sql_varchar" value="#property_type#" />,
<cfqueryparam cfsqltype="cf_sql_varchar" value="#property_num#" />,
<cfqueryparam cfsqltype="cf_sql_varchar" value="#property_address#" />,
<cfqueryparam cfsqltype="cf_sql_varchar" value="#property_area#" />,
<cfqueryparam cfsqltype="cf_sql_varchar" value="#property_postalcode#" />,
<cfqueryparam cfsqltype="cf_sql_varchar" value="#active#" />,
<cfqueryparam cfsqltype="cf_sql_integer" value="#price#" />,
<cfqueryparam cfsqltype="cf_sql_varchar" value="#year_built#" />,
<cfqueryparam cfsqltype="cf_sql_integer" value="#beds#" />,
<cfqueryparam cfsqltype="cf_sql_integer" value="#baths#" />,
<cfqueryparam cfsqltype="cf_sql_varchar" value="#acreage#" />,
<cfqueryparam cfsqltype="cf_sql_varchar" value="#description#" />,
<cfqueryparam cfsqltype="cf_sql_varchar" value="#features#" />,
<cfqueryparam cfsqltype="cf_sql_varchar" value="#sqft#" />,
<cfqueryparam cfsqltype="cf_sql_integer" value="#realtor#" />
<cfif brochure NEQ "">, <cfqueryparam cfsqltype="cf_sql_varchar" value="#file.ServerFile#" /></cfif>
)
</cfquery>


<cfquery name="getnewid" datasource="#datasource#">
SELECT MAX(id) AS newid
FROM listings
</cfquery>


<!--- START EMAILING USERS --->

<cfquery name="prefs" datasource="#datasource#">
SELECT *
FROM user_property_prefs
WHERE email_me = <cfqueryparam cfsqltype="cf_sql_varchar" value="1" />
</cfquery>

<cfloop query="prefs">

<cfquery name="get_name" datasource="#datasource#">
SELECT bill_to_first_name, bill_to_surname, clientid, email
FROM customers
WHERE email_me = <cfqueryparam cfsqltype="cf_sql_varchar" value="1" />
</cfquery>

<!--- COMPARES EXISTING listings AGAINST A PARTICULAR USER'S PREFERENCES --->

<cfquery name="get_listings" datasource="#datasource#">
SELECT *
FROM listings
WHERE id = <cfqueryparam cfsqltype="cf_sql_integer" value="#getnewid.newid#" />
<cfif prefs.min_price NEQ "0" AND prefs.max_price NEQ "0">AND price BETWEEN <cfqueryparam cfsqltype="cf_sql_integer" value="#prefs.min_price#" /> AND <cfqueryparam cfsqltype="cf_sql_integer" value="#prefs.max_price#" /></cfif>
<cfif prefs.beds NEQ "0">AND beds >= <cfqueryparam cfsqltype="cf_sql_integer" value="#prefs.beds#" /></cfif>
AND active = <cfqueryparam cfsqltype="cf_sql_varchar" value="1">
</cfquery>


<cfif get_listings.RECORDCOUNT GT 0>



<cfmail to="#get_name.bill_to_first_name# #get_name.bill_to_surname# <#get_name.email#>" from="#admin_email#" subject="Newly listed property matching your preferences" type="html">
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" " <html xmlns=" <head>
<meta http-equiv="Content-Type" content="text/html; charset=iso-8859-1" />
<title>New Property Added</title>
<style type="text/css">
<!--
.style1 {
font-family: Tahoma, Verdana;
font-size: small;
}
.style3 {font-family: Tahoma, Verdana; font-size: small; font-weight: bold; }
-->
</style>
</head>
<body>
<p class="style1">Dear #get_name.bill_to_first_name#,</p>
<p class="style1">A new property has been added, which might match your criteria. Please <a href=" here</a> to view this property.</p>
<p class="style1"></p>
<p class="style3">#get_listings.property_address#<br />
#get_listings.property_area#</p>

</body>
</html>
</cfmail>
</cfif>


</cfloop>

</cflock>

<!--- STOP EMAILING USERS --->


<cflocation addtoken="no" url="list_properties.cfm?insertsuccessful=yes">
 
Sort by date Sort by votes
You need to specify which query to use in your cfmail tag, or you put it inside a query loop. By just giving it "get_name.bill_to_first_name", it doesn't know that you want to use all of the records, so it only uses the first one returned from your query.


Hope This Helps!

ECAR
ECAR Technologies

"My work is a game, a very serious game." - M.C. Escher
 
Upvote 0 Downvote
How would I do this, can you give me an example please?
 
Upvote 0 Downvote
Using a query:
Code: 
<cfmail query="get_name"
        to="#bill_to_first_name# #bill_to_surname# <#email#>"
        from="#admin_email#"
        subject="Newly listed property matching your preferences"
        type="HTML">
		...mail content goes here...
		</cfmail>
Using a Loop
Code: 
<cfloop query="get_name">
	<cfmail to="#bill_to_first_name# #bill_to_surname# <#email#>"
	        from="#admin_email#"
	        subject="Newly listed property matching your preferences"
	        type="HTML">
	...mail content goes here...
	</cfmail>
</cfloop>

Hope This Helps!

ECAR
ECAR Technologies

"My work is a game, a very serious game." - M.C. Escher
 
Upvote 0 Downvote
I already have a loop around the cfmail tag - it is
<cfloop query="prefs">

Can I put a cfloop within a cfloop?
 
Upvote 0 Downvote
you have this:

<cfquery prefs>
<cfloop prefs>
<cfquery get_name>
<cfquery get_listings>
<cfmail get_names>
</cfloop>

so... how are you getting past the first record in get_names?

We've heard that a million monkeys at a million keyboards could produce the complete works of Shakespeare; now, thanks to the Internet, we know that is not true.
 
Upvote 0 Downvote
You can have as many loops inside loops as you want.


Hope This Helps!

ECAR
ECAR Technologies

"My work is a game, a very serious game." - M.C. Escher
 
Upvote 0 Downvote
Status
Not open for further replies.

Similar threads

HansKok
  • Locked
  • Question
Error after only changing text and not codes
Replies
0
Views
585
HansKok
HansKok
ElizaShteto
  • Locked
  • Question
Split One Page in two separate Pages in Framemaker 11
Replies
0
Views
196
ElizaShteto
ElizaShteto
Mike Lewis
  • Locked
  • Question
Using Dreamweaver to maintain a site not created in Dreamweaver
Replies
2
Views
746
Mike Lewis
Mike Lewis
oshinwa
  • Locked
  • Question
Which Call collector should I use and which one is compatible with my Ericsson BP250?
Replies
0
Views
649
oshinwa
oshinwa
RosieGp
  • Locked
  • Question
How to read string of 0s and 1s 1
Replies
3
Views
770
LyndonOHRC
LyndonOHRC

Part and Inventory Search

Sponsor

Back
Top