How to Create Website Visitor Counter in PHP and MySQL

Being able to count the number of visitors on each web page of your website will definitely help you in multiple ways. The most important one is that it allows you to understand what people are looking for on your website. In this tutorial, I’ll guide you about how you can add a website visitor counter on your own website in a step by step manner.

I’ll be using PHP and MySQL for the basic functionality but also use a little bit HTML5 code to depict a real-world scenario.

Just in case you are new to the Web Development field and want to earn money by creating professional websites. Then, I would highly recommend you to take these courses from Udemy.

• The Complete 2021 PHP Full Stack Web Developer Bootcamp
• Build a Social Network from Scratch: JavaScript + PHP + MySQL
• Create a Netflix clone from Scratch: JavaScript + PHP + MySQL
• Make a Spotify Clone from Scratch: JavaScript + PHP + MySQL

Anyways, let’s take a look at what you will be learning in this tutorial.

Table of Contents

• Basic functionality of this project
• Setup database and tables for website visitor counter
• Files and folder used in this tutorial
• Connect to database
• Create PHP functions to handle unique visitor counter
• Create few web pages to count visits
• Download website visitor counter

Basic functionality of this project

• It allows us to count total website views.
• It allows us to count number of visitors on a single web page.
• It makes use of visitor’s IP address to only count unique views.

Setup database and tables for website visitor counter

Basically we need a database for two purposes. First of all we will use it to store data for each web page of our website. Secondly we have to keep a record of previous visitors which help us figure out whether the new visitor is unique or not.

Let’s open your phpMyAdmin and create a database named “website_visitor_counter”. Or simply use the below SQL query.

CREATE DATABASE website_visitor_counter;

Now move on and add two tables “pages” and “page_views” in the newly created database. You can make use of the below SQL queries.

CREATE TABLE pages
(
  id INTEGER UNSIGNED NOT NULL AUTO_INCREMENT,
  total_views INTEGER UNSIGNED NOT NULL,
  
  PRIMARY KEY (id)
);

CREATE TABLE page_views
(
  visitor_ip VARCHAR(255) NOT NULL,
  page_id INTEGER UNSIGNED NOT NULL,
  
  FOREIGN KEY (page_id) REFERENCES pages(id) ON DELETE CASCADE ON UPDATE CASCADE
);

Use of each database table:-

• pages – This table is used to store total views of each web page.
• page_views – The main purpose of this database table is to maintain a record of every unique visit because we will need it in future to check whether the new visitor is unique or not.

In this tutorial we will be counting hits on three web pages. So, let’s add three rows in the “pages” table (one row for each web page). Use the below SQL INSERT query.

INSERT INTO pages (total_views)
VALUES (0),(0),(0);

Files and folder used in this tutorial

• includes – (This folder is used to group files that are included in other files)
  • db_connect.php – (This file is used to create database connection)
  • functions.php – (This file is used to group all user-defined PHP functions)
  • header.php – (This file contains the header of web page)
  • footer.php – (This file contains the footer of web page)
• index.php – (Main page of website, only used to display total website views)
• page_1.php – (Contains code for first web page)
• page_2.php – (Contains code for second web page)
• page_3.php – (Contains code for third web page)

Create these files and folder before proceeding to next step.

Connect to database

Connecting to a MySQL database using PHP is a really simple and straightforward procedure. Copy/Paste the below PHP code inside your “db_connect.php” file and save it.

db_connect.php

<?php
$db_host = "localhost";               // Database Host
$db_user = "root";                    // Database User
$db_pass = "";                        // Database Password
$db_name = "website_visitor_counter"; // Database Name

$conn = mysqli_connect($db_host, $db_user, $db_pass, $db_name); // Connect to Database

if(!$conn) // Check connection
{
  die("Connection failed: " . mysqli_connect_error()); // Display error if not connected
}
?>

A point to be noted is that you may need to change the values of above PHP variables according to your specific environment.

