中文

Build a Todo App with PDO

AI-assistedDrafted with AI assistance and reviewed before publishing; examples are version-pinned. Verify against official docs when in doubt.

PHP 8.4

Time to assemble the earlier chapters into something that runs: a todo list that can add, complete and delete items, stored in SQLite. The point is not the feature set — it is getting each layer right.

Layout and entry point

todo/
  public/index.php
  src/db.php
  src/helpers.php
  views/layout.php
  views/list.php
  storage/app.db
<?php
// public/index.php
declare(strict_types=1);

require __DIR__ . '/../src/helpers.php';
require __DIR__ . '/../src/db.php';

session_start();

$route = $_GET['r'] ?? 'list';
$method = $_SERVER['REQUEST_METHOD'];

try {
    match (true) {
        $method === 'GET'  && $route === 'list'   => showList($pdo),
        $method === 'POST' && $route === 'add'    => addTodo($pdo),
        $method === 'POST' && $route === 'toggle' => toggleTodo($pdo),
        $method === 'POST' && $route === 'delete' => deleteTodo($pdo),
        default => notFound(),
    };
} catch (Throwable $e) {
    error_log((string) $e);
    http_response_code(500);
    echo 'Server error';
}

A ?r= query parameter is the smallest viable router. Every write goes through POST, so link prefetching, crawlers and a browser restoring history can never trigger a change.

Schema

<?php
// src/db.php
declare(strict_types=1);

$pdo = new PDO('sqlite:' . __DIR__ . '/../storage/app.db', null, null, [
    PDO::ATTR_ERRMODE            => PDO::ERRMODE_EXCEPTION,
    PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC,
    PDO::ATTR_EMULATE_PREPARES   => false,
]);

$pdo->exec(<<<SQL
CREATE TABLE IF NOT EXISTS todos (
    id         INTEGER PRIMARY KEY AUTOINCREMENT,
    title      TEXT    NOT NULL,
    done       INTEGER NOT NULL DEFAULT 0,
    created_at TEXT    NOT NULL
)
SQL);

For a demo, creating the table at the entry point is fine. A real project uses a migration script so DDL never runs on a normal request.

Helper functions

<?php
// src/helpers.php
declare(strict_types=1);

function e(?string $value): string
{
    return htmlspecialchars($value ?? '', ENT_QUOTES | ENT_SUBSTITUTE, 'UTF-8');
}

function render(string $view, array $data = []): void
{
    extract($data, EXTR_SKIP);
    ob_start();
    require __DIR__ . '/../views/' . $view . '.php';
    $content = ob_get_clean();
    require __DIR__ . '/../views/layout.php';
}

function redirect(string $url): never
{
    header('Location: ' . $url, true, 303);
    exit;
}

function notFound(): never
{
    http_response_code(404);
    exit('Not found');
}

function flash(?string $message = null): ?string
{
    if ($message !== null) {
        $_SESSION['flash'] = $message;
        return null;
    }
    $value = $_SESSION['flash'] ?? null;
    unset($_SESSION['flash']);
    return $value;
}

function csrf_token(): string
{
    return $_SESSION['csrf'] ??= bin2hex(random_bytes(32));
}

function csrf_check(): void
{
    $sent = $_POST['csrf'] ?? '';
    if (!is_string($sent) || !hash_equals($_SESSION['csrf'] ?? '', $sent)) {
        http_response_code(419);
        exit('Session expired, reload the page and try again');
    }
}

redirect() uses 303 so the browser follows up with a GET. flash() reads once and clears, which is exactly what Post/Redirect/Get needs.

Reading and rendering

function showList(PDO $pdo): void
{
    $todos = $pdo->query('SELECT id, title, done FROM todos ORDER BY done ASC, id DESC')->fetchAll();

    render('list', [
        'title' => 'Todos',
        'todos' => $todos,
        'flash' => flash(),
    ]);
}
<?php
// views/list.php
/** @var list<array{id:int,title:string,done:int}> $todos */
/** @var string|null $flash */
?>
<h1><?= e($title) ?></h1>

<?php if ($flash !== null): ?>
  <p class="flash" role="status"><?= e($flash) ?></p>
<?php endif; ?>

<form method="post" action="?r=add" class="add-form">
  <input type="hidden" name="csrf" value="<?= e(csrf_token()) ?>" />
  <label for="title">New task</label>
  <input id="title" name="title" maxlength="120" required autofocus />
  <button type="submit">Add</button>
</form>

<ul class="todo-list">
  <?php foreach ($todos as $todo): ?>
    <li class="<?= $todo['done'] ? 'done' : '' ?>">
      <form method="post" action="?r=toggle" class="inline">
        <input type="hidden" name="csrf" value="<?= e(csrf_token()) ?>" />
        <input type="hidden" name="id" value="<?= (int) $todo['id'] ?>" />
        <button type="submit"><?= $todo['done'] ? 'Undo' : 'Done' ?></button>
      </form>

      <span><?= e($todo['title']) ?></span>

      <form method="post" action="?r=delete" class="inline"
            onsubmit="return confirm('Delete this task?')">
        <input type="hidden" name="csrf" value="<?= e(csrf_token()) ?>" />
        <input type="hidden" name="id" value="<?= (int) $todo['id'] ?>" />
        <button type="submit">Delete</button>
      </form>
    </li>
  <?php endforeach; ?>
