0

I am using SwiftMailer to send emails to people who are registered on my website. I want to track opens & click throughs, and I have the opens working perfectly. The click throughs are almost there...

When you open the email I use an image set to 1px by 1px that loads a PHP script. This tracks the person's email address and records the number of times they've opened it in my MySql database. All of the links in the email first take the user to a script on my site that uses GET requests to read their email address and the email that the link was contained in. This script then records this information in my DB.

The problem is that when you click on a link in the email, it records the first click just fine. And it will record every different link a user clicks on, but it will only record each click from the same source once.

IE: In the footer of each email is a link to like the company on Facebook, and another one for Twitter. When you click on FB, the DB is updated to say you clicked on 1 link. If you click on FB again, it will not update. But if you click on Twitter, it will say you clicked on 2 links.

If I load the script directly in my browser it will keep track of each click properly.

EDIT:

URL structure: https://<my website>.com/emails.php?type=click&email=<id of email>&address=<user email>&url=<link to click on>

PHP:

if($_GET['type'] == 'click') {
    $email = $_GET['email'];
    $address = $_GET['address'];
    $url = $_GET['url'];

    // Get Worker Email
    $sql = mysqli_query($con, "SELECT * FROM worker_emails WHERE id='$email'");
    $row = mysqli_fetch_array($sql);

    // Unserialize Array
    $array = unserialize($row['clicks']);

    // Update Array
    $array[''.$address.'']['Clicks'] = $array[''.$address.'']['Clicks'] + 1;

    // Serialize Array
    $array = serialize($array);

    // Update Click Tracking
    mysqli_query($con, "UPDATE worker_emails SET clicks='$array' WHERE id='$email'");

    // Send User to Link
    header("Location: ".$url);
}

Suggestions?

DecimalCampaign
  • 35
  • 1
  • 2
  • 9
  • 1
    No one other than you has access to the actual code. – Sergiu Paraschiv Oct 03 '14 at 13:51
  • 1
    This is bound to fail (the hidden image part), for example Gmail will fetch all images automatically and then serve them from their own servers. – jeroen Oct 03 '14 at 13:56
  • Jeroen, the hidden image part works perfectly. Even in Gmail. My question is about the click tracking. – DecimalCampaign Oct 03 '14 at 13:58
  • Sure. I won't mention the sql injection problem you have then. – jeroen Oct 03 '14 at 14:03
  • This is still in development, and not at all close to production stage. This was whipped together this morning, and I'm trying to find a solution to my one problem that I asked the question about. Do you make a habit out of being an asshole about off-topic issues on Stackoverflow? – DecimalCampaign Oct 03 '14 at 14:05
  • Could you also show us some DB dumps? The code looks OK to me, and if you say it works outside of emails then it might be something else. – Sergiu Paraschiv Oct 03 '14 at 14:10
  • I actually try to be helpfull. Like in my first comment. And the second one as well, keeping in mind that you don't appreciate constructive comments that are not answers in the comment section and not as an answer :-) – jeroen Oct 03 '14 at 14:15
  • 2
    By the way, have you checked that it is not a caching problem? You can click your link, then clear the browsers cache and then click the same link again to see if that is the problem. – jeroen Oct 03 '14 at 14:15
  • Jeroen, thank you. I didn't think of the cache issue, and that's exactly what it was. Once I cleared the cache the clicks were recorded properly. Any suggestions on how to get around the caching issue? – DecimalCampaign Oct 03 '14 at 14:23
  • You could replace the links with forms and buttons to make POST requests instead. You can probably style html mails to make it appear like links but that will probably fail for text-only viewers. Also, I don't do much with html mails so I don't know if forms would actually work in all mail clients. – jeroen Oct 03 '14 at 14:26
  • 2
    I'm guessing you need to read this: http://en.wikipedia.org/wiki/List_of_HTTP_header_fields#Avoiding_caching – Sergiu Paraschiv Oct 03 '14 at 14:28
  • @SergiuParaschiv Didn't even think of that, much better... – jeroen Oct 03 '14 at 14:29

0 Answers0