Create PHP functions to handle unique visitor counter

Now it’s time to take a look at the core part of this project where we will be creating three PHP functions to define the logic and handle the overall functionality of a website hit counter.

Here’s the contents of our “functions.php” file.

functions.php

<?php
function total_views($conn, $page_id = null)
{
  if($page_id === null)
  {
    // count total website views
    $query = "SELECT sum(total_views) as total_views FROM pages";
    $result = mysqli_query($conn, $query);
    
    if(mysqli_num_rows($result) > 0)
    {
      while($row = $result->fetch_assoc())
      {
        if($row['total_views'] === null)
        {
          return 0;
        }
        else
        {
          return $row['total_views'];
        }
      }
    }
    else
    {
      return "No records found!";
    }
  }
  else
  {
    // count specific page views
    $query = "SELECT total_views FROM pages WHERE id='$page_id'";
    $result = mysqli_query($conn, $query);
    
    if(mysqli_num_rows($result) > 0)
    {
      while($row = $result->fetch_assoc())
      {
        if($row['total_views'] === null)
        {
          return 0;
        }
        else
        {
          return $row['total_views'];
        }
      }
    }
    else
    {
      return "No records found!";
    }
  }
}



function is_unique_view($conn, $visitor_ip, $page_id)
{
  $query = "SELECT * FROM page_views WHERE visitor_ip='$visitor_ip' AND page_id='$page_id'";
  $result = mysqli_query($conn, $query);
  
  if(mysqli_num_rows($result) > 0)
  {
    return false;
  }
  else
  {
    return true;
  }
}



function add_view($conn, $visitor_ip, $page_id)
{
  if(is_unique_view($conn, $visitor_ip, $page_id) === true)
  {
    // insert unique visitor record for checking whether the visit is unique or not in future.
    $query = "INSERT INTO page_views (visitor_ip, page_id) VALUES ('$visitor_ip', '$page_id')";
    
    if(mysqli_query($conn, $query))
    {
      // At this point unique visitor record is created successfully. Now update total_views of specific page.
      $query = "UPDATE pages SET total_views = total_views + 1 WHERE id='$page_id'";
      
      if(!mysqli_query($conn, $query))
      {
        echo "Error updating record: " . mysqli_error($conn);
      }
    }
    else
    {
      echo "Error inserting record: " . mysqli_error($conn);
    }
  }
}
?>

Explanation of above created PHP functions:

• total_views($conn, $page_id = null) – This function is used to retrieve total views of website or a specific page in a website.
• is_unique_view($conn, $visitor_ip, $page_id) – This function takes the visitor’s IP address and ID of a page to check whether the visitor has already viewed the page or not.
• add_view($conn, $visitor_ip, $page_id) – If the visitor is unique then this function will add his/her record inside the “page_views” table and also increment the “total_views” column of visited web page by one inside “pages” table.

Create few web pages to count visits

In this section we will be creating four web pages to count unique visitors on our website. The files used for these web pages are as follow:

• index.php
• page_1.php
• page_2.php
• page_3.php

Also remember that I’ve used two more files “header.php” and “footer.php” just to separate the header and footer section of our web pages. This way we will be able to reuse same code in multiple files and it makes it very easy to edit as we just need to modify one file.

Basically the “header.php” file contains few links to navigate on different web pages of this project. Here’s the code for this file.

header.php

<!DOCTYPE html>
<html>
  <head>
    <title>Website Visitor Counter</title>
  </head>
  
  <body>
    <header>
      <ul>
        <li><a href="index.php">Main Page</a></li>
        <li><a href="page_1.php">Page 1</a></li>
        <li><a href="page_2.php">Page 2</a></li>
        <li><a href="page_3.php">Page 3</a></li>
      </ul>
    </header>

“footer.php” only contains few ending tags of our web pages. Check the code below:

footer.php

  </body>
</html>

