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, ther eare 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 subnets 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 timestamp:
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!
Related posts
- Limit the number of downloads per client
- More advanced features in phpMyAdmin
- Upload images for usage in TinyMCE
Comments
Trackback URL for this post: http://www.web-development-blog.com/archives/count-clicks-the-right-way/trackback/
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.
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 
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.
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..
Great. Thanks for the cool fix. Now I need to take care of people using proxies for increasing their ‘hit’ counts.
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
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.
Hi Robert,
right click fraud related to ads are a terrible fro publisher and advertiser. A lot of google adsense accounts became suspended because of click fraud.
PS. fraudulent is okay check http://www.answers.com/fraudulent
Sorry, the comment form is closed at this time.
A better click counter for the proxy List script…
Hi,
just wrote some tutorial for a safer click counter on proxy list script sites:
the code you should use for your “count_hits.php” file is:
if (empty($_GET)) {
$url = ‘http://www.yoursi……