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.
Create config.php, copy below code and update database credentials.
Create installer.php and put below in it.
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.
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 ;
`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 ;
`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);
?>
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());
}
?>
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"> </td>
</tr>
</table>
</div>
<input type="hidden" id="last_count" value="0" />
</body>
</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"> </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;
}
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>
<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
}
?>
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.
Thank you, very useful DB
ReplyDeleteHow can I keep my DB up to date?
ReplyDeletePlease check back after few days. I will publish new DB soon..
DeleteYou are the best. Great Job. I'm still on it though
ReplyDelete