#1 Store Visitor Activity Log in MySql Database with PHP

In this article, we will learn about the website visitor activity tracking script. User activity on the online application / Website can be tracked using the Visitor Log. You’ll collect the visitor’s IP address, referrer, browser, and other information when they visit the website and store it in a database log.

The country, latitude, and longitude of the visitor can also be stored in the database for geolocation tracking. Along with the visitor’s information, the logging system frequently stores and tracks the website’s interior access information. The $_SERVER variable in PHP will be used to achieve the majority of the knowledge. To get the visitors’ geolocation data, you’ll use a third-party API.

Are you want to get implementation help, or modify or extend the functionality of this script?

A Tutorialswebsite Expert can do it for you.

Hire an Expert

Using PHP and MySQL, we’ll show you how to collect visitor information (IP, referrer, browser, geolocation, and so on) and store logs in a database. We’ll use PHP to log the visitor’s activity in the MySQL database.

Table of Contents

Store Visitor Activity Log in the MySql Database

Step-1: Create Database Tablel

To store the user activity information, a table should be made in the database. The following SQL query creates a visitor_activity_logs table with some essential fields in the MySQL database.


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

CREATE TABLE `visitor_activity_logs` (

`id` int(11) NOT NULL AUTO_INCREMENT,

`user_ip_address` varchar(50) COLLATE utf8_unicode_ci NOT NULL,

`user_agent` varchar(255) COLLATE utf8_unicode_ci NOT NULL,

`page_url` varchar(255) COLLATE utf8_unicode_ci NOT NULL,

`referrer_url` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,

`created_on` datetime NOT NULL DEFAULT current_timestamp(),

PRIMARY KEY (`id`)

) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

Step-2: Create Database Configuration (dbconfig.php)

The dbconfig.php file is used to connect and select the database. Specify the database hostname ($dbHost),DB username ($dbUsername), DB password ($dbPassword), and DB name ($dbName) as per your MySQL database credentials.


<?php
// Database configuration
$host     = "localhost";
$dbuser = "Database Username";
$dbpass = "Database password";
$dbname = "Database Name";
 
// Create database connection
$conn = new mysqli($host, $dbuser, $dbpass, $dbname);
 
// Check connection
if ($conn->connect_error) {
    die("Connection failed: " . $conn->connect_error);
}
?>

<?php

// Database configuration

$host = "localhost";

$dbuser = "Database Username";

$dbpass = "Database password";

$dbname = "Database Name";

// Create database connection

$conn = new mysqli($host, $dbuser, $dbpass, $dbname);

// Check connection

if ($conn->connect_error) {

die("Connection failed: " . $conn->connect_error);

}

Step-3: Create Visitor Activity Log file(user_activity_log.php)

The most important part is gathering information from your visitor browser. PHP has a global variable called $_SERVER that contains several environment variables, including visitor information.

Simply execute the following code to collect all of the information you require:


<?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://"; 

$user_current_url = $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 activity log into database 

$sql = "INSERT INTO visitor_activity_logs (user_ip_address, user_agent, page_url, referrer_url, created_on) VALUES ($user_ip_address, $user_agent,$user_current_url,$user_agent ,NOW())"; 

$insert = $conn->query($sql);
 
?>

<?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://";

$user_current_url = $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 activity log into database

$sql = "INSERT INTO visitor_activity_logs (user_ip_address, user_agent, page_url, referrer_url, created_on) VALUES ($user_ip_address, $user_agent,$user_current_url,$user_agent ,NOW())";

$insert = $conn->query($sql);

In the above code:

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.

Step-4: Store Activity Logs in Database

To store the visitor activity log in the database, we have to create a webpage named index.php and Include the Log script (user_activity_log.php) in the web page.

index.php file


<?php
    include_once 'user_activity_log.php'; 
?>

<!DOCTYPE html>
<html>
<head>
    <meta charset="utf-8">
    <title>How to Store Visitor Activity Log in the MySql Database Using PHP</title>
</head>
<body>
    <div class="container">
        <div class="cw-info-box">
            <p><strong>Visitor Activity Log</strong></p>
            <div class="log-data">
                <p><b>Ip Address</b> : <?php echo $user_ip_address; ?></p>
                <p><b>User Agent</b> : <?php echo $user_agent; ?></p>
                <p><b>Current page url</b> : <?php echo $user_current_url; ?></p>
                <p><b>Referrer url</b> : <?php echo $referrer_url; ?></p>
            </div>
        </div>
    </div>
</body>
</html>

<?php

include_once 'user_activity_log.php';

<!DOCTYPE html>

<html>

<head>

<title>How to Store Visitor Activity Log in the MySql Database Using PHP</title>

</head>

<body>

Visitor Activity Log

Ip Address : <?php echo $user_ip_address; ?>

User Agent : <?php echo $user_agent; ?>

Current page url : <?php echo $user_current_url; ?>

Referrer url : <?php echo $referrer_url; ?>

</div>

</body>

</html>

Also Read: How to Get Current Visitor Location using HTML5 Geolocation API

Are you want to get implementation help, or modify or extend the functionality of this script?

A Tutorialswebsite Expert can do it for you.

Hire an Expert

Wrapping Words

I hope you found this guide helpful and learned How to Store the Visitor Activity Log in the MySql Database Using PHP. If you did, please consider sharing this post on social media and Keep learning!. You can also extend the functionality as per your requirement.

Pradeep Maurya

Pradeep Maurya is the Professional Web Developer & Designer and the Founder of “Tutorials website”. He lives in Delhi and loves to be a self-dependent person. As an owner, he is trying his best to improve this platform day by day. His passion, dedication and quick decision making ability to stand apart from others. He’s an avid blogger and writes on the publications like Dzone, e27.co