🚀 Building the PHP/SQLite People Dashboard

This guide explains how to set up and run a People Dashboard application using PHP to manage a list of people's names and dates of birth stored in an SQLite3 database.

📋 Prerequisites

Before you begin, you will need a local environment capable of running PHP, such as:

📁 Project Structure

All files should be placed in a single directory, such as /people-app.

/people-app
├── config.php
├── db.php
├── form.php
├── helpers.php
├── index.php
├── process.php
├── stats.php
└── style.css

💻 Complete Code Reference

1. Authentication Setup (config.php)

Initializes the session and sets up the separate users.db file and users table for application security.

<?php
session_start();

// Create SQLite database
$db = new SQLite3('users.db');

// Create users table if it doesn't exist
$db->exec('CREATE TABLE IF NOT EXISTS users (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    username TEXT UNIQUE NOT NULL,
    password TEXT NOT NULL,
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP
)');
?>

2. People Database (db.php)

Establishes the connection to people.db and ensures the core people table is ready.

<?php
// db.php — central SQLite connection + helper

ini_set('display_errors', 1);
error_reporting(E_ALL);

function getDb(): SQLite3 {
    static $db = null;
    if ($db === null) {
        $dbFile = __DIR__ . '/people.db';
        $db = new SQLite3($dbFile);
        $db->exec('PRAGMA foreign_keys = ON;');
        $db->exec('PRAGMA journal_mode = WAL;');
        $db->exec('CREATE TABLE IF NOT EXISTS people (
            id INTEGER PRIMARY KEY AUTOINCREMENT,
            name TEXT NOT NULL,
            dob TEXT NOT NULL
        )');
    }
    return $db;
}
?>

3. Utility Functions (helpers.php)

Contains functions for security (clean), date formatting, and redirection, including a check for user login state.

<?php
// helpers.php — shared utility functions
if (session_status() === PHP_SESSION_NONE) {
    session_start();
}

if (!isset($_SESSION['user_id'])) {
    header('Location: login.php');
    exit;
}

require_once __DIR__ . '/db.php';
require_once __DIR__ . '/stats.php';

function clean($s) {
    return htmlspecialchars($s ?? '', ENT_QUOTES, 'UTF-8');
}

function formatDate(string $iso): string {
    if ($iso === '') return '';
    $d = DateTime::createFromFormat('Y-m-d', $iso);
    return $d ? $d->format('m/d/Y') : $iso;
}

function iso_date_or_null($s) {
    if (!$s) return null;
    $d = DateTime::createFromFormat('Y-m-d', $s);
    return $d && $d->format('Y-m-d') === $s ? $s : null;
}

function redirect($target = 'index.php') {
    header("Location: $target");
    exit;
}
?>

4. Statistics Functions (stats.php)

Calculates and returns key dashboard statistics: total people count and average age.

<?php
// stats.php — functions for dashboard statistics
require_once __DIR__ . '/db.php';

function getStats(SQLite3 $db): array {
    $stats = ['count' => 0, 'avg_age' => 0];

    // Total count
    $row = $db->query('SELECT COUNT(*) AS c FROM people')
              ->fetchArray(SQLITE3_ASSOC);
    $stats['count'] = (int)$row['c'];

    // Average age using SQLite's julianday()
    $row = $db->query(
        'SELECT AVG((julianday("now") - julianday(dob)) / 365.25) AS avg_age FROM people'
    )->fetchArray(SQLITE3_ASSOC);
    $stats['avg_age'] = $row['avg_age'] ? round($row['avg_age'], 1) : 0;

    return $stats;
}
?>

5. Dashboard View (index.php)

The main view that fetches data, calculates stats, handles search, and displays the list of people.

<?php
require_once '../config.php';

if (session_status() === PHP_SESSION_NONE) {
    session_start();
}

if (!isset($_SESSION['user_id'])) {
    header('Location: ../sign-in.php');
    exit;
}

require_once __DIR__ . '/db.php';
require_once __DIR__ . '/helpers.php';
require_once __DIR__ . '/stats.php';

$db    = getDb();
$stats = getStats($db);

// --- Search + Fetch ---
$q    = trim($_GET['q'] ?? '');
$list = [];
if ($q !== '') {
    $stmt = $db->prepare(
        'SELECT * FROM people WHERE name LIKE :q OR dob LIKE :q ORDER BY name COLLATE NOCASE ASC'
    );
    $stmt->bindValue(':q', "%$q%", SQLITE3_TEXT);
    $res = $stmt->execute();
} else {
    $res = $db->query('SELECT * FROM people ORDER BY name COLLATE NOCASE ASC');
}
while ($row = $res->fetchArray(SQLITE3_ASSOC)) {
    $list[] = $row;
}
?>
<!doctype html>
<html lang="en">
<head>
  <meta charset="utf-8">
  <title>People Dashboard</title>
  <link rel="stylesheet" href="style.css">
</head>
<body>

  <h1>People Dashboard</h1>

  <!-- Stats boxes -->
  <div class="stats">
    <div class="stat-box">
      <h3>👥 Total People</h3>
      <p><?= $stats['count'] ?></p>
    </div>
    <div class="stat-box">
      <h3>📊 Average Age</h3>
      <p><?= $stats['avg_age'] ?> years</p>
    </div>
  </div>

  <!-- Search bar -->
  <form method="get" class="search">
    <input type="text" name="q" placeholder="Search by name or DOB" value="<?= clean($q) ?>">
    <button class="btn btn-primary" type="submit">Search</button>
    <a class="btn btn-secondary" href="index.php">Clear</a>
  </form>

  <p><a class="btn btn-light" href="form.php">➕ Add New Person</a></p>

  <!-- People list -->
  <?php if (empty($list)): ?>
    <p>No people found.</p>
  <?php else: ?>
    <table>
      <thead>
        <tr><th>ID</th><th>Name</th><th>Date of Birth</th><th>Actions</th></tr>
      </thead>
      <tbody>
        <?php foreach ($list as $row): ?>
        <tr>
          <td><?= (int)$row['id'] ?></td>
          <td><?= clean($row['name']) ?></td>
          <td><?= formatDate($row['dob']) ?></td>
          <td>
            <a class="btn btn-secondary" href="form.php?id=<?= (int)$row['id'] ?>">Edit</a>
            <form method="post" action="process.php" style="display:inline"
                  onsubmit="return confirm('Delete this person?');">
              <input type="hidden" name="id" value="<?= (int)$row['id'] ?>">
              <button class="btn btn-danger" type="submit" name="action" value="delete">Delete</button>
            </form>
          </td>
        </tr>
        <?php endforeach; ?>
      </tbody>
    </table>
  <?php endif; ?>

</body>
</html>

🔧 Linux Notes

Change ownership of the folder so the web server can read and write files:

sudo chown -R $USER:$USER /var/www/html/people-app
sudo chmod -R 775 /var/www/html/people-app