Manuals

Comprehensive guide on creating a simple website analytics system

Comprehensive guide on creating a simple website analytics system using PHP, HTML, CSS, JavaScript, and MySQL. This example provides a basic framework that you can expand upon to suit your specific needs.

I. Database Setup (MySQL)

First, we need a database to store the visitor data. Open your MySQL client (e.g., phpMyAdmin, MySQL Workbench) and execute the following SQL commands:

CREATE DATABASE IF NOT EXISTS website_analytics;

USE website_analytics;

CREATE TABLE IF NOT EXISTS `visits` (
  `id` INT(11) NOT NULL AUTO_INCREMENT,
  `visit_time` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  `ip_address` VARCHAR(50) DEFAULT NULL,
  `page_url` VARCHAR(255) DEFAULT NULL,
  `user_agent` VARCHAR(255) DEFAULT NULL,
  `referrer` VARCHAR(255) DEFAULT NULL,
  PRIMARY KEY (`id`)
);

CREATE TABLE IF NOT EXISTS `browsers` (
  `id` INT(11) NOT NULL AUTO_INCREMENT,
  `user_agent` VARCHAR(255) NOT NULL,
  `browser_name` VARCHAR(50) NOT NULL,
  `browser_version` VARCHAR(50) DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `user_agent` (`user_agent`)
);

CREATE TABLE IF NOT EXISTS `os` (
  `id` INT(11) NOT NULL AUTO_INCREMENT,
  `user_agent` VARCHAR(255) NOT NULL,
  `os_name` VARCHAR(50) NOT NULL,
  `os_version` VARCHAR(50) DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `user_agent` (`user_agent`)
);

CREATE TABLE IF NOT EXISTS `devices` (
  `id` INT(11) NOT NULL AUTO_INCREMENT,
  `user_agent` VARCHAR(255) NOT NULL,
  `device_type` VARCHAR(50) NOT NULL,  -- 'desktop', 'mobile', 'tablet', etc.
  PRIMARY KEY (`id`),
  UNIQUE KEY `user_agent` (`user_agent`)
);


CREATE TABLE IF NOT EXISTS `locations` (
  `id` INT(11) NOT NULL AUTO_INCREMENT,
  `ip_address` VARCHAR(50) NOT NULL,
  `country_code` VARCHAR(2) DEFAULT NULL,
  `country_name` VARCHAR(100) DEFAULT NULL,
  `region_code` VARCHAR(2) DEFAULT NULL,
  `region_name` VARCHAR(100) DEFAULT NULL,
  `city` VARCHAR(100) DEFAULT NULL,
  `latitude` DECIMAL(10, 8) DEFAULT NULL,
  `longitude` DECIMAL(10, 8) DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `ip_address` (`ip_address`)
);

This creates a database named website_analytics and a table named visits to store visit data. browsers, os, devices and locations tables are used for storing information parsed from the user_agent and ip_address fields.

II. PHP Backend (tracking.php)

Create a PHP file named tracking.php on your server. This script will handle recording the visitor data. You’ll need to configure it with your MySQL connection details.

<?php

// Database credentials
$host = "localhost";
$username = "your_username";
$password = "your_password";
$database = "website_analytics";

// Establish database connection
$conn = new mysqli($host, $username, $password, $database);

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

// Get visitor data
$ip_address = $_SERVER['REMOTE_ADDR'];
$page_url = $_SERVER['HTTP_REFERER']; // URL of the page where the request originated
if (isset($_POST['current_url'])) {
    $current_url = $_POST['current_url']; // URL of the current page
} else {
    $current_url = $_SERVER['REQUEST_URI']; // Default to the server URI
}
$user_agent = $_SERVER['HTTP_USER_AGENT'];
$referrer = $_SERVER['HTTP_REFERER'];


// Function to sanitize data (prevent SQL injection)

function sanitize($data) {
  global $conn;
  return mysqli_real_escape_string($conn, trim($data));
}

// Sanitize data
$ip_address = sanitize($ip_address);
$page_url = sanitize($page_url);
$current_url = sanitize($current_url);
$user_agent = sanitize($user_agent);
$referrer = sanitize($referrer);



