Create a php click counter

by krike in PHP & MYSQL / Tutorials on 06 Mar 2010


Since my deposit/advertising script was approved on the envato network (codecanyon.net) the first request I received was to create a click tracking to see how many times a specific banner was clicked on. In this tutorial I will teach you the basics of creating such a click counter.

Example of the click counter of the deposit script

deposit click counter

The database

Let’s start by creating a new database called counter and execute following queries

CREATE  TABLE  `banners` (
 `id` INT NOT  NULL  AUTO_INCREMENT  PRIMARY  KEY ,
  `url` VARCHAR( 150  )  NOT  NULL ,
  `banner` VARCHAR( 150  )  NOT  NULL ,
  `name` VARCHAR( 60  )  NOT  NULL, 'clicks' INT NOT NULL DEFAULT  '0'
) ;

Let’s insert some data now

INSERT INTO `banners` (`id`, `url`, `banner`, `name`, `clicks`) VALUES
(1, 'http://themeforest.net', 'http://envato.s3.cdn.plus.org/referrer_adverts/tf_260x120_v2.gif', 'themeforest', 0),
(2, 'http//graphicriver.net', 'http://envato.s3.cdn.plus.org/referrer_adverts/gr_260x120_v4.gif', 'graphicriver', 0);

Configuration of the database

Let’s start by creating all of the nescessary files for this tutorials. Create 3 new php files, name them index.php, config.php and counter.php

Paste the following code into config.php to make the connection to the database, this you will need to display the banners and to track the clicks

note*: I’m using mysqli and not regular mysql, you may need to pay attention to this because the parameters for the sql query and the link to the database is different when using mysqli.

<?php $db_host = "localhost";
$db_username = "root";
$db_password = "";
$db_database = "counter";

$link = mysqli_connect($db_host,$db_username,$db_password) or die("Cannot connect");
mysqli_select_db($link, $db_database) or die("Cannot select the database"); ?>

Make sure to fill in the right username, password and database name before you continue

Display of the banners

Open index.php if haven’t allready done so. Let’s add the basic html structure:

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Strict//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-strict.dtd">
  <html xmlns="http://www.w3.org/1999/xhtml">
  <head>
  <meta http-equiv="Content-Type" content="text/html; charset=utf-8" />
  <title>banners index page</title>
  </head>
<body>
</body>
</html>

Above the doctype let’s include the config file and query for the banners

<?php
include("config.php");
$sql = mysqli_query($link, "SELECT id, url, banner, name FROM banners");
  ?>

Now we need to fetch the queried data and display each banner from our table, so between the body tag add the following code

<div id="container">
<?php 
while($result = mysqli_fetch_object($sql)):
    ?>
    <p>
    <a href="counter.php?id=<?php echo $result->id; ?>">
    <img src="<?php echo $result->banner; ?>" alt="<?php echo $result->name; ?>" />
    </a>
    </p>
    <?php
endwhile;
?>
</div><!-- end of container -->

Now browse your index.php, If everything works fine you should have something like this:

Now the trick is in fact to link to the counter.php and pass in the id of the banner as a parameter (eg: counter.php?id=//id of the banner), which we allready did (see previous code). We then run a query to increase the click with 1 and then we redirect the user to the correct url.

Track click and redirect

Open counter.php and add the following code

<?php

//1. include the configuration file
  include("config.php");
  
//2. Get the id from the url and store it into a variable
  $id = mysqli_real_escape_string($link, $_GET['id']);
  
//3. fetch the url and clicks from this banner
  $clicks = mysqli_fetch_object(mysqli_query($link, "SELECT url, clicks FROM banners WHERE id=".$id.""));
  
//4. increase clicks with 1
  $new_click = $clicks->clicks+1;
  
//5. update this into the database, check if it was succesfull
  if(mysqli_query($link, "UPDATE banners SET clicks=".$new_click." WHERE id=".$id."")):
  //6. redirect to the url
  header("Location: ".$clicks->url);
  else:
  //6. else write to error log
  endif;
?>

so let’s go over each line

  1. 1. Include the configuration file
  2. 2. Get the id from the url and store it into a variable
  3. 3. Fetch the url and clicks from this banner
  4. 4. Increase clicks with 1
  5. 5. Update this into the database, check if it was succesfull
  6. 6. Redirect to the url else write to error log

Conclussion

There you go in only a few steps you were able to create a click tracker to keep track of how many times your banners were clicked on your site. If you don’t want to spend to much time coding the click tracker yourself you might be interested into the deposit/avertisement script at http://codecanyon.net/item/deposit-system/59753

Written by krike

Lorem ipsum dolor sit amet, consectetur adipiscing elit. Nam sit amet nisl nisl. Ut interdum libero vitae quam ultricies et lacinia elit aliquet. Praesent tincidunt, sem tempus feugiat feugiat, turpis tellus scelerisque erat, sit amet feugiat neque arcu ac lectus. Sed at mi et elit interdum scelerisque vitae eu felis.

krike has written 77 posts.

  • Hi,

    Nice tutorial!

    Some suggestions to improve:

    – simple cookie/ip check to stop spamming of clicks
    – a seperate table to monitor the click date (to enable sorting of clicks via a date period in the admin area

    I think you also missed the bit about impressions in your tutorials too 🙂

    THanks

    • @Webby Dev Rob, thank you for your comment. This is actually a basic tutorial to guide people into creating the basics of a click counter script. However you are making a very good point by implementing a check to make sure not to count spammer clicks. As for the impressions, I think you are referring to the screenshot. I did seem to have missed it, I will try to fix that as soon as possible.

  • This is a very well-written tutorial. One observation: With this way of counting clicks (I think) we cut off the normal flow of SEO link juice because the target webpage URL is being removed from the href attribute. I am looking at the moment at a website that counts clicks on normal links, leaving the normal href attribute with its URL intact, and firing the counting script using the onClick attribute. For people who want to pass on link juice in the normal way, that would seem to be a better approach.

  • Anna

    Ive been looking for this kind of tutorial! Much thanks to you. 🙂

  • Rodrigo Montenegro

    I have been trying to do this for two days. Thanks, man ♥.