Jump to content

Unique click tracking using PHP?


Katrina1986

Recommended Posts

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

Link to comment
Share on other sites

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.  

Link to comment
Share on other sites

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 />";
    }
Link to comment
Share on other sites

This thread is more than a year old. Please don't revive it unless you have something important to add.

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.