Count clicks the right way!

If you own a link list or directory where the listings are ranked (on some page) on the number of outgoing hits, you’re possible victim of click fraud. There are not only people clicking their own listings many times, there are also click bots (remote scripts) which drive the click count “into the sky” in a relative short time.

Click fraud is bad for your sites reputation and is not fair to the listings from other websites. In this PHP code show case we will explain what a webmaster need to do to get his click counter visitor friendly and safe.

First of all don’t worry about if a click doesn’t get counted!

Objectives and requirements

We need to use a link which is informative for the visitor, a link like http://www.domain.com/click.php?id=234 doesn’t show the visitor the target link. better would be http://www.domain.com/browse.php?url=www.external-site.com

We need to identify our visitor, that a click is not counted twice because the visitor click a link twice or more. We store the IP address from the visitor together with a link ID in a database.

We need to protect us against click bots: Many click bots are very smart, they use many IP addresses from different sub-nets that they look like real people. Some of them even pre-load the website to get the sites referrer information. Since we store the IP address, the click bot can’t be very effective for the fraudulent listing. Check your click results frequently and check outstanding results (if a regular listing gets normally 10 clicks a day and on some days there are many hundreds, then some one has tried hack your click system).

You need two database tables, one for the links and one for the clicks. The first one need only an ID and a column for the URL. The table for the clicks needs a column for the URL id, one for the IP address and one for the time stamp:

CREATE TABLE `links` ( `ident` INT NOT NULL, `url` VARCHAR( 100 ) NOT NULL , INDEX ( `ident` ) , UNIQUE ( `url` ) ) ENGINE = MYISAM ;

CREATE TABLE `clicks` ( `site_id` int(11) NOT NULL, `click_time` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP, `ip_adr` varchar(30) NOT NULL, KEY `site_id` (`site_id`) ) ENGINE=MyISAM;

After we created the database tables we need to enter a few links, use a clean way to insert your links. Don’t use the protocol in the beginning (if you need this, store protocol in a extra table column)

At the moment some one has clicked the link our redirect script is executed (don’t forget the PHP tags and the building a database connection):

if (empty($_GET['url'])) {
	$url = 'http://www.yoursite.com/';
} else {
	if (get_magic_quotes_gpc()) $_GET['url'] = stripslashes($_GET['url']);
	$gURL = mysql_real_escape_string($_GET['url']);
	$result = mysql_query("SELECT ident FROM links WHERE ident = '".$gURL."'");
	if (mysql_num_rows($result) == 1) {
                $url = 'http://'.$gURL;
		$id = mysql_result($result, 0, 'ident');
		$IP = mysql_real_escape_string($_SERVER['REMOTE_ADDR']);
		$time_diff = 3600*24*14; // count ones in 14 days
		$sql = sprintf("SELECT COUNT(*) AS testval FROM clicks WHERE ip_adr = '%s' AND click_time+%d > NOW() AND site_id = %d", $IP, $time_diff, $id);
		$res = mysql_query($sql) or die(mysql_error());
		$test = mysql_result($res, 0, 'testval');
		if ($tell == 0) {
			mysql_query(sprintf("INSERT INTO clicks SET ip_adr = '%s', site_id = %d", $IP, $gURL));
		}
	} else {
		$url = 'http://www.yoursite.com/';
	}
}
header('Location: '.$url);
exit;

First we test if the variable URL exists and check also if there is a records for that URL. I the record exists, we test if there is a click records for the user with from the collected IP address. I the result is empty we add a new click record to our database. If the link is valid the user get redirected to the URL he already clicked, otherwise the visitor gets back to the site homepage.

There are a lot of free or paid scripts with a bad click counting mechanism. Use this snippet to protect your site for click fraud! In most of the cases you need only to add the click database table and you need to modify the SQL data for the links. That’s all!

Published in: PHP Scripts