Jul
21
2011

Simple (Advanced) Click Tracking Using PHP and MySQL

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]

12 Comments + Add Comment

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

    • Great! Glad you found it helpful!

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

  • […] 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 […]

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

  • 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…

  • can’t we do it by jquery?

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

  • 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.

    • 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)

  • Thanks very much 🙂

  • Code update for PHP 7.0

    https://codeshare.io/axzEnD

Leave a comment