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

20 Comments

  1. Just tried your site (proxystash) and no your list count clicks as often I click one and the same link. You should update your script very soon…

  2. You could very much improve this (by losing some tiny percentage of javascript disabled clients) by continuing to put normal links without proxying and adding event to record click via ajax just before client leaves site. Thus you would not be messing with proper linking, google ranks, and so on.

  3. yes right, some client side code will help too, but doesn’t protect click fraud (human clicks) I wrote this tutorial to help people with lists like from proxy blog.
    Btw. I don’t think that you should have to many direct links on your site, most of all if you have site from a directory type (too many outgoing links). A simple redirect is much better for some sites…

  4. Olaf, of course, for small sites it’s better.

    Still, javascript approach protects from botclicks (they do not count), adding rel=”nofollow” protects from misranking, people (hence – possible removal from google:) do not complain that all links on site and local, and javascript “click” still can be logged with your script to protect site from click fraud.

    In the end – rating clicks will get you to work on more and more preventive measures, as people get smarter :)

  5. hehe, you’re absolutely right ;)

    I think my blog title is not the best… This tutorial covers only the server side part, if you need to protect your clicks against bots some client side script will help a lot.

  6. The way you figure out the remote ip will cause problems; as you don’t try to find the real ip address of machines behind proxies, you risk not counting clicks from huge amounts of people like afaik AOL and others..

  7. Hi Maarten,

    yes this is right and I think for big sites like digg.com you should protect the vote/click feature with a login.

    This snippet is for simple sites like: http://www.proxyroll.com

  8. Great. Thanks for the cool fix. Now I need to take care of people using proxies for increasing their ‘hit’ counts.

  9. yes the right clicks counting is neccessary. do you know if such bots can fraud with google analytics? because i use this tool to track clicks on my site. thank you

  10. Hi,
    since most bots doesn’t execute client side code and Google anaylics counts the pageviews for each IP address, I think their stats should be fine.

  11. Fraudulent (sp?) clicks are a menace – either by bots or by real people. I know plenty of people who have been attacked by fraud clicks on their online PPC advertising campaigns.

    Any preventive meansures would be nice to see and its also nice to see plent of people discussing this. Thanks for the post.

  12. @Bold Endeavours

    most bots doesn’t support Javascript (they just load a page).

    It’s possible that your number of pageviews went up, but the number of visitors should not be affected.

  13. I placed an image on my blog and the link on the image goes to another website…is there anyway to calculate how many times that image has been clicked…please help…

Comments are closed.