“index.php” file is just like the main/home page of a website. We will only use it to display total website views. Here’s the code for this file.

index.php

<?php
require_once('includes/db_connect.php'); // Database connection file
require_once('includes/functions.php');  // PHP functions file
?>

<!-- header file -->
<?php require_once('includes/header.php'); ?>

<div>
  <?php
  $total_website_views = total_views($conn); // Returns total website views
  echo "<strong>Total Website Views:</strong> " . $total_website_views;
  ?>
</div>

<div style="color: red;">Note: This page only displays the total views of website.<div>

<!-- footer file -->
<?php require_once('includes/footer.php'); ?>

Does this code looks complex? Don’t worry! Let me explain…

• In first PHP block we are including two PHP files “db_connect.php” and “functions.php”.
• After that we include the header of our web page.
• Next we created a <div></div> element where we called the “total_views($conn)” function. You may have noticed that we have only provided one database connection variable as parameter, it’s because the second “page_id” parameter is optional. This way the function will return the total website views and store it inside the specified variable. After that we are outputting the value of that variable on screen.
• On next line I’ve simply displayed a note.
• At last the “footer.php” file is included in the web page.

“page_1.php”, “page_2.php” and “page_3.php” are like other web pages on a website. So, we are going to track them separately just to show you the correct method of how you should implement website visitor counter on your personal or business website.

page_1.php

<?php
require_once('includes/db_connect.php'); // Database connection file
require_once('includes/functions.php');  // PHP functions file

$page_id = 1;
$visitor_ip = $_SERVER['REMOTE_ADDR']; // stores IP address of visitor in variable

add_view($conn, $visitor_ip, $page_id);
?>

<!-- header file -->
<?php require_once('includes/header.php'); ?>

<div>
  <?php
  $total_page_views = total_views($conn, $page_id); // Returns total views of this page
  echo "<strong>Total Views of this Page:</strong> " . $total_page_views;
  ?>
</div>

<!-- footer file -->
<?php require_once('includes/footer.php'); ?>

page_2.php

<?php
require_once('includes/db_connect.php'); // Database connection file
require_once('includes/functions.php');  // PHP functions file

$page_id = 2;
$visitor_ip = $_SERVER['REMOTE_ADDR']; // stores IP address of visitor in variable

add_view($conn, $visitor_ip, $page_id);
?>

<!-- header file -->
<?php require_once('includes/header.php'); ?>

<div>
  <?php
  $total_page_views = total_views($conn, $page_id); // Returns total views of this page
  echo "<strong>Total Views of this Page:</strong> " . $total_page_views;
  ?>
</div>

<!-- footer file -->
<?php require_once('includes/footer.php'); ?>

page_3.php

<?php
require_once('includes/db_connect.php'); // Database connection file
require_once('includes/functions.php');  // PHP functions file

$page_id = 3;
$visitor_ip = $_SERVER['REMOTE_ADDR']; // stores IP address of visitor in variable

add_view($conn, $visitor_ip, $page_id);
?>

<!-- header file -->
<?php require_once('includes/header.php'); ?>

<div>
  <?php
  $total_page_views = total_views($conn, $page_id); // Returns total views of this page
  echo "<strong>Total Views of this Page:</strong> " . $total_page_views;
  ?>
</div>

<!-- footer file -->
<?php require_once('includes/footer.php'); ?>

“page_1.php”, “page_2.php” and “page_3.php” are almost similar to the “index.php” file with few exceptions.

• We are using a “$page_id” variable to store the ID of that specific page, we got this ID from the database table “pages”.
• After that we are using “$_SERVER[‘REMOTE_ADDR’]” to get the IP address of visitor.
• Now we simply pass the above two variables to our “add_view($conn, $visitor_ip, $page_id)” function.
• We also pass the “$page_id” variable to “total_views($conn, $page_id)” function, which will return total unique views of a specific web page.

Download website visitor counter

Download