How to build a custom web analytics tool in PHP, MySQL and jQuery Ajax
It is with no doubt the desire for all website owners to have as many people as possible visiting their websites.
In whichever niche or industry your website falls under, you definitely wish to have as much traffic as you possibly can.
With huge traffic (number of website visits) comes great opportunities such as increased brand awareness, more app installations, more leads, more sales (of physical or digital products), more income through display advertisements, more income from affiliate marketing, etc.
As a way of getting more traffic to their websites, many website owners go an extra mile to market their websites through multiple ways such as Search Engine Optimization (SEO), Pay per Click (PPC) advertising, Search Engine Marketing (SEM), Social Media Marketing (SMM), Email Marketing, Affiliate Marketing, Content Marketing and Influencer Marketing among other ways.
There is then the need to monitor, measure and track the marketing efforts through the amount of traffic the website gets in return.
There exist many and great tools that can help in tracking and monitoring the traffic to a website.
Google Analytics for instance is the most popular free website traffic analytics tool owned by Google. It is easy to integrate and use. With it, you can view how many visitors your website has had over a given duration, the countries in which your visitors are coming from, the devices they are using, how they get to your website, how they leave your website, how long they stay on your site, the most visited pages, number of visitors currently viewing your website and their location, etc.
Another powerful and popular tool for analytics is AWstats, a tool that comes already packaged in your web hosting cPanel account. With it, you can as well track multiple aspects of your website traffic.
With these tools, you have all you need. In most cases, you won't need to make your own analytics tool. In fact, most people would discourage you from reinventing the wheel.
However,
- If you are the curious kind who never tire from experimenting and trying new things.
- If you find fun in always building new tools.
- If you like things made custom to your taste and individual needs.
You may then consider building your own.
Building a custom Web Analytics tool
In this tutorial, I will take you through a step-by-step procedure on how to create a custom website analytics tool (like Google Analytics) using PHP, MySQL, and jQuery Ajax.
With this tool, we will be able to store a record of every unique page visit. We will save the following details:
- The URL of the page;
- The IP address of the visitor;
- The operating system of the visitor;
- The browser the visitor is using;
- The version of the browser;
- The country from which the visitor is viewing the page;
- The time in which the visitor gets into the page;
- And lastly, the time in which they leave the page.
In your MySQL website database, create a table with the fields(columns) as below with this SQL query:
CREATE TABLE `analytics` (
`id` int(20) NOT NULL,
`page_url` varchar(150) NOT NULL,
`entry_time` datetime NOT NULL,
`exit_time` datetime NOT NULL,
`ip_address` varchar(30) NOT NULL,
`country` varchar(50) NOT NULL,
`operating_system` varchar(20) NOT NULL,
`browser` varchar(20) NOT NULL,
`browser_version` varchar(20) NOT NULL,
`created_at` timestamp NOT NULL DEFAULT current_timestamp()
)
Our table will be created and look like below in PhpMyAdmin.
Related article: Creating and setting up MySQL database in cPanel
Now that our database table is ready, the following step is to collect the information we want to save.
Getting the current page URL
In a previous article, I explained in detail how to get the full URL of the current page using PHP language. Here I won't go much into details, use the PHP code below to get the URL.
<?php
$pageurl = (isset($_SERVER['HTTPS']) && $_SERVER['HTTPS'] === 'on' ? "https://" : "http://").$_SERVER['HTTP_HOST'].$_SERVER['REQUEST_URI'];
?>
Getting the current time
The next step is to get the current time in DateTime format. We can do it using PHP code as below:
<?php
$timenow = date("Y-m-d H:i:s");
?>
Related article: How to get the current date and time in PHP
Getting the user IP address
Below is how you get the IP address of the user in PHP:
<?php
if(!empty($_SERVER['HTTP_CLIENT_IP'])) {
$ip = $_SERVER['HTTP_CLIENT_IP'];
}
else if(!empty($_SERVER['HTTP_X_FORWARDED_FOR'])){
$ip = $_SERVER['HTTP_X_FORWARDED_FOR'];
}
else {
$ip = $_SERVER['REMOTE_ADDR'];
}
?>
Getting the country name of the user
We will use the IP address derived above to query for the country. We will send a GET request to http://www.geoplugin.net/json.gp, passing "ip" as the parameter using PHP curl.
The response is a JSON object comprising a lot of data. We are only interested in getting the country name.
<?php
$ch = curl_init();
curl_setopt($ch, CURLOPT_URL, "http://www.geoplugin.net/json.gp?ip=".$ip);
curl_setopt($ch, CURLOPT_HTTPHEADER, array('Content-Type: application/json'));
curl_setopt($ch, CURLOPT_RETURNTRANSFER, TRUE);
curl_setopt($ch, CURLOPT_HEADER, FALSE);
curl_setopt($ch, CURLOPT_SSL_VERIFYPEER, false);
$result = curl_exec($ch);
curl_close($ch);
$country = json_decode($result)->geoplugin_countryName;
?>
In case you want to see all the JSON data returned, just echo the $result variable.
Related article: How to extract and access JSON data in PHP
Getting the user operating system, browser, and browser version
Using the superglobal variable $_SERVER['HTTP_USER_AGENT'] returns all the information we want about the client (ie. user machine and browser).
<?php
echo $_SERVER['HTTP_USER_AGENT'];
?>
My output:
Mozilla/5.0 (X11; Ubuntu; Linux x86_64; rv:92.0) Gecko/20100101 Firefox/92.0 X-Middleton/1
You may decide to store all the above output as it is. In the scope of this tutorial, we break it down into OS as "Ubuntu", browser as "Firefox" and browser version as "92.0".
To do that we will use the PHP explode() function to break its components as arrays and access them using array indexes because we already know the format and their positions in the array.
Using the PHP code below, we will be able to get the value of the three:
<?php
$client = $_SERVER['HTTP_USER_AGENT'];
$operatingsystem = explode(";",$client)[1];
$browser = end(explode(" ",$client));
$browsername = explode("/",$browser)[0];
$browserversion = explode("/",$browser)[1];
?>
Saving the Analytics data
Now we already have all the information we needed about the user. The next step is to save them into the database table we just created.
To avoid multiple duplicate entries in the database, we will only save a user visit to a page once in a day. So, if a user had already visited the page earlier and visits it again, we won't save the second visit to that page.
We will use the user IP address, page URL, and the date to check whether a user had already visited that page earlier, before saving that record in the database.
With the following code, we will be able to save that data.
<?php
$con = mysqli_connect("localhost","dbuser","dbpassword","dbname") or die("Error in database connection");
$today = date("Y-m-d");
if(mysqli_num_rows(mysqli_query($con,"SELECT id FROM analytics WHERE ip_address='$ip' AND page_url='$pageurl' AND entry_time LIKE '%$today%'")) < 1){
$query = $con->prepare("INSERT INTO analytics(page_url,entry_time,ip_address,country,operating_system,browser,browser_version) VALUES(?,?,?,?,?,?,?)");
$query->bind_param('sssssss',$pageurl,$timenow,$ip,$country,$operatingsystem,$browsername,$browserversion);
$query->execute();
$query->close();
}
?>
The above code will insert a new record with all the user information.
Putting it all together
Now let's combine all the above code segments into one working code that you can use directly in your project.
<?php
$con = mysqli_connect("localhost","dbuser","dbpassword","dbname") or die("Error in database connection");
$client = $_SERVER['HTTP_USER_AGENT'];
$operatingsystem = explode(";",$client)[1];
$browser = end(explode(" ",$client));
$browsername = explode("/",$browser)[0];
$browserversion = explode("/",$browser)[1];
$timenow = date("Y-m-d H:i:s");
$today = date("Y-m-d");
if(!empty($_SERVER['HTTP_CLIENT_IP'])) {
$ip = $_SERVER['HTTP_CLIENT_IP'];
}
else if(!empty($_SERVER['HTTP_X_FORWARDED_FOR'])){
$ip = $_SERVER['HTTP_X_FORWARDED_FOR'];
}
else {
$ip = $_SERVER['REMOTE_ADDR'];
}
$ch = curl_init();
curl_setopt($ch, CURLOPT_URL, "http://www.geoplugin.net/json.gp?ip=".$ip);
curl_setopt($ch, CURLOPT_HTTPHEADER, array('Content-Type: application/json'));
curl_setopt($ch, CURLOPT_RETURNTRANSFER, TRUE);
curl_setopt($ch, CURLOPT_HEADER, FALSE);
curl_setopt($ch, CURLOPT_SSL_VERIFYPEER, false);
$result = curl_exec($ch);
curl_close($ch);
$country = json_decode($result)->geoplugin_countryName;
$pageurl = "https://".$_SERVER['HTTP_HOST'].$_SERVER['REQUEST_URI'];
if(mysqli_num_rows(mysqli_query($con,"SELECT id FROM analytics WHERE ip_address='$ip' AND page_url='$pageurl' AND entry_time LIKE '%$today%'")) < 1){
$query = $con->prepare("INSERT INTO analytics(page_url,entry_time,ip_address,country,operating_system,browser,browser_version) VALUES(?,?,?,?,?,?,?)");
$query->bind_param('sssssss',$pageurl,$timenow,$ip,$country,$operatingsystem,$browsername,$browserversion);
$query->execute();
$query->close();
}
?>
You just have to add the above code to all your PHP pages that you want to record visits for.
To make it easier, just create a file, e.g name it as "analytics.php", add the code above, and include it in all your PHP pages. You can achieve this using include(), include_once(), require() or require_once().
Saving the exit time
All the above data gets saved when a user visits a page. Now we need to update the visit record with the exit time when the user leaves the page.
We will only achieve this if a user leaves a page by closing the browser tab or clicks a link to another page.
In such a case, we will use the "beforeunload" jQuery function to trigger a synchronous Ajax request with a post request containing the current page URL when a user is leaving the page. This Ajax request will be sent to a PHP file namely "page-exit.php" and that will use the page URL, user IP address, and the date to update the record that coincides in the database.
jQuery code
<script>
$(window).on('beforeunload', function(){
var pageurl = window.location.href;
$.ajax({
method: "post",
url : "page-exit.php",
data: {
pageurl:pageurl
},
cache:false,
async: false
});
});
</script>
The PHP code
<?php
$con = mysqli_connect("localhost","dbuser","dbpassword","dbname") or die("Error in database connection");
$timenow = date('Y-m-d H:i:s');
$today = date('Y-m-d');
if(isset($_POST["pageurl"])){
$pageurl = $_POST["pageurl"];
if(!empty($_SERVER['HTTP_CLIENT_IP'])) {
$ip = $_SERVER['HTTP_CLIENT_IP'];
}
else if(!empty($_SERVER['HTTP_X_FORWARDED_FOR'])){
$ip = $_SERVER['HTTP_X_FORWARDED_FOR'];
}
else {
$ip = $_SERVER['REMOTE_ADDR'];
}
$query = $con->prepare("UPDATE analytics SET exit_time = ? WHERE ip_address= ? AND page_url= ? AND entry_time LIKE ?");
$query->bind_param('ssss',$timenow,$ip,$pageurl,'%$today%');
$query->execute();
$query->close();
}
?>
With all the above data saved, you can now easily customize and visualize it as you wish through the use of tables, pie charts, line charts, bar graphs, etc.
That's it!
Your Analytics tool is ready.
Conclusion
Getting traffic is the most important achievement for every website. Website owners go the extra mile and do whatever it may take to get as many people as possible to visit their website.
For this reason, tracking and knowing how much traffic a website receives over a certain period of time is very crucial.
There are a number of website analytics tools that work perfectly already in place such as Google Analytics, Awstats, and Microsoft Clarity among others.
For various reasons, maybe out of curiosity, the desire to try out new things, or just to have everything done and work your way, you may want to make your own custom web analytics tool.
In this tutorial, we covered exactly that; how to make your own custom Web Analytics tool using PHP, MySQL, and jQuery Ajax.
It is my hope that you enjoyed the tutorial, and that it was easy to follow along and understand.
