PHP + PHPMailer - check if email was opened / read

Question

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.

Don't forget to URL-encode the values you're injecting into your URL. — Synchro, Jul 19 '16 at 06:50
@GinoPane, I checked the file and there is nothing wrong with it. I even copied the include_db.php content into the script and the error persisted. It seems the problem comes from the header() or $_GET() functions — Armitage2k, Jul 19 '16 at 06:53
If you have any error messages, you should add them to the question. — Gino Pane, Jul 19 '16 at 07:07
@Synchro, I just did that in the mail.php file, but the result is the same. the problem is not that the url has problems since it doesnt work when I enter the URL in the address bar to manually launch the script. The error starts from the If($_GET['type']) statement onwards. — Armitage2k, Jul 19 '16 at 07:07
You are using num_rows to get the number of records but you are not executing a query before that, so the result will always be 0. Also I'm not sure num_rows(the sql query) is the proper way to call that function. — PerroVerd, Jul 19 '16 at 08:36

score 1 · Accepted Answer · answered Jul 19 '16 at 07:18

1

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.

answered Jul 19 '16 at 07:18

Zac Brown

459
4
12

I figured it had to be something small like that. Still, the code doesnt update the DB and returns no error on my XAMPP. On my live server it does throw a 500 error though...? – Armitage2k Jul 19 '16 at 07:49
What's the exact error that's logged in the serverlog? Does `include '../../plugins/MySQL/connect_db.php';` actually exist at that path? – Zac Brown Jul 19 '16 at 07:52
the path does exist and my server error log spouts nonsense about my favicon.ico not existing. I fixed the favicon but still not cigar... rest of the log is empty and no other error is shown, except for the 500 message when running the script. WTH? – Armitage2k Jul 19 '16 at 08:12
I think you shoudl post another thread with your new question. The two issues are unrelated, and this'll take some more troubleshooting. – Zac Brown Jul 19 '16 at 08:32

PHP + PHPMailer - check if email was opened / read

1 Answers1