Skip to Content
PHPCODE
how to store ip address in database using php
php code / September 3, 2021

The Visitor Log is used to keep track of user activities on a web application. Users’ IP addresses, referrer URLs, browser information and other facts can be recorded in a database when they access the website. It is possible to log the visitor’s nation, latitude, and longitude information in the database to follow their geolocation.

The logging system may save and track visitor information as well as internal website access information. Using PHP’s $_SERVER variable, you’ll get most of the data. Geolocation data can be obtained by using an API from a third-party service provider (API). Here, we’ll teach you how to use PHP and MySQL to collect visitor information (IP, referrer, browser type, geolocation, etc.) and store it in a database.

Use PHP to provide the following features in this Website Visitor Tracking example script: record visitor activities in a MySQL database.

Database Create Table

The visitor logs must be stored in a database table. By using the following SQL query, you may create a table called visitor logs in your 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;

Configuration of the database (dbConfig.php)

PHP and MySQL are used to connect to the database via dbConfig.php. You’ll need to specify the database host ($dbHost), username ($dbUsername), password ($dbPassword), and name ($dbName) based on your actual database login 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 the in Log (log.php)

In PHP, $_SERVER, a super global variable, is used to access the majority of server and browser related information.

Page URL – The current page URL is obtained by using HTTPS and SERVER PORT and HTTPHOST indices, as well as REQUEST URI and QUERY STRING indices.
Use the HTTP REFERER key for the URL of the referrer. URL of the page that the user-agent came from to get to the present page.
In order to determine the visitor’s IP address, the REMOTE ADDR key is needed.
This key is used to retrieve information about the user agent of the browser. Details about the current request header are provided by the browser.

PHP and MySQL are used to insert visitor logs into the database.

This statement (prepare, bind and execute) inserts log data into the MySQL database using a prepared statement.

<?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(); 
?>

Storing logs in a DB

Store visitor information in the database by incorporating Log script (log.php) into a web page’s HTML code

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

Tracking your location with geolocation software

Third party APIs are required to acquire information about the visitor’s country of origin and geolocation. If you want to acquire the geolocation of an IP address using PHP, you can utilise IP Geolocation API.

In the database, the following information can be kept as a result of the geolocation data provided by the API:

Country Code — The country’s short code.
State/Province – State/Province.
IP address’s latitude and longitude coordinates.

Note:

In this log script, visitors’ data is fetched and stored automatically in the database. To save the visitor’s activity log, you only need to put this script on the web page where it will be stored. With PHP and MySQL, the most valuable information is recorded in the database log, as seen in our example code. However, you can add any other information you like to the database.

PHPCODE © 2023