When people talk about "analytics," they usually imagine complex machine learning models or massive data warehouses. But at its core, a backend for an analytics dashboard is just about two things: efficiently recording events and aggregating those events into something a human can actually understand.

For this lesson, we're going to build a simple page-view tracker. I want a system that records which page a user visited and then allows us to query the "Top 5 Most Visited Pages" for the current day. It's a classic pattern you'll see in almost every internal admin tool.

Capturing the Page Hits

First, we need a way to log the event. I'm going to keep the database schema lean: just a page_views table with a page_path (string) and a viewed_at (timestamp). We don't need a primary key for the logic, but we'll keep one for database health.

// logger.php
$pdo = new PDO('mysql:host=localhost;dbname=analytics_db', 'user', 'pass');

$page = $_SERVER['REQUEST_URI'];

$stmt = $pdo->prepare("INSERT INTO page_views (page_path, viewed_at) VALUES (?, NOW())");
$stmt->execute([$page]);

I usually put this snippet in a global header or a middleware layer. It's lightweight, but remember: in a high-traffic environment, hitting the database on every single page load is a recipe for a crash. In a real-world production app, I'd probably push these events to a queue like Redis first, but for our dashboard, direct inserts are fine.

Aggregating the Traffic

Now for the actual dashboard logic. We don't want to see every single row; we want the totals. This is where GROUP BY becomes your best friend in SQL.

// dashboard_data.php
$pdo = new PDO('mysql:host=localhost;dbname=analytics_db', 'user', 'pass');

$query = "SELECT page_path, COUNT(*) as views 
          FROM page_views 
          GROUP BY page_path 
          ORDER BY views DESC 
          LIMIT 5";

$stmt = $pdo->query($query);
$topPages = $stmt->fetchAll(PDO::FETCH_ASSOC);

I ran this locally and it worked perfectly. But then I noticed something. As I added more test data from yesterday and the day before, the "Top Pages" were reflecting the total history of the site. That's not a dashboard; that's an archive. A real dashboard needs a time window—usually "today" or "last 24 hours."

I messed up by forgetting the WHERE clause. Let's fix that so we're only looking at today's traffic:

// The corrected query
$query = "SELECT page_path, COUNT(*) as views 
          FROM page_views 
          WHERE viewed_at >= CURDATE() 
          GROUP BY page_path 
          ORDER BY views DESC 
          LIMIT 5";

Adding WHERE viewed_at >= CURDATE() ensures that the moment the clock strikes midnight, our dashboard resets. It keeps the data relevant and, more importantly, keeps the query fast as the table grows.

Packaging the Data for the Frontend

Since the frontend will likely be a separate JS component or a template, I'm not going to echo HTML directly here. Instead, I'll structure the response as a JSON object. This makes the backend reusable if you ever decide to switch from a PHP template to a Vue or React frontend.

header('Content-Type: application/json');

$stats = [
    'generated_at' => date('Y-m-d H:i:s'),
    'top_pages' => $topPages,
    'total_views_today' => $pdo->query("SELECT COUNT(*) FROM page_views WHERE viewed_at >= CURDATE()")->fetchColumn()
];

echo json_encode($stats);

I added a total_views_today key because it's the first thing any stakeholder asks for when they open a dashboard. It's a simple scalar value that gives immediate context to the "Top Pages" list.

📋 Practical Task

Implementing a Page-Specific View Counter

Currently, our dashboard shows the top 5 pages across the whole site. Your task is to create a new PHP script called page_stats.php that accepts a path via a GET request (e.g., page_stats.php?path=/about) and returns the total number of views for only that specific page for all time.

Your script should:

• Validate that the path parameter exists in the $_GET array.
• Use a prepared statement to prevent SQL injection.
• Return a JSON response containing the path and the view count.