// --- Location Data (Requires a GeoIP Service/Database) ---
function record_location($conn, $ip_address) {
    // Check If the IP is already in the database
    $sql = "SELECT id FROM locations WHERE ip_address = '$ip_address'";
    $result = $conn->query($sql);

    if ($result->num_rows > 0) {
        // Ip already present in location database
    } else {
      // Example utilizing the geoip lookup service ip-api.com
      $geo_url = "http://ip-api.com/json/" . urlencode($ip_address);
      $geo_data = @file_get_contents($geo_url);

      if ($geo_data !== FALSE) {
          $geo_data = json_decode($geo_data, true);

          if ($geo_data['status'] === 'success') {
            $country_code = sanitize($geo_data['countryCode']);
            $country_name = sanitize($geo_data['country']);
            $region_code = sanitize($geo_data['region']);
            $region_name = sanitize($geo_data['regionName']);
            $city = sanitize($geo_data['city']);
            $latitude = sanitize($geo_data['lat']);
            $longitude = sanitize($geo_data['lon']);

            $sql = "INSERT INTO locations (ip_address, country_code, country_name, region_code, region_name, city, latitude, longitude)
              VALUES ('$ip_address', '$country_code', '$country_name', '$region_code', '$region_name', '$city', '$latitude', '$longitude')";

            if ($conn->query($sql) !== TRUE) {
                error_log("Error inserting location data: " . $conn->error);  // Log the error
              }
          } else {
            error_log("GeoIP lookup failed for IP: " . $ip_address . " - " . $geo_data['message']); // Log the failure
          }
      } else {
        error_log("Failed to fetch GeoIP data for IP: " . $ip_address); // Log the failure of fetching
      }
    }
}


// Function to parse User Agent for browser and OS details
function parse_user_agent($user_agent) {
    $browser = [
        'name' => 'Unknown',
        'version' => 'Unknown'
    ];
    $os = [
        'name' => 'Unknown',
        'version' => 'Unknown'
    ];
    $device = 'Unknown';


    // Browser Detection
    if (preg_match('/MSIE (\d+\.\d+);/', $user_agent, $matches)) {
        $browser['name'] = 'Internet Explorer';
        $browser['version'] = $matches[1];
    } elseif (preg_match('/Firefox\/(\d+\.\d+)/', $user_agent, $matches)) {
        $browser['name'] = 'Firefox';
        $browser['version'] = $matches[1];
    } elseif (preg_match('/Chrome\/(\d+\.\d+)/', $user_agent, $matches)) {
        $browser['name'] = 'Chrome';
        $browser['version'] = $matches[1];
    } elseif (preg_match('/Safari\/(\d+\.\d+)/', $user_agent, $matches)) {
        $browser['name'] = 'Safari';
        $browser['version'] = $matches[1];
    } elseif (preg_match('/Opera\/(\d+\.\d+)/', $user_agent, $matches)) {
        $browser['name'] = 'Opera';
        $browser['version'] = $matches[1];
    } elseif (strpos($user_agent, 'Edge') !== false) {
        $browser['name'] = 'Edge';
        preg_match('/Edge\/(\d+\.\d+)/', $user_agent, $matches);
        if (isset($matches[1])) {
            $browser['version'] = $matches[1];
        }
    }

    // OS Detection
    if (preg_match('/Windows NT (\d+\.\d+)/', $user_agent, $matches)) {
        $os['name'] = 'Windows';
        $os['version'] = $matches[1];
    } elseif (preg_match('/(iPhone|iPad|iPod)/', $user_agent)) {
        $os['name'] = 'iOS';
        preg_match('/OS (\d+_\d+)/', $user_agent, $matches);
          if (isset($matches[1])) {
            $os['version'] = str_replace('_', '.', $matches[1]);
        }
    } elseif (preg_match('/Android (\d+\.\d+)/', $user_agent, $matches)) {
        $os['name'] = 'Android';
        $os['version'] = $matches[1];
    } elseif (strpos($user_agent, 'Mac OS X') !== false) {
        $os['name'] = 'macOS';
        preg_match('/Mac OS X (\d+_\d+)/', $user_agent, $matches);
        if (isset($matches[1])) {
            $os['version'] = str_replace('_', '.', $matches[1]);
        }
    } elseif (strpos($user_agent, 'Linux') !== false) {
        $os['name'] = 'Linux';
        $os['version'] = 'Unknown';
    }


    // Device Type Detection
    $device = 'desktop'; //Default
    if (preg_match('/(iPhone|iPad|iPod|Android)/', $user_agent)) {
        $device = 'mobile';
        if (strpos($user_agent, 'iPad') !== false) {
            $device = 'tablet';
        }
    }


    return [
        'browser' => $browser,
        'os' => $os,
        'device' => $device
    ];
}





