How to Store Visitor Log in the Database using PHP and MySQL?

Within this tutorial, we will explore the development of a straightforward web application featuring a central focus on Site Visitor Logs. This article's primary objective is to serve as a valuable resource for PHP programming newcomers and students, offering insights into the implementation of common features to enrich their proficiency and understanding of the PHP programming language. Here, you will find the complete source code for a basic web application that includes a functionality for tracking site visitor activity.

Reasons for Storing Visitor Logs in a Database

Database Storage of Visitor Logs is a commonly featured aspect in various web applications, including Content Management Systems. This feature serves a crucial purpose, enabling website administrators to monitor page views and more. It fulfills several significant roles, such as:

  1. Analytics and Statistics - Enabling website owners and managers to track and analyze visitor behavior effectively.
  2. Security Monitoring - Assisting in identifying suspicious or malicious activities, such as hacking attempts, spam, or unauthorized access.
  3. Performance Monitoring - Providing insights into performance issues, slow-loading pages, or high traffic periods.
  4. Historical Data - Serving as a historical record of website and user activities.

How to Store Visitor Log in the Database using PHP and MySQL?

In this tutorial, I'll supply you with a straightforward website source code that includes a `Visitor Log Storage` feature to help you grasp the process better. Before diving into the coding aspect, make sure you have the following components installed on your local machine if they aren't already:

  • Web Server Software (e.g., XAMPP or WAMP)
  • Code Editor (e.g., Sublime Text, Microsoft Visual Studio Code, or Notepad++)

Once you have installed the Web Server Software, be sure to start or run the `Apache` and `MySQL` services.

Furthermore, the web application's user interface, which I will be presenting, relies on Content Delivery Networks (CDN) for loading the Bootstrap Framework and jQuery. These libraries are essential for enhancing the UI design. Please ensure you have an internet connection when accessing the web application to benefit from these resources. If offline, you may need to download the libraries and update the CSS and Script links accordingly.

Creating the Database

