PHP - Link clicks counter

From Global Programming Syntax

Sometimes if you have a download link or a link to your friends site, you might want to record how many times those links are being clicked on. Below is a simple link counter which requires a MySQL database and PHP. To create the MySQL table run/execute the following script with the first 5 lines (4 variables) being set to your database. After running/executing the below script, you can delete file with the below script from the server and proceed with the rest of the instructions.

<?
$dbhost='localhost'; //database host (usually localhost)
$accountname='root'; //database username.
$password=''; //database password
$database='my_database'; //database name - not table
 
//configure the above variables.
 
 
$linkID = @mysql_connect($dbhost,$accountname,$password)
	or die("Could not connect to MySQL server");
@mysql_select_db($database) or die("Could not select database");
 
mysql_query('CREATE TABLE `'.$database.'`.`stats_linkclicks` (`link_id` TEXT NOT NULL, `link_to` TEXT NOT NULL, 
`hits` TEXT NOT NULL) ENGINE = MyISAM') or die(mysql_error());
echo "Table named 'stats_linkclicks' has been created successfully."
?>

Now the MySQL table is set up, second step is to make the file which will record the data. So when the user clicks the link, the user will be sent to this new php file (script below) and from that file will be redirected to the real location without them knowing they have visited this php file. So create a new file called redirect.php and place the following script within that file:

<?
$dbhost='localhost'; //database host (usually localhost)
$accountname='root'; //database username.
$password=''; //database password
$database='my_database'; //database name - not table
 
//configure the above variables.
 
 
 
if (isset($_GET['redirect']) && isset($_GET['link_id']))
    {
    $result=mysql_query("SELECT * FROM `stats_linkclicks` WHERE `link_id`='".
    $_GET['link_id']."' AND `link_to`='".$_GET['redirect']."'");
    if (mysql_num_rows($result)==0)
        {
        mysql_query("INSERT INTO `stats_linkclicks` SET `link_id`='".
        $_GET['link_id']."', `link_to`='".$_GET['redirect']."', `hits`='1'");
        } else {
        $getarr=mysql_fetch_array($result);
        $hits=$getarr['hits']+1;
        mysql_query("UPDATE `stats_linkclicks` SET `hits`='".$hits."' WHERE `link_to`='".
        $_GET['redirect']."' AND `link_id`='".$_GET['link_id']."'");
        }
    header("Location: ".$_GET['redirect']);
    echo "<a href='".$_GET['redirect']."'>redirect link</a>";
    exit;
    } else
if (!isset($_GET['redirect']) && !isset($_GET['link_id']))
    {
    $result=mysql_query("SELECT * FROM `stats_linkclicks`");
    echo "<table border=1 cellpadding=3 cellspacing=0><tr bgcolor='#CCCCCC'><td>Link ID</td><td>Link To</td><td>Hits</td></tr>";
    while ($row=mysql_fetch_array($result))
        {
        echo "<tr><td>".$row['link_id']."</td><td>".$row['link_to']."</td><td>".$row['hits']."</td></tr>";
        }
    echo "</table>";
    exit;
    } else {
    echo "Invalide url variables detected";
    }
?>

Now the final step is just to setup the link correctly so it will record the data and redirect properly. Below is an example of a link that leads to www.google.com and the page it is from is index.php

<a href='redirect.php?redirect=http://www.google.com&link_id=from_file.php'>link</a>

So when linking to 'www.google.com' from a page named 'index.php', you need to first place after the redirect variable (redirect=) the url you are linking to which in this case was 'www.google.com.au'. However, if you are using the domain name, you must place 'http://' or 'https://' before the domain name. This is just so the computer knows which protocole to use but does not apply if the url is just something like 'page.html' or 'folder/page.php'. Then to identify the link, place the from address after link_id= which will be displayed when viewing the stats. Below is an example of each part of the link broken up.

<a href='redirect.php?redirect=
http://www.google.com

&link_id=
from_file.php

'>link</a>

Then you may say where are my stats. Well just open redirect.php without anything after the question mark (no url variables) and you will then be able to view all urls ever accessed through the redirect file.