function record_parsed_data($conn, $user_agent) {
    $parsed_data = parse_user_agent($user_agent);
    $browser = $parsed_data['browser'];
    $os = $parsed_data['os'];
    $device = $parsed_data['device'];

    // Browser
    $sql = "SELECT id FROM browsers WHERE user_agent = '$user_agent'";
    $result = $conn->query($sql);
    if ($result->num_rows == 0) {
        $browser_name = sanitize($browser['name']);
        $browser_version = sanitize($browser['version']);
        $sql = "INSERT INTO browsers (user_agent, browser_name, browser_version) VALUES ('$user_agent', '$browser_name', '$browser_version')";
        $conn->query($sql);   //No error checking for brevity, but you should add it
    }

    // OS
    $sql = "SELECT id FROM os WHERE user_agent = '$user_agent'";
    $result = $conn->query($sql);
    if ($result->num_rows == 0) {
        $os_name = sanitize($os['name']);
        $os_version = sanitize($os['version']);
        $sql = "INSERT INTO os (user_agent, os_name, os_version) VALUES ('$user_agent', '$os_name', '$os_version')";
        $conn->query($sql);  //No error checking for brevity, but you should add it
    }


    //Device
    $sql = "SELECT id FROM devices WHERE user_agent = '$user_agent'";
    $result = $conn->query($sql);
    if ($result->num_rows == 0) {
        $device_type = sanitize($device);
        $sql = "INSERT INTO devices (user_agent, device_type) VALUES ('$user_agent', '$device_type')";
        $conn->query($sql);  //No error checking for brevity, but you should add it
    }

}




// Insert visit data into the database
$sql = "INSERT INTO visits (ip_address, page_url, user_agent, referrer) VALUES ('$ip_address', '$current_url', '$user_agent', '$referrer')";

if ($conn->query($sql) === TRUE) {
    // Data inserted successfully

    // Record location using IP address
    record_location($conn, $ip_address);

    // Parse and record browser, OS, and device data
    record_parsed_data($conn, $user_agent);  //Record browser and os only  one time using the useragent as unique key




} else {
    error_log("Error inserting visit data: " . $conn->error); // Log the error
}


// Close connection
$conn->close();

// Return a 204 No Content response to avoid interfering with page content. It's an accepted standard to tracking
http_response_code(204);
?>

Important considerations and improvements for the PHP script:

Security: Proper sanitization and validation are crucial to prevent SQL injection. The sanitize function attempts to address this, but you should use prepared statements for robust protection. Also escape output when displaying in analytics to prevent XSS.
Error Handling: The code includes basic error logging. Implement more robust error handling, including checking for database query errors and logging them. Examine the database logs.
Concurrency: If you expect a high volume of traffic, consider using a message queue (e.g., Redis, RabbitMQ) to offload the processing of analytics data. This prevents the tracking process from slowing down your website.
IP Address Anonymization: To comply with privacy regulations (like GDPR), consider anonymizing IP addresses before storing them. You can truncate the last octet of the IP address.
User Tracking (Cookies): You can use cookies to track unique visitors across multiple page views. Store a unique identifier in a cookie and associate it with the visit data. Be sure to comply with cookie consent regulations.
Caching: Cache the results of the User Agent parsing and the GeoIP lookup for faster performance.
GDPR and other Considerations: Consider providing a mechanism for users to opt-out of tracking.

III. JavaScript Client (tracking.js)

Create a JavaScript file named tracking.js and include it in your website’s pages (ideally just before the closing </body> tag).

(function() {
  // Get the current URL
  var currentURL = window.location.href;

  // Create a new XMLHttpRequest object
  var xhr = new XMLHttpRequest();

  // Configure the request
  xhr.open("POST", "/tracking.php", true); // Replace "/tracking.php" with the actual path to your PHP script
  xhr.setRequestHeader("Content-Type", "application/x-www-form-urlencoded");

  // Define what happens on successful data submission
  xhr.onload = function() {
    if (xhr.status == 204) {
       // Request was successful, no content to handle.  Optional handling.
    } else {
      console.error("Tracking request failed with status: " + xhr.status);
    }
  };

  // Define what happens in case of error
  xhr.onerror = function() {
    console.error("Tracking request failed: Network error.");
  };

  // Send the request with the current URL as data
  xhr.send("current_url=" + encodeURIComponent(currentURL));
})();

