0

I m using PHP to track email opens and update it in mysql database.
But whenever a open event happens i m getting multiple inserts from the same user like this

userid  response_time

db20eea614  2020-05-07 19:13:27
db20eea614  2020-05-07 19:13:27
db20eea614  2020-05-07 19:13:27
db20eea614  2020-05-07 19:13:26
db20eea614  2020-05-07 19:13:26
db20eea614  2020-05-07 19:09:46
db20eea614  2020-05-07 19:04:26
c8decdc52c  2020-05-07 18:22:55
c8decdc52c  2020-05-07 18:22:55
9c481bde00  2020-05-07 16:56:43
9c481bde00  2020-05-07 16:56:22

This is my PHP:


if(isset($_GET['encid']))
{


$mysqli=new mysqli("localhost","un","pass","tab1");
if($mysqli === false){
 die("ERROR: Could not connect. " . $mysqli->connect_error);
}


date_default_timezone_set("Asia/Kolkata");
$rt = strtotime(date_default_timezone_get());
$rtime = date("Y-m-d H:i:s", $rt);


 $sql = "insert into resptab (userid, response_time) values (?,?);";
        if($stmt = $mysqli->prepare($sql)){
                 $stmt->bind_param("ss", $_GET['encid'], $rtime);
                  $stmt->execute();
                  $stmt->close();
        }
        $mysqli->close();


}

This is my tracking link inside HTML message body

<img src="http://mydomain/index/track.php?encid={userid}" width="1" height="1">

I noticed this irregularity from gmail and yahoo user emails. I'm getting multiple requests from the email end for a single event. How do i prevent this ?
Is there a way to cut out multiple incoming requests for a period of time after the first request is registered?

Jason Maddox
  • 25
  • 6
  • You could add a unique constraint to your database based on the user_id and response_time if you're sure that those two should be unique in combination. If you're concerned about the multiple requests, I think you'd need to show where the requests come from, not how you handle the requests. – droopsnoot May 08 '20 at 11:54
  • I've placed my tracking link in **img** tag of html [inside email message body], so that it will call during mail open `````` – Jason Maddox May 08 '20 at 13:41
  • @droopsnoot, regarding unique constraint, here is my problem : What if a user wants to read an email again and again. i want to track that too. In the above case, the user is opening the mail once only, but there are multiple inserts happnd, I ve tested it with my own gmail id also – Jason Maddox May 08 '20 at 13:46
  • That's why you have the constraint based on the user_id _and_ the response_time, not just the user_id. It's only when the combination of both is not unique that it will be rejected. That presumes the user won't legitimately be able to read it twice at exactly the same time. – droopsnoot May 08 '20 at 17:52
  • It would of course be better to figure out why your code is being called more than once, but I don't think that's anything to do with the code you showed. – droopsnoot May 08 '20 at 17:58
  • @droopsnoot, that sounds logical, I ll use unique constraint based on two columns. but that won't solve my problem completely. – Jason Maddox May 09 '20 at 13:39
  • https://stackoverflow.com/a/3025332/12167751 planning on implementing this to avoid same userid and rtime insertion – Jason Maddox May 09 '20 at 14:15

0 Answers0