Unique click tracking using PHP?

[Katrina1986]

Hi guys,

I was searching for a simple click tracking script, i have placed some banners in my site, i want to track total clicks and unique clicks of the banner, i have created click tracking system with the help of http://www.phpdevtips.com/2011/07/simpl ... -and-mysql , CAn anybody hep me out with unique click tracing script, also is it possible to track impressions on the banner?

Thank you guys

[ginerjm]

I don't understand your dilemma. You said that you 'created (a) click tracking system' but then you asked for help with '(a) unique click tracing script'. What's the difference? And what is an 'impression'?

[rwhite35]

A lot of that work and more is available through Google Analytics.  Rather than re inventing the wheel, I would take a look at how you integrate that solution into your web page.  Generally, I'm all about "roll-your-own" but not in this.  

[QuickOldCar]

It's hard to track unique clicks unless are logged in registered users your website.

Only other way to track is by the remote ip and is not reliable, ip's change for many users.

Setting a session or cookie and checking that as well can help not get duplicate ip clicks or views.

If wanted to do such a thing I would use a different table just for those, ip and url, ip being unique, then do a count how many for that url.

 

To get the ip:

$ip = $_SERVER['REMOTE_ADDR'];
if (strstr($ip, ', ')) {
    $ips = explode(', ', $ip);
    $ip = $ips[0];
}

You can pass the banner url as a GET parameter for tracking total views or clicking, it's the same idea. For every time is clicked or viewed you do a +1

 

Here is a way I have used to track api's, is another that I simply use datetime but for this I wanted to return results easier per year,month or day and keep them as uniques. The other method was running a script hourly and fetching the count from text files.

 

sql

CREATE TABLE IF NOT EXISTS `track` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `url` varchar(255) COLLATE utf8_unicode_ci NOT NULL,
  `t_year` int(4) NOT NULL,
  `t_month` int(2) NOT NULL,
  `t_day` int(2) NOT NULL,
  `views` bigint(20) NOT NULL DEFAULT '0',
  `clicks` bigint(20) NOT NULL DEFAULT '0',
  PRIMARY KEY (`id`),
  UNIQUE KEY `unique_index` (`url`,`t_year`,`t_month`,`t_day`)
) ENGINE=MyISAM  DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci AUTO_INCREMENT=8 ;

sql dummy data

INSERT INTO `track` (`id`, `url`, `t_year`, `t_month`, `t_day`, `views`, `clicks`) VALUES
(1, 'site.com', 2014, 2, 8, 2467, 55),
(2, 'site2.com', 2015, 5, 23, 1482, 12),
(3, 'site3.com', 2015, 1, 5, 2, 0),
(4, 'site3.com', 2015, 5, 5, 1, 0),
(5, 'site6.com', 2015, 5, 22, 3, 0),
(6, 'site6.com', 2015, 5, 23, 2, 7),
(7, 'site.com', 2015, 5, 23, 1, 0);

So for either clicks or views you use the same script

track.php?set=clicks&url=banner-url.com

track.php?set=views&url=banner-url.com

 

track.php

<?php
date_default_timezone_set('America/New_York');

if (isset($_GET['url']) && trim($_GET['url']) != '') {
    $url = trim($_GET['url']);
}

if (isset($_GET['set']) && (trim($_GET['set']) == 'views' || trim($_GET['set']) == 'clicks')) {
    $views_or_clicks = trim($_GET['set']);
}

if (isset($url) && isset($views_or_clicks)) {
    
    $dbhostname     = 'localhost';
    $dbusername     = 'user';
    $dbpassword     = 'password';
    $dbdatabasename = 'table';
    
    $con = mysqli_connect($dbhostname, $dbusername, $dbpassword, $dbdatabasename) or die("Error connecting to database");
    
    $url     = mysqli_real_escape_string($con, $url);
    $date    = time();
    $t_year  = date("Y", $date);
    $t_month = date("m", $date);
    $t_day   = date("d", $date);
    $sql     = "INSERT INTO track (url,t_year,t_month,t_day,{$views_or_clicks}) VALUES ('{$url}','{$t_year}','{$t_month}','{$t_day}','1')
   ON DUPLICATE KEY UPDATE {$views_or_clicks}={$views_or_clicks}+1";
    mysqli_query($con, $sql);
}
?> 

You can pull any data want like sum of all views for a specific url just for the day, a certain month or year, get as crazy as want with it.

A few select queries:

//one url, views just year
$sql = "SELECT url, SUM(views) AS totals from track WHERE url='{$url}' AND t_year='{$t_year}'";
	
//one url, views year and month
$sql = "SELECT url, SUM(views) AS totals from track WHERE url='{$url}' AND t_year='{$t_year}' AND t_month='{$t_month}'";
	
//one url, views year,month and day
$sql = "SELECT url, SUM(views) AS totals from track WHERE url='{$url}' AND t_year='{$t_year}' AND t_month='{$t_month}' AND t_day='{$t_day}'";
	
//one url, total views
$sql = "SELECT url, SUM(views) AS totals from track WHERE url='{$url}'";
	
//all urls total views
$sql = "SELECT url, SUM(views) AS totals FROM track GROUP BY url";
	
//all urls total clicks
$sql = "SELECT url, SUM(clicks) AS totals FROM track GROUP BY url";
	
$result = mysqli_query($con, $sql);
    while($row = mysqli_fetch_assoc($result)){
        echo $row['url']." - ".$row['totals']."<br />";
    }