IV. HTML Integration

Include the tracking.js file in your HTML pages. Place the script tag just before the closing </body> tag:

<!DOCTYPE html>
<html>
<head>
  <title>Your Website</title>
  <!-- Your other head elements -->
</head>
<body>

  <!-- Your website content -->

  <script src="/tracking.js"></script>  <!-- Adjust the path as needed -->
</body>
</html>

V. CSS (Optional)

There’s no specific CSS required for the tracking itself. However, when you build your analytics dashboard, you’ll use CSS to style the data display.

VI. Analytics Dashboard (analytics.php)

Create a PHP file (e.g., analytics.php) to display the collected data. This dashboard will query the visits table and present the data in a user-friendly way. This is where your CSS will be used to style the output.

<!-- analytics.php -->
<!DOCTYPE html>
<html>
<head>
  <title>Website Analytics</title>
  <style>
    /* Basic CSS Styling for the Dashboard */    body { font-family: sans-serif; }
    table { border-collapse: collapse; width: 100%; }
    th, td { border: 1px solid #ddd; padding: 8px; text-align: left; }
    th { background-color: #f2f2f2; }
  </style>
</head>
<body>

<h1>Website Analytics Dashboard</h1>

<?php

// Database credentials (same as tracking.php)
$host = "localhost";
$username = "your_username";
$password = "your_password";
$database = "website_analytics";

// Establish database connection
$conn = new mysqli($host, $username, $password, $database);

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


// --- Basic Statistics ---
$total_visits_query = "SELECT COUNT(*) AS total_visits FROM visits";
$total_visits_result = $conn->query($total_visits_query);
$total_visits = $total_visits_result->fetch_assoc()['total_visits'];

echo "<p><strong>Total Visits:</strong> " . $total_visits . "</p>";



// --- Visits Table ---
echo "<h2>Recent Visits</h2>";
echo "<table>";
echo "<thead><tr><th>Time</th><th>IP Address</th><th>Page URL</th><th>User Agent</th><th>Referrer</th></tr></thead>";
echo "<tbody>";

$sql = "SELECT visit_time, ip_address, page_url, user_agent, referrer FROM visits ORDER BY visit_time DESC LIMIT 20"; // Show the latest 20 visits
$result = $conn->query($sql);

if ($result->num_rows > 0) {
    while($row = $result->fetch_assoc()) {
        echo "<tr>";
        echo "<td>" . htmlspecialchars($row["visit_time"]) . "</td>";
        echo "<td>" . htmlspecialchars($row["ip_address"]) . "</td>";
        echo "<td>" . htmlspecialchars($row["page_url"]) . "</td>";
        echo "<td>" . htmlspecialchars($row["user_agent"]) . "</td>";
        echo "<td>" . htmlspecialchars($row["referrer"]) . "</td>";
        echo "</tr>";
    }
} else {
    echo "<tr><td colspan='5'>No visits recorded yet.</td></tr>";
}

echo "</tbody>";
echo "</table>";




// --- Browser Statistics ---
echo "<h2>Browser Statistics</h2>";
echo "<table>";
echo "<thead><tr><th>Browser</th><th>Count</th></tr></thead>";
echo "<tbody>";

$sql = "SELECT b.browser_name, COUNT(*) AS count FROM visits v JOIN browsers b ON v.user_agent = b.user_agent GROUP BY b.browser_name ORDER BY count DESC";
$result = $conn->query($sql);

if ($result->num_rows > 0) {
    while($row = $result->fetch_assoc()) {
        echo "<tr>";
        echo "<td>" . htmlspecialchars($row["browser_name"]) . "</td>";
        echo "<td>" . htmlspecialchars($row["count"]) . "</td>";
        echo "</tr>";
    }
} else {
    echo "<tr><td colspan='2'>No browser data available.</td></tr>";
}

echo "</tbody>";
echo "</table>";


// --- Operating System Statistics ---
echo "<h2>Operating System Statistics</h2>";
echo "<table>";
echo "<thead><tr><th>Operating System</th><th>Count</th></tr></thead>";
echo "<tbody>";

$sql = "SELECT o.os_name, COUNT(*) AS count FROM visits v JOIN os o ON v.user_agent = o.user_agent GROUP BY o.os_name ORDER BY count DESC";
$result = $conn->query($sql);

if ($result->num_rows > 0) {
    while($row = $result->fetch_assoc()) {
        echo "<tr>";
        echo "<td>" . htmlspecialchars($row["os_name"]) . "</td>";
        echo "<td>" . htmlspecialchars($row["count"]) . "</td>";
        echo "</tr>";
    }
} else {
    echo "<tr><td colspan='2'>No OS data available.</td></tr>";
}

echo "</tbody>";
echo "</table>";



// --- Device Type Statistics ---
echo "<h2>Device Type Statistics</h2>";
echo "<table>";
echo "<thead><tr><th>Device Type</th><th>Count</th></tr></thead>";
echo "<tbody>";

$sql = "SELECT d.device_type, COUNT(*) AS count FROM visits v JOIN devices d ON v.user_agent = d.user_agent GROUP BY d.device_type ORDER BY count DESC";
$result = $conn->query($sql);

if ($result->num_rows > 0) {
    while($row = $result->fetch_assoc()) {
        echo "<tr>";
        echo "<td>" . htmlspecialchars($row["device_type"]) . "</td>";
        echo "<td>" . htmlspecialchars($row["count"]) . "</td>";
        echo "</tr>";
    }
} else {
    echo "<tr><td colspan='2'>No Device data available.</td></tr>";
}

echo "</tbody>";
echo "</table>";



// --- Geographic Statistics (Basic Example) ---
echo "<h2>Visits by Country</h2>";
echo "<table>";
echo "<thead><tr><th>Country</th><th>Count</th></tr></thead>";
echo "<tbody>";

$sql = "SELECT l.country_name, COUNT(*) AS count FROM visits v JOIN locations l ON v.ip_address = l.ip_address GROUP BY l.country_name ORDER BY count DESC";
$result = $conn->query($sql);

if ($result->num_rows > 0) {
    while($row = $result->fetch_assoc()) {
        echo "<tr>";
        echo "<td>" . htmlspecialchars($row["country_name"]) . "</td>";
        echo "<td>" . htmlspecialchars($row["count"]) . "</td>";
        echo "</tr>";
    }
} else {
    echo "<tr><td colspan='2'>No Country data available.</td></tr>";
}

echo "</tbody>";
echo "</table>";



// Close connection
$conn->close();

?>

</body>
</html>

Explanation of analytics.php:

Database Connection: Establishes a MySQL connection using the same credentials as tracking.php.
Basic Statistics: Queries the visits table to calculate and display:
- Total number of visits
- (You can add other stats like unique visitors, average time on site, etc.)
Recent Visits Table: Retrieves the most recent visits from the visits table and displays them in an HTML table. htmlspecialchars is very important for security.
Browser Statistics: Queries the database to count visits by browser type (using the browsers table).
Operating System Statistics: Queries to generate counts by operating system (using the os table).
Device Type Statistics: Queries to generate counts by device type (using the devices table).
Geographic Statistics: Queries to count visits by country.
HTML Structure: Wraps the data in a basic HTML structure with CSS styling for presentation.

VII. Key Improvements and Further Development

More Detailed Analytics Dashboard:
- Implement date range filters for the analytics.
- Add charts and graphs using a library like Chart.js for visual representation.
- Implement session tracking (using cookies and server-side sessions).
- Track user behavior (e.g., clicks, form submissions, scrolling).
- Add more geographic reporting (region, city).
Performance Optimization:
- Use indexes on your database tables to speed up queries.
- Implement caching mechanisms to reduce database load.
- Offload data processing to a background process (e.g., using cron jobs).
Security: Consistently use prepared statements to eliminate SQL injection risks. Escape every output for HTML context.
Maintainability:
- Structure your code into functions and classes for better organization.
- Use a templating engine for the analytics dashboard to separate logic from presentation.
Asynchronous Tracking: Use the navigator.sendBeacon() API to send tracking data even when the user navigates away from the page. This is more reliable than standard XMLHttpRequest for critical tracking events.

This provides a solid foundation. Remember to adapt the code, especially the database interaction and user agent parsing, to your specific site structure and analytical goals. Pay close attention to security and privacy considerations as you expand the system.

Victoria

Im just a girl who hanging around with her friends ;)