External Link Hit Counter

If you own a resource website with a links page you may want to keep track of the links people are visiting so you can adjust your content and the links you add to suit. This simple external link hit counter will be able to help.

First, we need to create a database, and table in which we can store the links the referrers. We’ll do this with MySQL. Create a file called create.php and place the following code in it:

<?php
$connect = mysql_connect("localhost", "MYSQL_USER", "MYSQL_PASS");

$createdb = "CREATE DATABASE `linksdb`";
$createtbl = "CREATE TABLE `links` (
   `id` INT NOT NULL AUTO_INCREMENT,
   `siteurl` VARCHAR(255) NOT NULL,
   `sitename` VARCHAR(150) NOT NULL,
   PRIMARY KEY (`id`))";
   
$createtbl2 = "CREATE TABLE `linksref` (
   `id` INT NOT NULL AUTO_INCREMENT,
   `linkid` INT NOT NULL,
   `referrer` VARCHAR(255) NOT NULL,
   `hitsout` INT NOT NULL,
   PRIMARY KEY (`id`))";

if (mysql_query($createdb, $connect)) {
   echo "<p>Database created.</p>";

   mysql_select_db("linksdb", $connect);
   if (mysql_query($createtbl, $connect) && mysql_query($createtbl2, $connect)) {
      echo "<p>Tables created.</p>";
   } else {
      echo mysql_error();
   }
} else {
   echo mysql_error();
}
?>

Before you run the page in your browser, edit “MYSQL_USER” and “MYSQL_PASS” to match your MySQL username and password. If you don’t know your details and don’t know how to get them, get in touch with your host and they should be able to help.

Let’s go through the code that we’ve just pasted above. First things first we ‘open’ our PHP and declare the connection string — this will tell our mini script where to connect to MySQL, and who to connect as. The next bit is pretty self-explanatory: create a database called “linksdb” (those slanty apostrophe lookie-likies can be achieved by clicking the key next to the number ‘1’ (usually)). After that we create the table that’ll store our links with the fields: id, siteurl, sitename and hitsout; and the table that’ll store our referrers: referrer and hitsout.

The next line of code is an if statement, this checks whether or not the create database statement we typed above can actually run. If it can, it’ll echo a success sentence and then continue on to create the table. If that is successful, it will also echo a success statement. If the database creation string cannot be created you’ll get an error, and the table creation won’t even be attempted. Anyone getting errors with the creation can e-mail the error/s to me at jem@jemjabella.co.uk.

Once the database and table has been created, you can delete create.php and create links.php. In links.php we’ll ‘print’ the links we’ll be storing in our database and link to the file that will handle adding to the clicks out count. Let’s start by adding one of those mysql connection strings again:

<?php
$connect = mysql_connect("localhost", "MYSQL_USER", "MYSQL_PASS");
mysql_select_db("linksdb", $connect);

?>

Next we’ll pull our links using a mysql_query() and use while() plus the handy built in mysql_fetch_assoc() function which will put the entries from our table (our links) into an array to make echo-ing easier. When we do echo the data, we don’t want to show a link directly to the site/s that we’re actually linking to because we’d not be able to count the hits out. Instead, we link to a new file that we’re going to create in a moment using the unique id stored with each link to identify it and therefore that means links.php should now look like this:

<ul>
<?php
$connect = mysql_connect("localhost", "MYSQL_USER", "MYSQL_PASS");
mysql_select_db("linksdb", $connect);

$query = mysql_query("SELECT `links`.`id`, `links`.`sitename`, SUM(`linksref`.`hitsout`) AS `hitsout` FROM `links` LEFT JOIN `linksref` ON `links`.`id` = `linksref`.`linkid` GROUP BY `linksref`.`linkid`", $connect);
while($row = mysql_fetch_assoc($query)) {
   echo '<li><a href="linkcount.php?id='.$row['id'].'">'.$row['sitename'].'</a> ('.$row['hitsout'].')</li> ';
}
?>
</ul>

That’s actually it for the links.php file. Of course, if you don’t want them in an unordered list you could swap <ul> […] </ul> for <table> […] </table> and <li> […] </li> for <td> […] </td>. Now, onto the important bit. Create linkcount.php and paste the following code in:

<?php
$connect = mysql_connect("localhost", "MYSQL_USER", "MYSQL_PASS");
mysql_select_db("linksdb", $connect);

if (isset($_GET['id']) && is_numeric($_GET['id'])) {
	$id = (int)$_GET['id'];

	$strip = array("http://", "www.");
	$referrer = str_replace($strip, "", rtrim($_SERVER['HTTP_REFERRER'], "/"));

	$findLink = mysql_query("SELECT `links`.`siteurl` FROM `links` WHERE `links`.`id` = ".(int)$id." LIMIT 1");
	if (mysql_num_rows($findLink) == 0) {
	   header("Location: links.php");
	   exit;
	} else {
		$url = mysql_result($findLink, 0, 0);

		$findReferrer = mysql_query("SELECT `id` FROM `linksref` WHERE `linkid` = ".(int)$id." AND `referrer` = '".mysql_real_escape_string($referrer)."' LIMIT 1");
		if (mysql_num_rows($findReferrer) == 1) {
			$id = mysql_result($findReferrer, 0, 0);
			mysql_query("UPDATE `linksref` SET `hitsout` = `hitsout` + 1 WHERE `id` = ".(int)$id);
		} else {
			mysql_query("INSERT INTO `linksref` (`linkid`, `referrer`, `hitsout`) VALUES (".(int)$id.", '".mysql_real_escape_string($referrer)."', 1)");
		}

		header("Location: ".$url);
		exit;
	}
} else {
	exit;
}
?>

I’ve copied the code in one go to prevent mistakes that might occur if it was pasted bit by bit as this is one of the most important parts of our script. Briefly explained: the script connects to the database; makes sure that the id submitted via the URL is in fact a number (integer); returns you to the links page if the id isn’t a number or doesn’t exist in the database; adds to the hitsout count if the link does exists and last but not least, redirects the user to the requested URL.

Once you’ve uploaded the files (don’t forget to delete ‘create.php’ when you’ve run it) and added the links to your database, you’re ready to roll. To add links you will have to use PHPMyAdmin, until I can get ’round to writing a second tutorial on creating a link management panel.

Comments

  1. Leah Felicilda says:

    Hi there,

    I want to ask if this “External Link Hit Counter” would be able to track the number of people who will use the shared link that I have in my website? I’m not that techi so pardon my asking. If you can answer my question please do contact me.

    Thank you!

    Leah

  2. thank you for this script ;)

Speak Your Mind

*