🚀 Building the PHP/SQLite People Dashboard

This guide explains how to set up and run your "People Dashboard" application, which uses **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__ . '/helpers.php'; // Self-reference for environment setup in some frameworks
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, specifically the total count and average age.

<?php
// stats.php — functions for dashboard statistics
require_once __DIR__ . '/db.php';
require_once __DIR__ . '/helpers.php';
require_once __DIR__ . '/stats.php'; // Self-reference for environment setup in some frameworks

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

// Search + list logic...

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
    $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>
  <meta name="viewport" content="width=device-width, initial-scale=1">
  <link rel="stylesheet" href="style.css">
  <style>
    body { background-color: #f0f0f0; } /* Added inline style to match uploaded file intent */
    .search {
      margin-bottom: 1rem;
      display: flex;
      justify-content: flex-end;
      gap: 0.5rem;
    }
    .search input {
      width: 20ch; /* enough for ~20 characters */
      flex: none;
    }
    .btn-light {
      background: #f0f0f0;
      color: #333;
      border: 1px solid #ccc;
      text-decoration: none;
      padding: 0.4rem 0.7rem;
      font-size: 0.95rem;
      cursor: pointer;
      border-radius: 4px;
      display: inline-block;
    }
    .btn-light:hover {
      background: #e0e0e0;
    }
  </style>
</head>
<body>
  <a class="btn btn-light" href="../examples.php">Back to Examples</a>

  <h1>People Dashboard</h1>
  <p class="muted">Manage names and dates of birth stored in SQLite.</p>

  <!-- Stats boxes -->
  <div class="stats">
    <div class="stat-box">
      <h3>👥 Total Lives</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 (MM/DD/YYYY)" value="<?= clean($q) ?>">
    <button class="btn btn-primary" type="submit" name="action" value="search">Search</button>
    <a class="btn btn-secondary" href="index.php">Clear</a>
  </form>

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

  <!-- People list -->
  <?php if (empty($list)): ?>
    <p class="muted">No people found.</p>
  <?php else: ?>
    <div class="card">
      <h2>People</h2>
      <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>
    </div>
  <?php endif; ?>
</body>
</html>

Linux Notes - Change ownership of folder:

sudo chown -R $USER:$USER /var/www/html/$NAME_OF_FOLDER

sudo chmod -R 775 /var/www/html/$NAME_OF_FOLDER