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 [...]

Download the source file for Create a php click counter

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:

banners index page

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

Author:

Christophe (aka krike) is a Web designer & developer living in Belgium. He spends most of his time working with Wordpress, CodeIgniter & Photoshop but also helping beginners take their first step in the development world.

Related posts

3 Comments

  1. Webby Dev Rob on 20 Feb 2011

    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

  2. krike on 20 Feb 2011

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

  3. Cottage Stuff on 29 Apr 2012

    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.

Drop us a word

Spam Protection by WP-SpamFree