TRACK IF AN EMAIL WAS OPENED BY RECIPIENT - Dec 29th, 2018


If you're sending emails to a list from a series of records in a multi-record editor using CMSB's built in $mailArray
function, it’s easier then you think to track which emails were opened and which were not. (Because of the
multiplicity of email clients in cyberspace, it’s not a perfect solution but it can be pretty accurate.)

First, in the editor that contains your email addresses, you’ll need to create a check box field called ‘opened’.
It’s also handy to insert that field in the ListPage Fields so you can get a quick overview of the field’s status.

Now you’ll need to create a simple viewer ( I called mine email_open.php) that will update the ‘opened’ field to a
value of ‘1' when accessed. It also updates the updateDate to the date and time that the email was opened to
facilitate reporting. (I used the record number from the record to make sure that the right record gets updated.)


<?php
$libraryPath = 'cmsAdmin/lib/viewer_functions.php';
$dirsToCheck = array('/path/to/your/viewer_functions.php/','','../','../../','../../../');
foreach ($dirsToCheck as $dir) { if (@include_once("$dir$libraryPath")) { break; }}
if (!function_exists('getRecords')) { die("Couldn't load viewer library, check filepath in sourcecode."); }
?>
<?php
$numAsInt = intval( @$_GET['num'] );
$query = "UPDATE {$TABLE_PREFIX}client_uploads SET opened = '1', updatedDate = NOW() WHERE num = $numAsInt";
mysql_query($query) or die("MySQL Error:\n". htmlspecialchars(mysql_error()) . "\n");
$userNum = mysql_insert_id();
exit;
?>


NOTE: Dave Edis from Interactive Tools suggested, “You need to always escape or sanitize user input to prevent hackers
from tricking the script into running their own MySQL. Instead of calling mysql_real_escape_string() or a related
function I call intval() which forces the value to be a number and accomplishes the same thing."

Then somewhere in the foreach loop that loops through the records that contain your email address, you’ll need to
define the variable $recnum.


<?php $recnum = $record['num'] ?>


Then in the message body or header, you’ll need to insert an img tag that instead accesses your email_open.php file :


<img src='http://www.your_domain.com/email_open.php?num=$recnum' height='1' width='1' alt='' />


Since some recipients have images turned off in their email clients, one possibility (untested) is to use a bgsound tag
as a fallback The bgsound tag is proprietary to Microsoft so it only works on Outlook and IE.


<bgsound src='http://www.your_domain.com/email_open.php?num=$recnum ' volume='-10000'?/>


There’s also the HTML5 tag (also untested)


<audio autoplay='autoplay'><source src='http://www.your_domain.com/email_open.php?num=$recnum' type='audio/mpeg'>
</audio>


You can add additional criteria to define the record to be modified by adding those criteria to the end of the url after
$recnum (separated by &amp; ) and to the MySQL $query where statement separated by appropriate operators.

You can also implement this concept in the emailOnApproved plugin by adding:

Building on the concept in the recipe above, you can implement this idea for emails sent by the emailonapproved plugin.

Just add:


<img src='http://www.your_domain.com/email_open.php?num={$_REQUEST['num']} height='1' width='1' alt='' />


to the plugin's message area. The other files in this recipe can remain the same.

Note: If you are drawing on new records for each mailing, you won’t need to reset the ‘opened’ field for each
mailing, however, if you are drawing on the same records each time, you’ll need to reset the ‘opened’ field to a
value of ‘0’ on each record. Fortunately you can use the Field Resetter plugin from the Cookbook recipe called
“RESET THE VALUE OF A FIELD IN ALL RECORDS OR IN FIELDS IN MULTIPLE TABLES” to accomplish this with one click.



The materials on this web site have been created for use with CMS Builder content management software. CMS Builder software is published and licensed for use by InteractiveTools.com. Please contact Interactive Tools for information on the downloading of the software or the purchasing of licenses.


Terms of Service