Small CRUD App With PDO

Build a SQLite product manager by adding one persistence decision at a time. The important boundaries are connection configuration, schema constraints, prepared SQL, HTTP-string validation, and POST/redirect/GET.

Choose The Database Explicitly

This project uses SQLite, so its schema and identifier behavior are SQLite-specific:

CREATE TABLE products (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    name TEXT NOT NULL CHECK (length(name) BETWEEN 1 AND 100),
    price_cents INTEGER NOT NULL CHECK (price_cents BETWEEN 0 AND 99999999),
    status TEXT NOT NULL CHECK (status IN ('draft', 'published'))
);

Database constraints support PHP validation. They remain the final defense when another code path writes invalid data.

Configure PDO Once

Put connection construction in src/Database.php:

<?php

declare(strict_types=1);

function openDatabase(string $path): PDO
{
    $pdo = new PDO('sqlite:' . $path, null, null, [
        PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION,
        PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC,
        PDO::ATTR_STRINGIFY_FETCHES => false,
    ]);
    $pdo->exec('PRAGMA foreign_keys = ON');

    return $pdo;
}

Exception mode prevents ignored SQL errors. An explicit fetch mode makes repository return shapes predictable. Apply the migration from a CLI command before serving requests; do not create tables opportunistically inside a controller.

Learn One Repository Method At A Time

Start with lookup. The request ID never becomes SQL text:

// Partial: ProductRepository::find().
$statement = $this->pdo->prepare(
    'SELECT id, name, price_cents, status
     FROM products WHERE id = :id'
);
$statement->execute(['id' => $id]);
$row = $statement->fetch();

return $row === false ? null : $row;

false from fetch() becomes the application's null missing-record result. The controller can then choose 404.

Insert uses the same boundary and checks that SQLite returned an identifier:

// Partial: ProductRepository::insert().
$statement = $this->pdo->prepare(
    'INSERT INTO products (name, price_cents, status)
     VALUES (:name, :price_cents, :status)'
);
$statement->execute([
    'name' => $name,
    'price_cents' => $priceCents,
    'status' => $status,
]);

$id = $this->pdo->lastInsertId();
if ($id === false) {
    throw new RuntimeException('Inserted product ID was unavailable.');
}

return (int) $id;

Update and delete also bind :id. Their affected-row result must become a deliberate missing or concurrent-change outcome rather than an unexplained success message.

Bind Pagination As Integers

A bounded list uses stable order, limit, and offset:

// Partial: ProductRepository::page().
$statement = $this->pdo->prepare(
    'SELECT id, name, price_cents, status
     FROM products ORDER BY id DESC LIMIT :limit OFFSET :offset'
);
$statement->bindValue(':limit', $limit, PDO::PARAM_INT);
$statement->bindValue(':offset', $offset, PDO::PARAM_INT);
$statement->execute();

return $statement->fetchAll();

Do not interpolate query-string values into LIMIT or OFFSET. Validate the page as a positive bounded integer, use a fixed page size such as 20, then calculate the offset.

Parse Money Without A Float

Form values arrive as strings. Convert decimal text directly to integer cents:

<?php

declare(strict_types=1);

function parsePriceCents(string $price): ?int
{
    if (preg_match('/\A(0|[1-9][0-9]*)(?:\.([0-9]{1,2}))?\z/', $price, $match) !== 1) {
        return null;
    }

    $whole = (int) $match[1];
    $fraction = (int) str_pad($match[2] ?? '', 2, '0');
    $cents = ($whole * 100) + $fraction;

    return $cents <= 99_999_999 ? $cents : null;
}

This accepts 12, 12.5, and 12.50, but rejects negative, scientific, and three-decimal values. Persistence never receives a binary floating-point price.

Validate The Whole Product Shape

Normalize only scalar form fields, then collect field errors:

// Partial: inside validateProduct(array $input).
$name = is_string($input['name'] ?? null) ? trim($input['name']) : '';
$price = is_string($input['price'] ?? null) ? trim($input['price']) : '';
$status = is_string($input['status'] ?? null) ? $input['status'] : '';
$priceCents = parsePriceCents($price);

if ($name === '' || strlen($name) > 100) {
    $errors['name'] = 'Enter a name up to 100 bytes.';
}
if ($priceCents === null) {
    $errors['price'] = 'Enter a valid price.';
}
if (!in_array($status, ['draft', 'published'], true)) {
    $errors['status'] = 'Choose draft or published.';
}

Preserve the original price string for redisplay, but pass only the validated integer cents to the repository.

Make Writes Follow One Order

For create, update, and delete, keep the HTTP sequence visible:

// Partial: protected create handler after method, authorization, and CSRF checks.
[$values, $errors] = validateProduct($_POST);
if ($errors !== []) {
    http_response_code(422);
    require dirname(__DIR__) . '/templates/product-form.php';
    exit;
}

$id = $repository->insert($values['name'], $values['price_cents'], $values['status']);
header('Location: /product-edit.php?id=' . $id, true, 303);
exit;

All state-changing handlers require POST, current authorization, and CSRF before validation and persistence. Detail/edit routes parse a positive integer ID and return 404 when the repository returns null. Templates escape database values at render time.

Verify Each Boundary

Use an in-memory SQLite repository test to migrate, insert, find, update, page, delete, and confirm a missing row. Through HTTP, test normal writes, array-shaped fields, invalid prices, unknown IDs, bad CSRF, anonymous writes, HTML-looking names, and refresh after redirect.