</ul>

Use <button> inside a form rather than an <a> link for writes: it works with the keyboard, screen readers and without JavaScript. The confirm() call is the lightest possible guard, but it is enough to stop misclicks.

Writing

function addTodo(PDO $pdo): never
{
    csrf_check();

    $title = trim((string) ($_POST['title'] ?? ''));
    if ($title === '') {
        flash('Task text cannot be empty');
        redirect('/');
    }
    if (mb_strlen($title) > 120) {
        flash('Task text is limited to 120 characters');
        redirect('/');
    }

    $stmt = $pdo->prepare('INSERT INTO todos (title, done, created_at) VALUES (:title, 0, :created)');
    $stmt->execute([':title' => $title, ':created' => date('c')]);

    flash('Added');
    redirect('/');
}

function toggleTodo(PDO $pdo): never
{
    csrf_check();

    $id = filter_input(INPUT_POST, 'id', FILTER_VALIDATE_INT);
    if ($id === false || $id === null) {
        flash('Invalid parameter');
        redirect('/');
    }

    $stmt = $pdo->prepare('UPDATE todos SET done = CASE done WHEN 1 THEN 0 ELSE 1 END WHERE id = :id');
    $stmt->execute([':id' => $id]);

    redirect('/');
}

function deleteTodo(PDO $pdo): never
{
    csrf_check();

    $id = filter_input(INPUT_POST, 'id', FILTER_VALIDATE_INT);
    if ($id === false || $id === null) {
        flash('Invalid parameter');
        redirect('/');
    }

    $pdo->prepare('DELETE FROM todos WHERE id = :id')->execute([':id' => $id]);

    flash('Deleted');
    redirect('/');
}

filter_input returns the integer, false when validation fails, and null when the parameter is absent. Handle both failure cases — a === false check alone lets the missing case through.

Layout

<?php
// views/layout.php
/** @var string $content */
/** @var string $title */
?>
<!doctype html>
<html lang="en">
  <head>
    <meta charset="utf-8" />
    <meta name="viewport" content="width=device-width, initial-scale=1" />
    <title><?= e($title) ?></title>
    <style>
      body { font-family: system-ui, sans-serif; max-width: 40rem; margin: 3rem auto; padding: 0 1rem; }
      .inline { display: inline; }
      .done span { text-decoration: line-through; opacity: .6; }
      .flash { padding: .5rem .75rem; background: #f0fdf4; border-left: 3px solid #10b981; }
    </style>
  </head>
  <body>
    <?= $content ?>
  </body>
</html>

$content is printed unescaped because it is already-rendered HTML; every other value goes through e().

Running it locally

php -S localhost:8000 -t public

storage/ must be writable. SQLite creates app.db on first use — add it to .gitignore.

Pre-deploy checklist

  1. display_errors=0, errors written to a log.
  2. HTTPS in production, with session.cookie_secure=1 and session.cookie_httponly=1.
  3. Call session_regenerate_id(true) whenever the login state changes (this project has no login, so it is skipped).
  4. src/, views/ and storage live outside the web root — -t public guarantees that here.
  5. The database file must not be reachable by URL.
  6. Every write checks the CSRF token.
  7. Every list item is printed through e().

Common mistakes

1. Triggering writes from a GET link

<a href="?r=delete&id=3"> gets prefetched, crawled, or replayed from history. Writes are POST only.

2. No CSRF check

A third-party page can make a logged-in user submit a form. Validate the token on every write, and compare with hash_equals to avoid timing leaks.

3. Rendering directly after a POST

A refresh resubmits the form. Always Post/Redirect/Get: redirect() when the work is done.

4. Passing an unvalidated id into SQL

Even with prepared statements, confirm it is an integer: filter_input plus a cast.

5. Storing done as a string

SQLite has no boolean type. Use 0/1 integers and cast on the way out, so "0" never reads as truthy.

6. One list shared by every visitor

With no user table, all visitors see the same rows. Add user_id and filter every query once accounts exist.

7. Forgetting session_start()

$_SESSION is empty, so CSRF validation always fails. Start the session once, at the entry point.

Summary

  • One entry point routes everything; writes accept POST only and verify CSRF.
  • All SQL uses prepared statements; integer parameters are filtered first.
  • PRG plus flash messages handle resubmission and user feedback.
  • Views only print, with e() as the default action.
  • Walk the security checklist before deploying — it is cheaper than an incident.

Next: the same data layer exposed as a JSON API with proper status codes.

Tags