Build an early sign-up mailing list system

Note: This article has been marked for a quality review and will soon be updated.

When you’ve got a great idea for a product its often hard to resist buying up the domain name for it, but what do you put there? A coming soon logo? A paragraph about what you’re making? How about allowing visitors to sign-up to hear updates when the products becomes public? The last point is the most useful because not only does it let you keep track of early interest, it ensures your visitors don’t go getting all excited and then forget about the product when it launches a couple of months down the line. So our system will allow users to pop their email address into a form, and will then insert it into a database and send an email to the new subscriber. We’ll look at how to build in admin notification, as well as having a system in place that requires users to confirm their email address before they are added to the list. We’ll be making a script that we can use on multiple sites with little alteration so if, like me, you have a number of projects awaiting public release you will be able to stick it on all your different servers with relative ease. Let’s get started.

First up we will do the simple task of creating our database. Depending on the features you want, you may want to alter the structure a little, but in our case we will build in support for users to enter their name, email, and to have to confirm their subscription. So for that we will need the fields: ID – our primary key, name – if we want to collect the subscriber’s name, email, confirmed – if they have verified their email address, site – which mailing list they subscribed to, created – when did they subscribe? So our process should look like this: User Enters Email -> Added to Database & Verification Email Sent -> User Confirms -> Database Updated. So go ahead and add that table to your database, I called my database ‘mailing_lists’, with my table named ‘users’. Here is the SQL to create the table:

CREATE TABLE `users1` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(150) NOT NULL,
  `email` varchar(200) NOT NULL,
  `confirmed` tinyint(4) NOT NULL DEFAULT '0',
  `site` varchar(150) NOT NULL,
  `created` varchar(10) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=MyISAM AUTO_INCREMENT=2 DEFAULT CHARSET=latin1;

Now we’ll build the main PHP script list.php that will handle all the heavy lifting. To begin we’ll define some important variables, and create a connection to our database, here’s what that looks like:

define(REQUIRES_NAME, false);
define(NOTIFY_OWNER, true);
define(OWNER_EMAIL, 'YOUR EMAIL HERE');
define(SITE_NAME, 'SITE NAME HERE');

define(DB_USER, "USERNAME");
define(DB_PASS, "PASSWORD");
define(DB_HOST, "DB HOST");
define(DB_NAME, "DB NAME");

$db = new mysqli(DB_HOST, DB_USER, DB_PASS, DB_NAME);

We’re using MySQLi for a speed increase and to allow us to use Object Orientated Programming. Now we will create the function that will allow us to insert new subscribers to the database, we’ll call it addToList().

function addToList($email, $name = ''){
	if($name=='' && REQUIRES_NAME){
		// Name required and not provided
		echo 'Invalid name';
	}else {
		$email = strtolower($email);
		global $db;
		
		$cur = $db->query("SELECT * FROM users WHERE email = '".$email."' && site = '".SITE_NAME."'");

		if($cur->num_rows > 0){
			echo 'Duplicate entry detected';
			return false;
		}else {
			$db->query("INSERT INTO users(name, email, confirmed, site, created) VALUES('$name', '$email', 0,'".SITE_NAME."', '".time()."')") or die('Error inserting into database');
			return true;
		}		
	}
}

Let’s see what that function does. First up we check if we should be collecting the user’s name, if we are and it isn’t there, we stop the script and send out a warning. Otherwise we set up the $email variable and make sure we have access to our $db variable. The next part makes queries the database to make sure the user hasn’t already subscribed to the list, if they have we tell them that a duplicate entry was detected, and if not we insert their data into our table. Great stuff, we’ve done the main part of our script. But if we want the user to be able to confirm their email address, and even send an email to the admin, we’ll need to write a few more functions. And to make sure we don’t go writing duplicate code, we will create our own function for sending mail that will in turn call PHP’s mail() function. Here’s what it looks like:

function list_mail($to, $subject, $message){
	if(!empty($to) && !empty($subject) && !empty($message)){
		$headers  = 'MIME-Version: 1.0' . "\r\n";
		$headers .= 'Content-type: text/html; charset=iso-8859-1' . "\r\n";	
		$headers .= 'From: noreply@'. SITE_NAME . "\r\n";		

		mail($to, $subject, $message, $headers);
	}else {
		echo 'Bad parameters passed to list_mail()';
	}
}

It’s a pretty basic function that checks that the parameters of $to, $subject, and $message aren’t empty, and then sends a HTML email if everything checks out. Otherwise we echo out a handy error.

The next two functions we will write are the two that handle the content of the emails sent to our users and our admin, so you’ll need to alter them accordingly.

function sendConfrimMail($email, $name = ''){
	$subject = 'Subscription Confirmation';
	$message = '<html>
			<head><title>Subscription Confirmation</title></head>
			<body>
				<center><p style="margin-top:20px;">You have successfully subscribed to updates from '.SITE_NAME.', thank you for signing up to be the first to receive information about our exciting new project, we look forward to providing you with something amazing in the near future.


                                <p>But before you can receive updates you need to confirm your subscription by visiting: http://'.SITE_NAME.'/confirm.php?e='.$email.'&t='.md5($email.SITE_NAME).'</p>
				
				<p>-The Management</p></center>
			</body>
		</html>';
	
	list_mail($email, $subject, $message);
}

