One of the problems with adding information to a database is trying not to duplicate existing entries. This is especially true of opt-in mailing lists. There is nothing worse than sending duplicate emails to your opt-in mailing list clients. More often than naught it will be the fault of the end-user who forgot they already signed up, and then they go ahead and signup again. The unfortunate thing is they will blame you for multiple emails and possibly you will lose them as one of your subscribers. With this tutorial what we will do is create an application that looks at their entry and checks the database to see if the information already exists. If their information is already in the database, then the application will tell them and not allow a duplicate entry. While this tutorial uses an opt-in mailing list as an example, this method can be applied to any duplicate data checking you want to do. This tutorial is made up of three parts each part being separated by ==========.

First off we need a database to work with for this exercise we will use the following mySql database, I have added some data from my childhood hero's just so we don't pull errors when we test out our application! The database name is EasyCfm, you will need this for your DSN if you are going to test this application out on your system.

CREATE TABLE tutorials (
    id int(10) unsigned NOT NULL auto_increment,
    fname varchar(255) NOT NULL,
    lname varchar(255) NOT NULL,
    email varchar(255) NOT NULL,
    PRIMARY KEY (id)
);

#
# Dumping data for table 'tutorials'
#

INSERT INTO tutorials VALUES ( '1', 'Bugs', 'Bunny', 'bugs@bunny.com');
INSERT INTO tutorials VALUES ( '2', 'Road', 'Runner', 'road@runner.com');
INSERT INTO tutorials VALUES ( '3', 'Daffy', 'Duck', 'daffy@duck.com');

===============================================================

Now for the second part we need our form to input data into. Of course this form could have as many fields as you feel is necessary but for the purposes of this exercise I have only included the three most important fields. Also for formatting purposes I have enclosed the form in a table. Our form action value will be the next template named "dup_check_2.cfm" which is the template we will do our duplicate checking in. There is really nothing special about this form other than the field names correspond to the database column names.

<form action="dup_check_2.cfm" method="post">
<table width="400" border="1" align="center">
  <tr align="center">
     <td colspan="2">
Add Yourself To Our Mailing List</td>
  </tr>
  <tr>
     <td width="191" align="right">
First Name: </td>
     <td width="193">
<input name="fname" type="text" id="fname"></td>
  </tr>
  <tr>
     <td align="right">
Last Name: </td>
     <td>
<input name="lname" type="text" id="lname"></td>
  </tr>
  <tr>
     <td align="right">
Email Address: </td>
     <td>
<input name="email" type="text" id="email"></td>
  </tr>
  <tr align="center">
     <td colspan="2">
<input type="submit" name="Submit" value="Add Me to Mailing List"></td>
  </tr>
</table>

</form>

===============================================================

Now this is where the fun begins as we check for duplicate email addresses in our database. In this example I am only checking for duplicate email addresses as I am assuming that perhaps an end-user might like his newsletter delivered to two or more places, but would only like one copy delivered to each email address!



<!--- Query's Where statement is Checking to see 
if the email address already exists in the database --->

<cfquery name="DupCheck" datasource="EasyCfm">
    SELECT email
    FROM tutorials
    WHERE email = '#Form.email#'
</cfquery>

<!--- If our query shows a email match than using
recordCount we can tell the user that his email 
address already exists in our database --->

<cfif DupCheck.recordCount GT "0">
   You are already Listed in our Mailing List!
   <!--- If our recordCount is not greater than "0" 
    we continue on and process the new email address --->

<cfelse>
<!--- Insert Data (preferably after cleaning it) 
see my "advanced form checking tutorial" if you 
are not sure how to approach this task--->

<cfquery name="DupCheck" datasource="EasyCfm">
INSERT INTO tutorials
                  ( 
                    fname, 
                    lname,
                    email
                  )
                  VALUES (
                    '#Form.fname#',
                    '#Form.lname#',
                    '#Form.email#'
                  )
</cfquery>

<html>
<head>
   <title>Tell User he is now in the Database</title>
   <meta http-equiv=
"Content-Type" content="text/html; charset=iso-8859-1">
</head>

<body>

<!--- It is good practice to let the user know his information has
been successfully entered and also to echo back what they have submitted
to the database. This is easy enough to do as all we do is echo the
form values here --->

<cfoutput>
 
Thank You #Form.fname# #Form.lname# your email address: #Form.email# has now been entered into our database!
</cfoutput>

</body>
</html>


</cfif>

That?s basically it; you can of course check multiple form values being passed to your database. I have used this technique quite successfully with various types of user input and with only minor changes to this script it all works fine and is easy to implement.

About This Tutorial
Author: Redmanz
Skill Level: Intermediate 
 
 
 
Platforms Tested: CF5
Total Views: 135,390
Submission Date: December 09, 2002
Last Update Date: June 05, 2009
All Tutorials By This Autor: 6
Discuss This Tutorial
  • First: Great tutorial! I got it to work fine with one field, but you mentioned that this could be used to check more than one and I was wondering how. Here is the code snipet I used: SELECT email, username FROM tblRegistration WHERE email = '#Form.email#' and username = '#Form.username#' That email address is already in use. It works fine for the email, but not the username. I am sure it is something simple, but it is beyond me right now! Thanks!

Advertisement

Sponsored By...
Mobile App Development (IOS, Android, Cordova, Phonegap, Objective-C, Java) - Austin, Texas Mobile Apps - Touch512, LLC.