open email tracking without update it twice

Question

I need some help with my PHP as I have got a problem with my code. When I receive the email and when I open it, it will update the value in the database as it will update like twice. Example: When I open the email, it should update the value like 1 but it will update the 2 value instead of 1.

Here is the open_mail.php:

<?php

header("Content-Type: image/jpeg");
readfile("image.jpeg");

//Connect to the database
include('config.php');

$id = $_GET['id'];
$tracking_sql = mysqli_fetch_assoc(mysqli_query($link, "SELECT username, subject, campaign, newsletter_type, opened FROM tracking2 WHERE id = '$id'"));
$param_username = $tracking_sql['username'];
$subject = $tracking_sql['subject'];
$campaign = $tracking_sql['campaign'];
$newsletter_type = $tracking_sql['newsletter_type'];
$opened = $tracking_sql['opened'];
$date = date('Y-m-d H:i:s');


if ($opened == 1)
{
    if ($open_again == '')
    {
        mysqli_query($link, "UPDATE tracking2 SET opened = 2, datetime = '$date' WHERE id = '$id'");
        $open_again = 'opened';
    }
}

else if ($opened == 0)
{
    if ($open_again == '')
    {
        mysqli_query($link, "UPDATE tracking2 SET opened = 1, datetime = '$date' WHERE id = '$id'");
        $open_again = 'opened';
    }
}

echo "<img src='http://example.com/Images/track.jpeg'>";

//close the connection
mysqli_close($link);
?>

The problem I found is something to do with this line:

else if ($opened == 1)

I have added $open_again = 'opened' to see if it would help to block it, but it didn't help. And I have also tried this but it make no difference.

$tracking_sql = mysqli_fetch_assoc(mysqli_query($link, "SELECT username, subject, campaign, newsletter_type, opened FROM tracking2 WHERE id = '$id' LIMIT 1"));

I cant be able to solve the issue as it keep update it like twice after when I first received the email. I am not using the loop or anything when I am using the $id variable to search for the id in a database to update it in the same row as the id as it should have update it only once. The problem I believe is something have to do with open_mail.php.

Here is what it show on the bottom of the email:

<img src=3D"http://example.com/open_mail.php?id=3D2602 " style=3D"width: 0; 
max-height:0; overflow:hidden; ">

What I want to achieve is when I first receive the email and I open it, I want to search for the id in the tracking table and update it to 1, so when I open the email again, it will update the value to 2, then 3, 4, 5...etc.

Can you please show me an example what is the best way I could use to block from updating like twice after when I first receive the email that I open so ?

Please fix your [SQL Injection vulnerabilities](https://stackoverflow.com/questions/60174/how-can-i-prevent-sql-injection-in-php) — danblack, Feb 21 '19 at 21:57
@EternalHour Thank you, I have removed the if statements and replace with danblack's answer, but unfortunately the db is updated in multiple times as it shouldn't be. Any idea? — Robert Jones, Feb 21 '19 at 22:14
in most cases the whole email and image if set to load, (but yours wont properly anyway) gets cached, you really cant count how many times an email is opened. you can check the server logs to see what is accessing the script to increment the counter an email client, cache server etc — , Feb 21 '19 at 22:27
your telling the cleint that the image src is "" when it expects an actual image, you should replace `echo "";` with `header("Content-Type: image/jpeg"); // it will return image readfile("Images/track.jpeg");` — , Feb 21 '19 at 22:29
@tim should I remove echo `"";` and use `header("Content-Type: image/jpeg"); // it will return image readfile("Images/track.jpeg");` instead? — Robert Jones, Feb 21 '19 at 22:32
@tim I have done that and it is still updated the db in multiple times after I first received the email. But it didn't updated the db in multiple times when I open the email again. Any idea? — Robert Jones, Feb 21 '19 at 22:38
see my previous comment, A, the count means nothing, B. you can check the access logs to see what actually hit the counter script. depending on the mail software at the other end there are multiple options — , Feb 21 '19 at 22:41

danblack · Answer 1 · 2019-02-21T22:23:27.123

3

Update statements can reference their own column like:

UPDATE tracking2 SET opened = opened + 1 ...

So in context:

<?php

header("Content-Type: image/jpeg");
readfile("image.jpeg");

//Connect to the database
include('config.php');

$id = $_GET['id'];
$stmt = $link->prepare('UPDATE tracking2 SET opened = opened + 1, datetime=NOW() WHERE id = ?');
$stmt->bind_param('s', $id);
$stmt->execute();

//close the connection
mysqli_close($link);

edited Feb 21 '19 at 22:23

answered Feb 21 '19 at 21:56

danblack

12,130
2
22
41

Thank you for your help, in which code I should use it instead? if you can show me an example what I should use as it will be very helpful. – Robert Jones Feb 21 '19 at 21:58
I believe he is saying that the db is updated multiple times and shouldn't be. – EternalHour Feb 21 '19 at 22:07
@RobertJones you replace your if\else with the single query, there is no need to check if this is the first of millionth 'opened' – Feb 21 '19 at 22:07
@EternalHour yes you are correct. The db is updated multiple times and it shouldn't be. I dont have open_mail.php twice in the email as I only have it one and I did not use the cron job to run as I only use the open_mail.php in the email to update the value in a db. – Robert Jones Feb 21 '19 at 22:13
@danblack thank you for the update code, but unforunately it is still the same as where it was. Any idea? – Robert Jones Feb 21 '19 at 22:31
The relationship with email and the php script and cron is poorly defined to make it hard to answer. 'it is still the same as where it was' is too hard to parse. Good luck. IF you want to limit the number of updates append `AND opened < 5` to the SQL. – danblack Feb 21 '19 at 22:46
@danblack What you mean it is too hard to parse? – Robert Jones Feb 21 '19 at 22:54
the quoted bit 'it is still the same as where it was'. No idea which `it`? What is `same`? Where is `where`? which is `it` (number 2)? Or when was `was`? – danblack Feb 21 '19 at 23:01
Like I said after the code you posted, when I try it it will updated the value 1 in a db then it will update the value again to make it 2, the same as my code. So do you know how I can block the db from update it twice after the first email get open? – Robert Jones Feb 21 '19 at 23:12

open email tracking without update it twice

1 Answers1