function notifyOwner(){
	global $db;
	$subCount = $db->query("SELECT COUNT(*) FROM users WHERE site = '".SITE_NAME."'");
	$subCount = $subCount->fetch_array();
	$subCount = $subCount[0];

	$subject = 'New '.SITE_NAME.' Subscription!';
	$message = '<html>
			<head><title>New '.SITE_NAME.' Subscription!</title></head>
			<body>
				<center><p style="margin-top:20px;">A new subscription has just been submitted to the site! We currently have '.$subCount.' subscribers!</p>
				
				<p>Let\'s make em happy!</p></center>
			</body>
		</html>';

	list_mail(OWNER_EMAIL, $subject, $message);
}

Now those functions both do essentially the same thing, they just send messages to different people. And as a handy addition the notifyOwner() function also includes the subscriber count in the message. Feel free to go made coding up a beautiful HTML email and playing around with the content at this point.

You should notice that in the confirmation email we included a link that links to the site name we provided /confirm.php, and passes the variables ‘e’ and ‘t’ (not the friendly alien) along with it. The ‘t’ variable is the encrypted version of the user’s email, plus our site name as a salt. This is something we can easily replicate when we build confirm.php a little later.

So where are we at? Well we have a bunch of great functions that we can port to different sites with relatively little hassle, but, as the more astute among you may have noticed, none of them are ever called! So we’re going to have to write 10 more lines of code to make it happen! And it just so happens I’ve already done that :).

if(filter_var($_POST['email'], FILTER_VALIDATE_EMAIL)){
	if(addToList($_POST['email'], $_POST['name'])){
		sendConfrimMail($_POST['email'], $_POST['name']);
		if(NOTIFY_OWNER){
			notifyOwner();
		}
		echo 'Success';
	}
}else {
	echo 'Invalid email address';
}

So this code first validates the email address passed to us using the filter_var() function that works it’s magic by using a complex regular expression for validation – this saves us having to write one ourselves. Next we call our addToList() function from within an if statement. This ensures that the following code only executes if all goes well, and because the function also handles the error output we don’t need a matching else for that statement. If all does go well, we then send the confirmation email to our user, check if we need to notify the list’s owner/admin, and then echo ‘Success’. Otherwise we tell the world that the email provided wasn’t valid. Excellent. Now we only have 2 more parts to add; that’s the form itself, with which we will use some jQuery to handle our error, and the confirmation page. So let’s start with the server-side.

Here is the code for confirm.php:

if(!empty($_GET['e']) && !empty($_GET['t'])){
	define(SITE_NAME, 'SITE NAME');
	
	define(DB_USER, "USERNAME");
	define(DB_PASS, "PASSWORD");
	define(DB_HOST, "DB HOST");
	define(DB_NAME, "DB NAME");
	
	$db = new mysqli(DB_HOST, DB_USER, DB_PASS, DB_NAME);

	$email = $db->real_escape_string($_GET['e']);

	$result = $db->query("SELECT * FROM users WHERE email = '$email' AND site = '".SITE_NAME."'");
	
	if($row = $result->num_rows > 0){
		if($row['confirmed']=='0'){
			if($_GET['t']==md5($_GET['e'].SITE_NAME)){
				$db->query("UPDATE users SET confirmed = 1 WHERE email = '$email' AND site = '".SITE_NAME."'");
				echo 'Subscription confirmed!';
			}else {
				echo 'Invalid Token!';
			}
		}else {
			echo 'Email already confirmed!';
		}
	}else {
		echo 'No record found.';
	}

}else {
	echo 'Invalid URL!';
}

So here we check to make sure we have both the user’s email, and their confirmation token, and then connect to our database. We then create a variable called $email – a sanitised version of the email addressed passed to us in the URL. However we still use the one provided to us later – this is because a user might have a special character in their email address, and may not be a hacker, so if our function returns something slightly different it will alter the confirmation token. We then make sure that the record exists, and that it hasn’t yet been confirmed. If it hasn’t we test the confirmation token and update the database. Notice the 5 echo statements to keep the user updated about what’s going on. And that’s it for PHP for today. Let’s build our snazzy form.

Now our script expects only the email address of any subscriber, but we can optionally also pass it the name of a new subscriber. In this case we’ll just pass the email address to keep it simple, but if you wanted to include the name you would add another input named ‘name’.

<form action="list.php" method="post">
	<span class="status">Enter your email address below to hear updates.

	<input type="text" name="email" placeholder="Email address..." />
	<input type="submit" value="Subscribe to Updates" />
</form>	

And we’ll add some jQuery to submit the form using AJAX, and to handle the errors accordingly.

$(function(){ 
	$('form').submit(function(){
		$.ajax({
			url: 'list.php',
			type: 'POST',
			data: 'email='+$('input[name=email]').val(), //Add name here if you want it
			success: function(data){
				if(data=='Success'){
					$('form input').fadeOut(500, function(){
						$('.status').text('Email submitted! Please check your email to confirm your subscription.').css('font-weight','bold');
					});
				}else if(data=='Invalid email address') {
					$('.status').text('Please ensure you have entered a valid email address!').css('font-weight','bold');
				}else if(data=='Duplicate entry detected'){
					$('.status').text('Duplicate entry detected! You have already signed up.').css('font-weight','bold');
				}else {
					$('.status').text('Error processing data, please try again in a moment.').css('font-weight','bold');
				}
			}
		});
		return false;
	});
});

The code above is some simple jQuery to submit the form to list.php, and to handle what it returns. If the script outputs ‘Success’ it’s all good, and we can tell the user just that. We then process the rest of the errors that we cater for in our script, and humanise the data accordingly, and if the error is anything else – possibly to do with the server being busy, we just put out a standard error message.

And that is how to build an early sign up mailing list!

Download the source here.