Jul
21
2011
PHP // Tricks

Simple (Advanced) Click Tracking Using PHP and MySQL

Author An article by admin    Comments 12 Comments

On a recent project, I was asked to record the pages that each user was navigating to in a secured special-purpose website.  Initially it seemed daunting, however I recalled a script I’d written to track email open rates using PHP.

I am generally disinterested in maintaining a database table of pages, along with a second table of clicked links as I find it to be very inefficient, so I opted to create a simple pass-through method.

The requirements were simple:

  1. For each link, record the ID of the person who clicked it, and where they went
  2. Direct the user to that page with no noticeable redirect.
  3. Function properly with in-site or external-site links

No problem.

Step 1: Make the link

We must first ensure that all of the links we’re using are redirected through the pass-through page, while containing the ending page.

The ending page can be ANY page, linking within, or out-of your website.

<a href="http://www.YOURURL.com/trackit.php?page=YOURPAGE.php">Record This Link!</a>
<a href="http://www.YOURURL.com/trackit.php?page=https://www.phpdevtips.com">Record This Link!</a>

Step 2: The Database Table

This is pretty simple, as for this example we’ll only be tracking 2 parameters: 1) the page, and 2) the timestamp.  Note that I’ve added the “recid” column as an auto increment column in order to ensure a unique identifier.

CREATE TABLE `tracking_table` (

`recid` int(11) unsigned NOT NULL AUTO_INCREMENT,

`rec_use_page` varchar(220) DEFAULT NULL,

`rec_use_date` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00' ON UPDATE CURRENT_TIMESTAMP,

PRIMARY KEY (`recid`)

);

Step 2: The Tracking Page

This gets a little more complex.  We must receive the request from the link, insert the end-page into the database, and seamlessly pass the user to the page they are trying to get to.

<?php

//Connect to the database

mysql_connect("localhost", "root", "root") or die(mysql_error());

mysql_select_db("tetestdb") or die(mysql_error());

//Grab the destination page from the link
$redirect = mysql_real_escape_string($_GET['page']);

//Insert the destination page and timestamp into your database

$page_insert = mysql_query("INSERT INTO tracking_table (`rec_use_page`, `rec_use_date`) VALUES ('$redirect', now())") or die(mysql_error());

//Redirect the user to their intended location

header("Location: $redirect");

?>

Step 4…

Sorry to disappoint.  No step 4 here.  You now have a system of tracking any link that you manage, and the script can be easily modified.  For example, as aforementioned- my requirements include attaching a user ID to each link and so-on.

The entire script including database table may be downloaded here: [dm]3[/dm]

Tags: ,

12 Comments + Add Comment

  • Dhaval November 28, 2012 at 5:01 pm

    excellent article. thanks very much. I was able to use it to build a slightly more complex logging.

    Reply
    • Bennett December 20, 2012 at 6:15 pm

      Great! Glad you found it helpful!

      Reply
  • Paul April 4, 2013 at 10:54 pm

    Awesome dev tip. This will really help the client track ad clicks. Thanks for the article.

    Reply
  • track link clicks and alter link order with php/mysql? November 14, 2013 at 5:50 pm

    […] and elements by using a class id. I have looked high and low and the only thing I can find isCode: https://www.phpdevtips.com/2011/07/simple-advanced-click-tracking-using-php-and-mysql/ but I don't know how or where to edit it for my needs. Thanks Reply With […]

    Reply
  • sylvester June 17, 2014 at 1:30 pm

    nice tutorial really helped me. can I do it for image links as well

    Reply
  • Free Classified October 24, 2014 at 10:53 am

    Great Job Bennett Stone… Thanks for such a wonderful and simple tracking system. Here I need a little more help on this How can I track a conversion from any link ?

    Well I am going to test it on my classified site, If need any help will bother you again…

    Reply
  • ankit November 30, 2015 at 7:26 am

    can’t we do it by jquery?

    Reply
    • Bennett Stone December 12, 2015 at 12:02 pm

      Most email clients remove any javascript or externally included files, so no.

      Reply
  • Nikhil March 10, 2016 at 7:27 am

    First of all thanks for excellent article.
    Could you please suggest how we can track hard-bounce and spam complaints on a email using PHP.

    Reply
    • Bennett Stone April 19, 2016 at 3:05 pm

      For that you’d need a service such as sendgrid or mailgun (alternately you’d need to do some fairly serious mail management on your own server, however, mailgun is a good way to go and very inexpensive)

      Reply
  • Ambar April 2, 2016 at 11:05 am

    Thanks very much 🙂

    Reply
  • grechucia February 12, 2018 at 3:30 pm

    Code update for PHP 7.0

    https://codeshare.io/axzEnD

    Reply

Leave a comment