Sunday Feb 5
«
»
Aug
21/10
Hidden Images in Email to Confirm Receipt, Part 2
Last Updated on Tuesday, 31 August 2010 11:51
Written by Cody Snider
Saturday, 21 August 2010 04:07

In a previous post, we discussed how to track the viewing of an email using a 1x1px image, PHP and htaccess. Now we’ll see how to generate the ID that is associated with the email and update a database to record the action.

First, we’ll need a table to record the information. The following are two SQL queries to create the tables we need. The first query is for the table we’ll use for the tracking event and the second is a query to create the user table to identify each unique user (if you use some type of user login/account system, feel free to adjust the table parameters to work with that system):

View Code SQL
CREATE TABLE `user` (
  `user_id` int(3) NOT NULL AUTO_INCREMENT,
  `email_address` varchar(40) DEFAULT NULL,
  PRIMARY KEY (`user_id`),
  UNIQUE KEY `email` (`email_address`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8
View Code SQL
CREATE TABLE `email_tracker_event` (
  `event_id` int(3) NOT NULL AUTO_INCREMENT,
  `user_id` int(3) DEFAULT NULL,
  `event_timestamp` datetime DEFAULT NULL,
  PRIMARY KEY (`event_id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8

Next, in the document root folder for your site, add a file named signup_complete.php. This file will be the last step to your user signup form (which goes beyond the scope of this tutorial) and requires only that the user’s email address be passed as the first argument to the addUser() function we’re creating. If you are using a framework, consider adding a static method similar to this to your user model:

View Code PHP
<?php
 
mysql_connect('YOUR-DB-HOSTNAME', 'YOUR-USERNAME', 'YOUR-PASSWORD');
mysql_select_db('YOUR-DB');
 
function addUser($emailAddress){
 
    // PREPARE QUERY
    $query = 'INSERT IGNORE INTO `user` SET `email_address` = "' . mysql_escape_string($emailAddress) . '";';
 
    // EXECUTE QUERY
    $resultResource = mysql_query($query);
 
    // IF IT EXECUTED
    if($resultResource !== false){
 
	//  AND AFFECTED ONE ROW
	if(mysql_affected_rows() === 1){
 
	    // RETURN THE ID
	    return mysql_insert_id();
	} else if(mysql_affected_rows() === 0){
 
	    // OOPS! THIS ADDRESS IS ALREADY IN THE TABLE, RETURN THE ID
	    $result = mysql_query('SELECT `user_id` AS `id` FROM `user` WHERE `email_address` = "' . mysql_escape_string($emailAddress) . '";');
	    $row = mysql_fetch_array($result);
	    return $row['id'];
	}
    }
 
    // SOMETHING MUST HAVE GONE WRONG, RETURN FALSE
    return false;
}
 
function sendEmail($emailAddress, $id){
    $subject = 'Test email from your site';
    $body = '<html><body><h1>Test Email</h1><img src="http://YOUR-SITE.com/assets/images/email/image' . $id . '-' . rand(0, 9999) . '.png" /></body></html>';
    $from = 'YOUR-EMAIL-ADDRESS';
    $headers  = "From: " . $from . "\r\n";
    $headers .= "Content-type: text/html\r\n";
 
    return mail($emailAddress, $subject, $body, $headers);
}
 
// KILL ANY REQUEST THAT HAS NEWLINE/RETURN IN THE EMAIL ADDRESS, THEY'RE TRYING TO PULL A FAST ONE ON YA
if (eregi("(\r|\n)", $_REQUEST['email'])) {
    die();
}
 
// ADD THE USER AND GET THE ID
$userId = addUser($_REQUEST['email']);
 
if($userId !== false){
    echo 'USER ID: ' . $userId;
    if(sendEmail($_REQUEST['email'], $userId)){
	echo 'Email sent!';
    } else {
	echo 'Email NOT sent!';
    };
} else {
    echo 'Something went wrong, check DB connection.';
}
 
?>

Using the following URL, this script will add a user to the user table we created earlier and return the new user’s ID. This ID is what we will use for the image to track emails from the system that they have viewed.

http://YOUR-SITE.com/signup_complete.php?email=name@site.com

Last but not least, we’ll need to modify the /images/email/track.php we created in the previous post to record the viewing of the email:

View Code PHP
<?php
 
mysql_connect('YOUR-DB-HOSTNAME', 'YOUR-USERNAME', 'YOUR-PASSWORD');
mysql_select_db('YOUR-DB');
 
$trackingNumber = $_REQUEST['x'];
mysql_query('INSERT INTO `email_tracker_event` SET `user_id` = ' . mysql_escape_string($trackingNumber) . ', `event_timestamp` = NOW();');
 
header('Content-Type:image/png');
echo file_get_contents('track.png');
 
?>

In part 3 of this tutorial series, we’ll look at how to create campaigns and reports.

UPDATE:

Part 2.5 has been posted to clean up the code above and prepare it for the next step in creating a more complex email tracking system. Please review this step before proceeding to step 3 (you can still use the code above, but we’ll be building something much more complex by the end of this series).

This entry was posted on Saturday, August 21st, 2010 at 4:07 am and is filed under PHP. You can follow any responses to this entry through the RSS 2.0 feed. You can leave a response, or trackback from your own site.


1 Comment
  1. Commentskalyan   |  Tuesday, 31 August 2010 at 12:33 pm

    Great post.. Looking forward for the part 3 of this tutorial


Leave a Reply