Begin by creating a new MySQL database, which you can name as `dummy_db`. Afterward, add a new table within this database named `visit_logs`. The table should include the following columns as outlined in the MySQL script below:

  1. CREATE TABLE `visit_logs` (
  2. `user_ip` varchar(30) NOT NULL,
  3. `page_url` text NOT NULL,
  4. `reference_url` text NOT NULL,
  5. `user_agent` text NOT NULL,
  6. ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

Store Visitor Log in the Database using PHP and MySQL

Creating the Database Connection Script

Now, it's time to create a new PHP script file and save it as `db-connect.php`. This script file contains the PHP code responsible for establishing the database connection.

  1. <?php
  2. // Hostname
  3. $host = "localhost";
  4. // Username
  5. $uname = "root";
  6. // Password
  7. $pw = "";
  8. // DB name
  9. $db_name = "dummy_db";
  10.  
  11. try{
  12. // Opening Database Connection
  13. $conn = new mysqli($host, $uname, $pw, $db_name);
  14. }catch(Exception $e){
  15. die($e->getMessage());
  16. }
  17.  
  18. ?>

Creating the Visit Log Class

Now, proceed to create another PHP file, which you should name as `visitors-log.class.php`. Within this PHP script file, you will find the class responsible for retrieving the site visitor data and storing visitor data in the database.

  1. <?php
  2. class VisitorLog{
  3. private $db_conn;
  4. private $http_protocol;
  5. private $page_url;
  6. private $reference_url;
  7. private $user_ip;
  8. private $user_agent;
  9.  
  10. function __construct(){
  11. require_once('db-connect.php');
  12. $this->db_conn = $conn;
  13. $server_data = $_SERVER ?? "";
  14. if(!empty($server_data)){
  15. // Setting HTTP Protocol (http:// or http://)
  16. $this->http_protocol = ((!empty($server_data['HTTPS']) && $server_data['HTTPS'] != 'off') || $server_data['SERVER_PORT'] == 443) ? "https://" : "http://";
  17.  
  18. // Get Current URL
  19. $this->page_url = $this->http_protocol . $server_data['HTTP_HOST'] . $server_data['REQUEST_URI'] . $server_data['QUERY_STRING'];
  20.  
  21. // Get Reference URL
  22. $this->reference_url = !empty($_SERVER['HTTP_REFERER'])?$_SERVER['HTTP_REFERER']:'/';
  23. // User Agant
  24. $this->user_agent = $server_data['HTTP_USER_AGENT'];
  25.  
  26. // Get User IP Address
  27. $this->user_ip = $server_data['REMOTE_ADDR'];
  28. }
  29. }
  30. public function log_site_visit(){
  31. // Query Statement
  32. $stmt = $this->db_conn->prepare("INSERT INTO `visit_logs` (user_ip, page_url, reference_url, user_agent) VALUES (?, ?, ?, ?)");
  33. // Binding Insert Values
  34. $stmt->bind_param('ssss', $this->user_ip, $this->page_url, $this->reference_url, $this->user_agent);
  35. // Insert Site Visit Log Data into the database
  36. $save = $stmt->execute();
  37. if($save){
  38. // Do something when page visit data has been saved successfully
  39. // return true;
  40. }else{
  41. // Do something when page visit data has failed to save
  42. // return false;
  43. }
  44. }
  45. function __destruct(){
  46. // Closing Database Connection
  47. $this->db_conn->close();
  48. }
  49. }
  50. ?>

Creating the Website Interface

Next, let's develop the user interface for the website.

Index File

Start by creating a new PHP file named `index.php`. This file encompasses a fusion of PHP and HTML5 scripts, including the requirement of the class file and the navigation bar.

  1. <?php
  2. require_once("visitors-log.class.php");
  3. /**
  4. * Log Site Visit Data
  5. */
  6. $vlClass = new VisitorLog();
  7. $vlClass->log_site_visit();
  8.  
  9. //page
  10. $page = str_replace(["-", "_"], " ",$_GET['page'] ?? "home");
  11. ?>
  12. <!DOCTYPE html>
  13. <html lang="en">
  14. <meta charset="UTF-8">
  15. <meta name="viewport" content="width=device-width, initial-scale=1.0">
  16. <title>Sample Website | <?= ucwords($page) ?> Page</title>
  17. <!-- Bootstrap 5.3 CSS-->
  18. <link rel="stylesheet" href="https://cdn.jsdelivr.net/npm/bootstrap@5.3.2/dist/css/bootstrap.min.css">
  19. <!-- jQuery -->
  20. <script src="https://cdnjs.cloudflare.com/ajax/libs/jquery/3.7.1/jquery.min.js"></script>
  21. <!-- Bootstrap 5.3 JS-->
  22. <script src=" https://cdn.jsdelivr.net/npm/bootstrap@5.3.2/dist/js/bootstrap.bundle.min.js"></script>
  23. </head>
  24. <nav class="navbar navbar-expand-lg bg-body-tertiary">
  25. <div class="container-fluid">
  26. <a class="navbar-brand" href="#">Sample Website</a>
  27. <button class="navbar-toggler" type="button" data-bs-toggle="collapse" data-bs-target="#navbarNav" aria-controls="navbarNav" aria-expanded="false" aria-label="Toggle navigation">
  28. <span class="navbar-toggler-icon"></span>
  29. </button>
  30. <div class="collapse navbar-collapse" id="navbarNav">
  31. <ul class="navbar-nav">
  32. <li class="nav-item">
  33. <a class="nav-link <?= $page == 'home' ? "active" : '' ?>" href="./">Home</a>
  34. </li>
  35. <li class="nav-item">
  36. <a class="nav-link <?= $page == 'about_us' ? "active" : '' ?>" href="./?page=about_us">About Us</a>
  37. </li>
  38. <li class="nav-item">
  39. <a class="nav-link <?= $page == 'contact_us' ? "active" : '' ?>" href="./?page=contact_us">Contact Us</a>
  40. </li>
  41. <li class="nav-item">
  42. <a class="nav-link <?= $page == 'site_visits' ? "active" : '' ?>" href="./?page=site_visits">Visit Logs</a>
  43. </li>
  44. </ul>
  45. </div>
  46. </div>
  47. </nav>
  48. <div class="container-md py-4">
  49. <h4 class="text-center"><strong><?= ucwords($page) ?> Page</strong></h4>
  50. <?php
  51. if($page == "site visits")
  52. include('site-visits.php');
  53. ?>
  54. </div>
  55.  
  56. </body>
  57. </html>

Visit Log Display Page

Finally, let's develop the page that showcases the stored visit logs from the database. This file consists of a combination of both `PHP` and HTML5 scripts. Save this script as `site-visits.php`.

  1. <?php
  2. include("db-connect.php");
  3. ?>
  4. <div class="col-12">
  5. <div class="card rounded-0">
  6. <div class="card-body">
  7. <div class="container-fluid">
  8. <div class="table-responsive">
  9. <table class="table table-striped table-bordered">
  10. <colgroup>
  11. <col width="15%">
  12. <col width="15%">
  13. <col width="20%">
  14. <col width="20%">
  15. <col width="30%">
  16. </colgroup>
  17. <thead>
  18. <tr class="bg-dark text-light">
  19. <th class="text-center">Date/Time</th>
  20. <th class="text-center">IP</th>
  21. <th class="text-center">URL</th>
  22. <th class="text-center">Referer</th>
  23. <th class="text-center">User Agent</th>
  24. </tr>
  25. </thead>
  26. <tbody>
  27. <?php
  28. $visits = $conn->query("SELECT * FROM `visit_logs` order by abs(unix_timestamp(`date_created`)) desc");
  29. if($visits->num_rows > 0):
  30. ?>
  31. <?php while($row = $visits->fetch_assoc()): ?>
  32. <tr>
  33. <td><?= date("M d, Y g:i:s A", strtotime($row['date_created'])) ?></td>
  34. <td><?= $row['user_ip'] ?></td>
  35. <td><?= $row['page_url'] ?></td>
  36. <td><?= $row['reference_url'] ?></td>
  37. <td><?= $row['user_agent'] ?></td>
  38. </tr>
  39. <?php endwhile; ?>
  40. <?php else: ?>
  41. <tr>
  42. <th class="text-center" colspan="5">No Data</th>
  43. </tr>
  44. <?php endif; ?>
  45. </tbody>
  46. </table>
  47. </div>
  48. </div>
  49. </div>
  50. </div>
  51. </div>
  52. <?php
  53. $conn->close();
  54. ?>

The provided source code will generate the results as shown in the following images:

Store Visitor Log in the Database using PHP and MySQL

Store Visitor Log in the Database using PHP and MySQL

Store Visitor Log in the Database using PHP and MySQL

Store Visitor Log in the Database using PHP and MySQL

And there you have it! I hope this Storing Visitors Log in the Database using PHP and MySQL Tutorial will help you with your current project or you'll find this useful for your future PHP projects. Explore more on this website for more Free Source Codes, Tutorials, and Articles covering various programming languages.

Happy Coding =)

Add new comment