Please wait for 15 seconds...

Friday 2 May 2014



Today I am going to show you, how to create a simple visitor geo tracker or geo location system. This is one of the most basic and important tool that every website owner uses to analyze how website is going,  in which country website is popular, showing ads by country, redirect user to country specific page and much more. Let's create it in a simple and effective way.

Simple Visitor Geo Tracking System








Now copy below database table codes and create table with it.

IP and Geo Location Table

CREATE TABLE IF NOT EXISTS `geo_ips` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`ip_start` bigint(20) NOT NULL,
`ip_end` bigint(20) NOT NULL,
`country_code` varchar(5) NOT NULL,
`country` varchar(255) NOT NULL,
`state` varchar(255) NOT NULL,
`city` varchar(255) NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 ;


Visitor Record Table

CREATE TABLE IF NOT EXISTS `visitors` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`country` varchar(255) NOT NULL,
`state` varchar(255) NOT NULL,
`city` varchar(255) NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 ;

Create config.php, copy below code and update database credentials.

config.php

<?php
define('DB_HOST', '***DB_HOST***');
define('DB_USER', '***DB_USER***');
define('DB_NAME', '***DB_NAME***');
define('DB_PASSWORD', '***DB_PASSWORD***');

ini_set ('display_errors', 1);
error_reporting(E_ERROR);
?>

Create installer.php and put below in it.

installer.php(PHP part)

<?php
include 'config.php';

mysql_connect(DB_HOST, DB_USER, DB_PASSWORD);
mysql_select_db(DB_NAME);

// install database
if(isset($_GET['start'])){
   $start = $_GET['start'];
    $base_dir = "ip_sql/";
    $file_name = "ipdb0".$start.".sql";
    $file_data = file_get_contents($base_dir.$file_name);
    $query = trim($file_data);
 
    if(!@mysql_query($query))
        die(mysql_error());
}
?>


installer.php(HTML part)

<html>
<head>
<meta content="text/html; charset=utf-8" http-equiv="Content-Type" />
<title>Setup Geo Database/Table</title>
</head>
<body>
<div class="msg_main">
    <table border="0" width="100%">
        <tr>
            <td class="bold" valign="top">Message: </td>
            <td id="result">&nbsp;</td>
        </tr>
    </table>
</div>
<input type="hidden" id="last_count" value="0" />
</body>
</html>


installer.php(CSS part)

.bold{
        font-weight: bold;
    }
    .msg_main{
        position: relative;
        width: 400px;
        left: 50%;
        margin-left: -200px;
        padding: 20px;
        border: 2px skyblue solid;
        margin-top: 5%;
    }
    .msg_main td{
        padding: 7px;
    }


installer.php(javascript part)

<script type="text/javascript" src="jquery.min1.11.0.js"></script>
<script type="text/javascript">
$(document).ready(function(){
    db_installer();
});
function db_installer(){
    var last_count = parseInt($('#last_count').val());
    var start = last_count+1;
    var max = 1973;
    var msg = "";
    if(start <= max){
        $.ajaxSetup({
            url: "installer.php",
            type: "get"
        });
        $.ajax({
            data:"start="+start,
            success:function(response){
                msg = "Remaining files to install "+(max-start);
                msg += "<br>So sit back & relax and let the installer finish its job";
                $('#last_count').val(start);
                $('#result').html(msg);
             
                db_installer();
            }
        });
    }
    else{
        msg = "Completed";
        $('#result').html(msg);
    }
}
</script>


And do not forget to include jquery library in head tag.

Installer is ready and you have almost finished. Now you need to download geo IP table data.
Open installer.php in your browser and let it prepare geo IP table.
Since installer is preparing Geo IP table let's create tracker.php.  
Create tracker.php and copy & paste below code.


tracker.php

<?php
include 'config.php';

mysql_connect(DB_HOST, DB_USER, DB_PASSWORD) OR die(mysql_error());
mysql_select_db(DB_NAME) OR die(mysql_error());

$ip =  $_SERVER['REMOTE_ADDR'];
$long = sprintf('%u', ip2long($ip));
$sql = "SELECT * FROM `geo_ips` WHERE '$long' BETWEEN `ip_start` AND `ip_end`";
$result = mysql_query($sql) OR die(mysql_error());
$ip_detail = mysql_fetch_assoc($result);
if($ip_detail){
    // recored visitor location
    $sql = "INSERT INTO `visitors`(`country`,`state`,`city`)
            VALUES ( '".$ip_detail['country']."', '".$ip_detail['state']."', '".$ip_detail['city']."')";
    mysql_query($sql) OR die(mysql_error());
}
else{
    //Something wrong with IP
}
?>

After finishing Geo IP table you are ready to test  Visitor Geo Tracking System.
You can download tester.php and check if everything is working fine.

Posted by Atul

4 comments:

  1. How can I keep my DB up to date?

    ReplyDelete
    Replies
    1. Please check back after few days. I will publish new DB soon..

      Delete
  2. You are the best. Great Job. I'm still on it though

    ReplyDelete

Techsirius on Facebook