Sunday May 20

Archive for the ‘PHP’ Category

Newer Entries »
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, August 21st, 2010

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).

Posted under PHP  |  Comments  1 Comment
Jul
24/10
Hidden Images in Email to Confirm Receipt
Last Updated on Saturday, 21 August 2010 04:14
Written by Cody Snider
Saturday, July 24th, 2010

It’s a little rude to ask someone to confirm they read an email, isn’t it? Well, there’s a way to get a confirmation without them knowing it using PHP and htaccess.

First, create a directory in your images folder called ‘email’ (/images/email/ for this tutorial). Inside that folder, create an .htaccess file. Be sure that your Apache directive for AllowOverride is set to All.

In this .htaccess file, add the following:

View Code HTACCESS
RewriteEngine On
ReWriteRule ^image(.*?)\-\d{4}\.png$ track.php?x=$1 [L]

Next, create a 1x1px PNG image in the same folder and name it track.png. This will be the image loaded in the email.

Finally, in the same folder create a file named track.php and add the following:

View Code PHP
<?php
 
$trackingNumber = $_REQUEST['x'];
// logic here for updating your database tables
 
header('Content-Type:image/png');
echo file_get_contents('track.png');
 
?>

Inside the email you are sending, include the following line. This will allow the system to track the ID ’1234′ when that image is loaded. Additionally, as we are using htaccess to mask the script being used, the image will appear to be a regular PNG without any extra parameters.

View Code HTML
<img src="http://www.yoursite.com/images/email/image1234.png" />

In part 2 of this tutorial, we’ll discuss where the ID is generated and how it is used.

Posted under PHP  |  Comments  10 Comments
Newer Entries »