Welcome to the second part of my series on how to create a simple mailing list in ColdFusion. Be sure to read the first entry in the series before starting this one. As previously mentioned, the goal of this application is to create a simple way for users to sign up at your web site. An administrator can then use a tool to send an email to folks who have signed up. Today's entry will deal with the administrator a bit. Now I'm going to cheat a bit. I don't want to spend a lot of time on security and all that, so I'm going to write a script and place it in the same folder as my other files. Obviously in a real world application this file would be placed in a protected folder. The specific item to add to our application today is a simple interface to list the subscribers and add/delete folks. Later in the series I'll discuss how folks can delete themselves, but the honest truth is that even if you provide such a method, folks will still email you (or call you) and demand that you remove them. So lets work on a tool that will make that simple.
The following script will handle listing subscribers, removing subscribers, as well as adding them:
<cfif structKeyExists(url, "delete")>
<cfset application.maillist.unsubscribe(url.delete)>
</cfif>
<cfif structKeyExists(form, "add") and len(trim(form.email)) and isValid("email", form.email)> <cfset application.maillist.subscribe(form.email)> </cfif>
<cfset members = application.maillist.getMembers()>
<cfoutput> <p> Your mail list has <cfif members.recordCount is 0> no members <cfelseif members.recordCount is 1> 1 member <cfelse> #members.recordCount# members </cfif>. You may use the table below to remove any member, or the form to add a new member. </p> </cfoutput>
<cfif members.recordCount gte 1>
<p> <table border="1"> <tr> <th>Email Address</th> <td> </td> </tr> <cfloop query="members"> <tr <cfif currentRow mod 2>bgcolor="yellow"</cfif>> <cfoutput> <td>#email#</td> <td><a href="listmembers.cfm?delete=#token#">Delete</a></td> </cfoutput> </tr> </cfloop> </table> </p>
</cfif>
<form action="listmembers.cfm" method="post"> <input type="text" name="email"> <input type="submit" name="add" value="Add Subscriber"> </form>
There is a lot going on here, so let's handle it line by line. At the top of the script I have two checks. The first is for list removals. I check for the value, url.delete, and if it exists, I call out to my CFC to unsubscribe the user. I'm using the token instead of the email address. (You will see this later in the script.) The reason for this is that the token serves as a good primary key for the table. Sure, I know the email addresses are unique, but I'm also going to use something similar for the front end. Therefore, I just pass the token to the method.
Adding a subscriber is also rather simple. I look for the submit button (named "add") and check to see if the email address is valid. Because this is the admin I do less hand holding. I'm not going to display an error if the email address isn't valid. Obviously you can change this in your own code. I tend to be a bit cruel in my own administrator tools.
The next section of the script gets the members from the mailing list and displays a simple count of members along with a nicely designed table. (Yes, the nicely designed part is a joke.) I had mentioned above that I use the token for deletions. Now you see where this comes from. Each delete link passes it back to the script.
Last but not least, I added a simple form with one field and a button. This lets the administrator quickly add email address to the mail list.
Alright, now that I showed you the front end, let's look at the new version of the CFC:
<cfcomponent displayName="MailList" output="false">
<cffunction name="init" returnType="maillist" output="false" access="public">
<cfargument name="dsn" type="string" required="true">
<cfset variables.dsn = arguments.dsn>
<cfreturn this>
</cffunction>
<cffunction name="getMembers" returnType="query" output="false" access="public"
hint="Returns a query of everyone subscribed.">
<cfset var q = "">
<cfquery name="q" datasource="#variables.dsn#">
select email, token
from subscribers
order by email asc
</cfquery>
<cfreturn q>
</cffunction>
<cffunction name="subscribe" returnType="boolean" output="false" access="public"
hint="Adds a user to the mailinst list, if and only if the person wasn't already on the list.">
<cfargument name="email" type="string" required="true">
<cfset var checkIt = "">
<cfif not isValid("email", arguments.email)>
<cfthrow message="#arguments.email# is not a valid email address.">
</cfif>
<!--- only add if the user doesn't already exist. --->
<cflock name="maillist" type="exclusive" timeout="30">
<cfquery name="checkIt" datasource="#variables.dsn#">
select email
from subscribers
where email = <cfqueryparam cfsqltype="cf_sql_varchar" value="#arguments.email#">
</cfquery>
<cfif checkIt.recordCount is 0>
<cfquery datasource="#variables.dsn#">
insert into subscribers(email,token)
values(<cfqueryparam cfsqltype="cf_sql_varchar" value="#arguments.email#">,<cfqueryparam cfsqltype="cf_sql_varchar" value="#createUUID()#">)
</cfquery>
<cfreturn true>
<cfelse>
<cfreturn false>
</cfif>
</cflock>
</cffunction>
<cffunction name="unsubscribe" returnType="void" output="false" access="public"
hint="Removes a user to the mailinst list.">
<cfargument name="token" type="uuid" required="true">
<cfquery datasource="#variables.dsn#">
delete from subscribers
where token = <cfqueryparam cfsqltype="cf_sql_varchar" value="#arguments.token#">
</cfquery>
</cffunction>
</cfcomponent>
Let's focus on the changes from last time. There are two new methods, getMembers and unsubscribe. Both are rather simple, so I won't say a lot about them. If you have questions though, please add a comment.
Thats it for this entry. As a general FYI, I may not be able to write part three till Friday. I've got a presentation tomorrow night (are you coming?) and Thursday is packed. Also, I made a small tweak to the Application.cfc file. I added a small hook to let me reinit the application using a URL variable. It's in there if you want to take a peak, but isn't anything special. As before, I've attached a zip to this entry so you can download the code and look for yourself. The next entry will add the mailer to the application. (The whole point of the series!)
Archived Comments
Ray,
The download link to the zip isn't showing.
Thanks man. Added.
Hi and thanks for this tutorial. It is the first time I have worked with cfc files. Clearly a steep learning curve for me as i am stuck in CF5 tags!
Anyway all of your tutorial makes sense and works at my end except for when the subscriber them self (i.e not via the mailing list admin page/s) tries to unsubscribe
using your template TEMPUnsubscribe.cfm included in your archive file causes the problem. You have not included a way for the user to pass the token accros in teh URL query string.
If the user enters his email address blah@blah.net
the result sent to unsubscribe.cfm will be:
unsubscribe.cfm?emailaddress=ulric@uk2.net&unsubscribe=Unsubscribe
and this will cause the database deletion to fail and we get the result "Sorry, but you were not unsubscribed. Please ensure that you have copied the URL correctly from your mail client.".
Would it be possible for you to update the unsubscribe pages so that token is included somehow. I cant figure it out :(
many thanks
Sorry Ray! I did not figure out all i needed was unsubscribe.cfm?token=%token% for user self service unsubscribe to work, and that I should ignore template TEMPunsubscribe.cfm.
Major apologies!
Ulric
Glad you got it - way behind on email today.
Ray,
One thing I noticed is the behavior of the app if someone is already subscribed. If the email address already exists, the visitor gets the same message about thank you for registering. Is it possible to tell the visitor that he/she is already registered?
To enable the unsubscribe, what change do I need to make, after reading this post?
Comment 4 written by ulric on 13 March 2009, at 3:06 PM
Sorry Ray! I did not figure out all i needed was unsubscribe.cfm?token=%token% for user self service unsubscribe to work, and that I should ignore template TEMPunsubscribe.cfm.
Major apologies!
@Sean- So... your good? Or is comment 6 still an open question?
Yes, for Comment 6. When I subscribe, if my email is already in the database, I get the thank you message, but no email is sent. It would be better to tell the visitor that their email is already in the database and to click on that link in the email that was sent out so that their email can be verified. Isn't that how it should work? Something like that?
You would modify the code to use the return value. If it is false, it means you were already subscribed.
I want to use your email list from now on, but I already have a database with hundreds of emails. I added columns for 'token' and 'verified' and updated the 'verified' values to '1'. Is there a way I can update the 'token' values where they are NULL using createUUID, all in one query? Something like this?
<cfquery datasource="maillist">
UPDATE subscribers
SET token = <cfqueryparam cfsqltype="cf_sql_varchar" value="#createUUID()#">
WHERE token IS NULL
</cfquery>
I tried this as a test
<cfquery datasource="maillist">
UPDATE subscribers
SET token = <cfqueryparam cfsqltype="cf_sql_varchar" value="#createUUID()#">
WHERE id > 14
</cfquery>
and it did create UUIDs, but they were all the same. If this is at all possible, it probably involves some kind of LOOP, doesn't it?
Just use one query to get all the subscribers where token is null, then loop over that query and do an update on each. Yes, that means you have N+1 queries. Yes, I bet SQL has a "cooler" way of doing it. But who cares? ;) This will work and it's a one time mod. :)
Thank you, Ray. I got it to work. Very cool. All unique UUIDs. createUUID() works great! I never heard of it until I came across your series. Thank you!