Armitage2k Armitage2k - 28 days ago 8
PHP Question

PHP + PHPMailer - check if email was opened / read

I am sending system emails with PHPMailer and now need to be able to check whether the recipient has opened the mail or not. PHPMailer offers a class

$mail->ConfirmReadingTo = 'yourown@emailaddress.com';
that is similar to the Deposition-Notification-To function but I just cannot get to work.

The only alternative is the blank image solution which I managed to implement into the sent email, but am unable to execute on my server, returning a syntax error that makes no sense when including my DB credentials (the exact same code is working in many other scripts...).

I am suspecting some issues with the header declaration since the php file needs to return as genuine image to ensure it works with most providers.

Can anyone point me in the right direction please, I am wokring on this for too long and cant see it...

email.php

...
$mail->addAddress($email); // Send TO
$mail->Subject = $subject; // Email subject
$mail->Body = $message . "
<img src='http://www.qcisource.com/ihg/pages/poststay/email_tracker/record.php?type=Poststay&holidex=".urlencode($holidex)."&user=".urlencode($user)."&sent_to=".urlencode($email)."&crs=".urlencode($crs)."' width='1' height='1' border='0' alt=''/>"; // message and tracker pic in body
$mail->isHTML(TRUE); // HTML body? true/false
$mail->ConfirmReadingTo = 'qcisource@gmail.com';


record.php

<?php
error_reporting(E_ALL);

//Begin the header output
header('Content-Type: image/gif');

if(isset($_GET['type']) && $_GET['type'] == 'Poststay') {

// Start MySQLi connection
include '../../../plugins/MySQL/connect_db.php';
$mysqli = new mysqli($dbhost,$dbuser,$dbpass,$dbname);

if($mysqli->connect_errno > 0){
die('Unable to connect to database [' . $mysqli->connect_error . ']'); }

// define and sanitize GET variables
$type = mysql_real_escape_string($_GET['type']);
$holidex = mysql_real_escape_string($_GET['holidex']);
$user = mysql_real_escape_string($_GET['user']);
$sent_to = mysql_real_escape_string($_GET['sent_to']);
$crs = mysql_real_escape_string($_GET['crs']);

// check if submitted record already exists
$sql = "SELECT Holidex FROM qci_email_log WHERE Holidex = '$holidex' AND CRS = '$crs'";
$result = $mysqli->num_rows($sql);

if( $result == 0 ) {
$sql = "INSERT INTO `qci_email_log`(`Type`,`Holidex`,`User`,`Sent_To`,`CRS`) VALUES ('".$type."','".$holidex."','".$user."','".$sent_to."','".$crs."')";
if(!$result = $mysqli->query($sql)) {
die('Unable to insert email logging into database [' . $mysqli->error . ']'); }
}

// free result and close connection
$result->free();
$mysqli->close();
}

// disregarding if the database was affected, output an empty image

// URI to the image
$graphic_http = 'http://www.website.com/path/to/blank.gif';

// Get image filesize for header
$filesize = filesize( 'blank.gif' );

// Image output
header( 'Pragma: public' );
header( 'Expires: 0' );
header( 'Cache-Control: must-revalidate, post-check=0, pre-check=0' );
header( 'Cache-Control: private',false );
header( 'Content-Disposition: attachment; filename="blank.gif"' );
header( 'Content-Transfer-Encoding: binary' );
header( 'Content-Length: '.$filesize );
readfile( $graphic_http );

?>


right now, I get a syntax error here:

include '../../plugins/MySQL/connect_db.php';


EDIT: Error Message as following:
Parse error: syntax error, unexpected ';' in Z:\xampp\htdocs\ihg\pages\poststay\email_tracker\record.php on line 9

the problem is either with the include (which is unlikely) or with the $_GET handover, to which I have added the url-encode() function in the mail.php script

EDIT 3:
Updated the missing paranthesis, but the code still does not update the database...

EDIT 4:
changed the $db variable to $mysqli to be confirm with all calls throughout the script, but still no record written into the DB. added an error messages in case the insert fails, but nothing is returned...

EDIT 5:
I did not make any changes to the original code but rather would like to point out two things. (a) you are using mysql_real_escape_string, I think you should use mysqli_real_escape_string. (b) I tried your code pretty much the way you have it and was able to replicate your problem to the letter. By debugging, I found that the value in global $_GET is zapped when filtered through mysqli_real_escape_string and mysql_real_escape_string. So, this may very well be the root cause of your problem. I was able to write to MySQL only by referencing $_GET or $_REQUEST directly and we know that is never a good idea.

Answer

You are missing a closing paren on this line

if (($_GET['Type'] == 'Poststay') && (!empty($_GET['crs'])) {`

This should probably be:

if(isset($_GET['Type']) && $_GET['Type'] == 'Poststay') {

This verifies that the paramerter is set in the request, and if it equals Poststay, then will execute your other stuff.

Comments