Count clicks in your MySQL database

With this simple script it’s possible to count and track (country code and IP address) clicks from a visitors which followed a link to one of your link partners or advertisers. This script can be used together with your existing link database or, if you have made some modifications, with an array of links. I used the IP2nation database to get the visitors country code to store this code with the other information. We use the server variable HTTP_REFERER to store the URL, where the link was clicked, with the other data. You can use this script to cloak affiliate links too.

Use these SQL statements within your MySQL client:

CREATE TABLE `links` (
  `id` int(11) NOT NULL auto_increment,
  `titel` varchar(75) NOT NULL default '',
  `naam` varchar(35) NOT NULL default '',
  `url` varchar(150) NOT NULL default '',
  `description` text NOT NULL,
  PRIMARY KEY  (`id`)
) TYPE=MyISAM;

CREATE TABLE `clicks` (
  `id` int(10) NOT NULL auto_increment,
  `link_id` int(10) NOT NULL default '0',
  `visitor_ip` varchar(15) NOT NULL default '',
  `click_at` datetime NOT NULL default '0000-00-00 00:00:00',
  `country` char(2) NOT NULL default '',
  `on_page` varchar(50) NOT NULL default '',
  PRIMARY KEY  (`id`)
) TYPE=MyISAM;

Install in the same way the data from ip2nation tables

The PHP code snippet

Create a script past this code between the PHP tags.

// place here your database connection code

if (isset($_GET['id']) && intval($_GET['id']) > 0) {
    $delay = 12*3600; // change here the number of hours how often a unique click must be counted
    $sql_check = sprintf("SELECT COUNT(*) AS test FROM clicks WHERE link_id = %d AND visitor_ip = '%s' AND UNIX_TIMESTAMP(click_at) + %d > UNIX_TIMESTAMP(NOW())", $_GET['id'], $_SERVER['REMOTE_ADDR'], $delay);
    if (mysql_result(mysql_query($sql_check), 0, "test") == 0) {
        $country_sql = "SELECT country FROM ip2nation WHERE ip < INET_ATON('".$_SERVER['REMOTE_ADDR']."') ORDER BY ip DESC LIMIT 0,1";
        $country = mysql_result(mysql_query($country_sql), 0, "country");
        $sql_insert = sprintf("INSERT INTO clicks (link_id, visitor_ip, click_at, country, on_page) VALUES (%d, '%s', NOW(), '%s', '%s')", $_GET['id'], $_SERVER['REMOTE_ADDR'], $country, $_SERVER['HTTP_REFERER']);
        mysql_query($sql_insert);
    }
    $sql_url = sprintf("SELECT url FROM link_table WHERE id = %d", $_GET['id']);
    $url = mysql_result(mysql_query($sql_url), 0, "url");
    header("Location: ".$url);
    exit;
} else {
    header("Location: http://www.yourwebsite.com/");
    exit;
}

How-to use the script in your web page?

The script above is your link target, you need to create links like:

<a href="http:domain.com/myclickscript.php?id=34">Click this link please</a>