Archive for August, 2010

Aug
31/10

Hidden Images in Email to Confirm Receipt, Part 2.5

Last Updated on Tuesday, 31 August 2010 11:55
Written by Cody Snider
Tuesday, August 31st, 2010

In part 1 of this series, we discussed how to track email views using an image. In part 2, we setup the tables and functions necessary to record the emails. Before we start building the reporting and campaign tracking mechanisms for step 3, we will need to adjust the code so it will be more maintainable in the future. Based on the traffic and emails I’ve received regarding this script, we are going to develop this into an easy-to-use, quick-to-deploy solution that can adapt to a variety of existing content management systems and web frameworks but still work as a standalone system.

This is simply a cleanup step and the code below is the revised version of the functionality from step 2. Please update the constants in the Config class to use this script.

Requests to this new version also have an argument change and the URL should now look like this (where ID is the id for the user that will receive the email):

http://YOUR-SITE.com/signup_complete.php?userId=ID

View Code PHP

<?php
 
// SCRIPT CONFIGURATION
class Config {
 
    const EMAIL_ADDRESS = 'you@site.com'; // YOUR EMAIL ADDRESS
 
    const SITE_DOMAIN = 'site.com'; // YOUR DOMAIN
    const IMG_PATH = '/images/email/'; // PATH TO PNG IMAGE AND TRACK.PHP
 
    const DB_HOST = 'localhost'; // YOUR DB HOST
    const DB_USERNAME = 'username'; // YOUR DB USERNAME
    const DB_PASSWORD = 'password'; // YOUR DB PASSWORD
    const DB_SCHEMA = 'database'; // YOUR DATABASE/SCHEMA NAME
 
}
 
// PRESISTENT DB CONNECT
mysql_pconnect(Config::DB_HOST, Config::DB_USERNAME, Config::DB_PASSWORD);
mysql_select_db(Config::DB_SCHEMA);
 
// USER MODEL
class User {
 
    public $id;
    public $emailAddress;
    public $status = 0; // WE'LL USE THIS AS AN ACTIVE/CAN-BE-EMAILED FLAG
 
    // STATIC METHODS
 
    public static function addUser($emailAddress) {
	$result = mysql_query('INSERT IGNORE INTO `user` SET `email_address` = "' . mysql_escape_string($emailAddress) . '";');
    }
 
    // PUBLIC METHODS
 
    public function __construct($userId){
	$this->id = $userId;
 
	if($this->fetchDetails()){
	    $this->status = 1;
	} else {
	    $this->status = 0;
	}
    }
 
    // PRIVATE METHODS
 
    private function fetchDetails(){
	$result = mysql_query('SELECT * FROM `user` WHERE `user_id` = ' . $this->id . ';');
	if(mysql_num_rows($result) == 1){
	    $row = mysql_fetch_array($result);
	    $this->emailAddress = $row['email_address'];
	    return true;
	} else {
	    return false;
	}
    }
}
 
// EMAIL MODEL
class Email {
 
    public static function send(User $user){
	if($user->status === 1){
	    $subject = 'Test email from ' . Config::SITE_DOMAIN;
	    $body = '<html><body><h1>Test Email</h1><img src="http://' . Config::SITE_DOMAIN . Config::IMG_PATH . 'image' . $user->id . '-' . rand(0, 9999) . '.png" /></body></html>';
	    $headers  = "From: " . Config::EMAIL_ADDRESS . "\r\n";
	    $headers .= "Content-type: text/html\r\n";
	    return mail($user->emailAddress, $subject, $body, $headers);
	} else {
	    return false;
	}
    }
 
}
 
$user = new User($_REQUEST['userId']);
Email::send($user);
 
?>

Posted under PHP |

2 Comments

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

Archive for August, 2010

UPDATE:

Pages

Archives