PHP – MySQL visitor counter

In this tutorial I’m going to describe how to increment a view counter in a MySQL database. We are going to use PHP with the most simple solution. We want to keep this very simple so we won’t track IP addresses and won’t use cookies to avoid one visitor being counted multiple times. So basically this will be a page load counter, not an individual user counter.
php mysql visit counter

Prepare the MySQL database tables

First we have to create the database tables or add the columns to an existing one to store the information.

Connect with phpMyadmin and open the existing database you want to work with. Click Structure / Add 1 column and click Go. Let the column name be ‘visits’, INT type, length 15, Default 0 (so we can start counting from 0). Leave the remaining fields untouched and hit the Save button.

If you don’t have an existing database, create one with an ‘id’ (int, AUTO_INCREMENT, key) field and the ‘visits’ mentioned above

The PHP code

First we have to connect to the database.

We need two queries. The first one increases the value of the counter, where the id is associated to the specific page. We need to adjust the id for each page, or we can use a PageName/URL column for that according to our needs:

UPDATE Counter SET visits = visits+1 WHERE id=1

The following query retreives the value and stores it in a variable. This will be later displayed in the HTML body seciton.

SELECT visits FROM Counter WHERE id = 1

And finally the full code below:

<?php
    $servername = "localhost";
    $username = "dtbsuser";
    $password = "dtbs#passw01";
    $dbname = "dtbsname";
    $conn = new mysqli($servername, $username, $password, $dbname);

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

    $sql = "UPDATE Counter SET visits = visits+1 WHERE id = 1";
    $conn->query($sql);

    $sql = "SELECT visits FROM Counter WHERE id = 1";
    $result = $conn->query($sql);

    if ($result->num_rows > 0) {
        while($row = $result->fetch_assoc()) {
            $visits = $row["visits"];
        }
    } else {
        echo "no results";
    }
    
    $conn->close();
?>

<!doctype html>  
<html lang="en">
    <head>
        <meta charset="utf-8">
        <meta http-equiv="x-ua-compatible" content="ie=edge">
        <title>Visit counter</title>
    </head>
    <body>
        Visits: <?php print $visits; ?>

    </body>
</html>

You can style the widget to match your website. I think a 7 segment display font fits to this counter.