Storing Visitor Log in the Database with PHP and MySQL

Visitor Log helps to track user activity on the web application. When visitors access the website, you can collect the visitor’s IP address, referrer, browser, and other details, and store them as a log in the database. The visitor’s country, latitude, and longitude info can also be logged in the database for geolocation tracking.

Along with the visitor’s info, the internal access info of the website can be stored and tracked with the logging system. Most of the information will get using the $_SERVER variable in PHP. You can use a third-party API to get the geolocation data of the visitors. In this tutorial, we will show you how to get visitor’s info (IP, referrer, browser, geolocation, etc) and store logs in the database with PHP and MySQL.

In this example Website Visitor Tracking script, we will implement the following functionality to log the visitor’s activity in the MySQL database using PHP.

• Get visitor’s IP address, current page URL, referrer URL, and browser details using PHP $_SERVER variable.
• Store visitor logs in the database with PHP and MySQL.

Create Database Table

A table is required in the database to store the visitor logs. The following SQL creates a visitor_logs table in the MySQL database.

CREATE TABLE `visitor_logs` (
 `id` int(11) NOT NULL AUTO_INCREMENT,
 `page_url` varchar(255) COLLATE utf8_unicode_ci NOT NULL,
 `referrer_url` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
 `user_ip_address` varchar(50) COLLATE utf8_unicode_ci NOT NULL,
 `user_agent` varchar(255) COLLATE utf8_unicode_ci NOT NULL,
 `created` datetime NOT NULL DEFAULT current_timestamp(),
 PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

Database Configuration (dbConfig.php)

The dbConfig.php file is used to connect the database using PHP and MySQL. Specify the database host ($dbHost), username ($dbUsername), password ($dbPassword), and name ($dbName) as per your database credentials.

<?php 
// Database configuration 
$dbHost     = "localhost"; 
$dbUsername = "root"; 
$dbPassword = "root"; 
$dbName     = "codexworld"; 
 
// Create database connection 
$db = new mysqli($dbHost, $dbUsername, $dbPassword, $dbName); 
 
// Check connection 
if ($db->connect_error) { 
    die("Connection failed: " . $db->connect_error); 
}

Visitor Log (log.php)

Most of the server and browser related informations are retrieved with $_SERVER, a super global variable in PHP.

• Current Page URL – HTTPS, SERVER_PORT, HTTP_HOST, REQUEST_URI, and QUERY_STRING indices are used to get the current page URL.
• Referrer URL – HTTP_REFERER key is used. The page URL that referred the user-agent to the current page.
• IP Address – REMOTE_ADDR key is used to get the visitor’s IP address.
• Browser Info – HTTP_USER_AGENT key is used to get the user agent details. The browser details with the current request header.

Insert visitor logs in the database using PHP and MySQL.

• The prepared statement (prepare, bind and execute) is used to insert log data into the MySQL database.

<?php 
 
// Include the database configuration file 
include_once 'dbConfig.php'; 
 
// Get current page URL 
$protocol = ((!empty($_SERVER['HTTPS']) && $_SERVER['HTTPS'] != 'off') || $_SERVER['SERVER_PORT'] == 443) ? "https://" : "http://"; 
$currentURL = $protocol . $_SERVER['HTTP_HOST'] . $_SERVER['REQUEST_URI'] . $_SERVER['QUERY_STRING']; 
 
// Get server related info 
$user_ip_address = $_SERVER['REMOTE_ADDR']; 
$referrer_url = !empty($_SERVER['HTTP_REFERER'])?$_SERVER['HTTP_REFERER']:'/'; 
$user_agent = $_SERVER['HTTP_USER_AGENT']; 
 
// Insert visitor log into database 
$sql = "INSERT INTO visitor_logs (page_url, referrer_url, user_ip_address, user_agent, created) VALUES (?,?,?,?,NOW())"; 
$stmt = $db->prepare($sql); 
$stmt->bind_param("ssss", $currentURL, $referrer_url, $user_ip_address, $user_agent); 
$insert = $stmt->execute(); 
 
?>

Store Logs in Database

Include the Log script (log.php) in the web page to store visitor logs in the database.

<?php 
// Include visitor log script 
include_once 'log.php'; 
?>

Geolocation Tracking

If you want to collect information about the visitor’s country and geolocation, a third-party API is required to use. You can use IP Geolocation API to get the geolocation from the IP address using PHP.

Based on the geolocation data provided by the API, the following information can be stored in the database.

• Country Code – Short code of the country.
• Country Name – Name of the Country.
• Latitude & Longitude – Location of the IP address.

Conclusion

This log script will fetch visitor info and store the logs in the database automatically. You only need to include this script in the web page from which the visitor’s activity log will be stored. As per our example code, the most useful informations are stored in the database log with PHP and MySQL. However, you can insert any other info in the database as per your needs.