How to Store Visitor Log in MySQL Database using PHP?

 

Tracking user activity on a web application is made easier by visitor log. You can gather information about website visitors, such as their IP address, referrer, browser, and other specifics, and log that information in a database. For geolocation tracking, the database can also store the visitor's nation, latitude, and longitude information.

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;

connection.php

<?php

$servername="localhost";

$username="root";

$password="";

$database="hancie";

$conn=new mysqli($servername, $username, $password, $database );

if(!$conn){

    die ("connection fail" .mysqli_error());

}

?>

log.php

<?php

 

// Include the database configuration file

include_once 'connection.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 = $conn->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'; 
?>

Output