Building the CRUD Pages

Personal Catalogue: PHP & MySQL

Chapter 3 · Building the CRUD Pages

Chapter 2 built the real table structure. This chapter builds the first pages that actually write to it — adding an item, editing an existing one, and deleting one — using the PDO connection from Chapter 1 and the prepared-statement discipline this site's own PHP Intermediate course (Chapter 4) already covers in depth. Tags and search come later (Chapters 5 and 6); this chapter deliberately stays scoped to the items table alone.

Assumed Groundwork

If PDO prepared statements, named placeholders, or basic CRUD patterns aren't already comfortable, this course assumes that foundation was covered in PHP Intermediate, Chapter 4. This chapter builds on that material directly rather than re-teaching it.

A Minimal Item-Type Picker

Every add/edit form needs to offer the four real item types from item_types as a dropdown, rather than hard-coding them as literal strings in the form's own markup. A tiny shared function keeps that list in one place:

functions.php

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

function get_item_types(PDO $pdo): array {
    $stmt = $pdo->query('SELECT id, name FROM item_types ORDER BY id');
    return $stmt->fetchAll();
}

Since item_types almost never changes at runtime, a plain unparameterised query() is fine here — prepare()/execute() only really earns its keep when real, external data is involved, which this call doesn't have.

Adding an Item

add_item.php does two jobs depending on the request method: on a GET request it shows the empty form; on a POST request it validates and inserts the submitted data. This is the same "one file, two branches" pattern PHP Fundamentals' own Chapter 8 already introduced for $_SERVER['REQUEST_METHOD'].

add_item.php

<?php
require_once __DIR__ . '/functions.php';

$errors = [];

if ($_SERVER['REQUEST_METHOD'] === 'POST') {
    $itemTypeId  = (int) ($_POST['item_type_id'] ?? 0);
    $title       = trim($_POST['title'] ?? '');
    $creator     = trim($_POST['creator'] ?? '');
    $formatDetail = trim($_POST['format_detail'] ?? '');
    $releaseYear = trim($_POST['release_year'] ?? '');
    $notes       = trim($_POST['notes'] ?? '');

    if ($title === '') {
        $errors[] = 'Title is required.';
    }
    if ($itemTypeId < 1) {
        $errors[] = 'Please choose an item type.';
    }

    if (empty($errors)) {
        $stmt = $pdo->prepare('
            INSERT INTO items (item_type_id, title, creator, format_detail, release_year, notes)
            VALUES (:item_type_id, :title, :creator, :format_detail, :release_year, :notes)
        ');
        $stmt->execute([
            'item_type_id'   => $itemTypeId,
            'title'          => $title,
            'creator'        => $creator !== '' ? $creator : null,
            'format_detail'  => $formatDetail !== '' ? $formatDetail : null,
            'release_year'   => $releaseYear !== '' ? (int) $releaseYear : null,
            'notes'          => $notes !== '' ? $notes : null,
        ]);

        header('Location: /add_item.php?added=1');
        exit;
    }
}

$itemTypes = get_item_types($pdo);
?>
<!DOCTYPE html>
<html>
<body>

<?php if (isset($_GET['added'])): ?>
    <p>Item added.</p>
<?php endif; ?>

<?php foreach ($errors as $error): ?>
    <p style="color: red;"><?= htmlspecialchars($error) ?></p>
<?php endforeach; ?>

<form method="post">
    <label>Type:
        <select name="item_type_id" required>
            <option value="">-- choose --</option>
            <?php foreach ($itemTypes as $type): ?>
                <option value="<?= $type['id'] ?>"><?= htmlspecialchars($type['name']) ?></option>
            <?php endforeach; ?>
        </select>
    </label><br>

    <label>Title: <input type="text" name="title" required></label><br>
    <label>Creator: <input type="text" name="creator"></label><br>
    <label>Format detail: <input type="text" name="format_detail"></label><br>
    <label>Release year: <input type="number" name="release_year"></label><br>
    <label>Notes: <textarea name="notes"></textarea></label><br>

    <button type="submit">Add Item</button>
</form>

</body>
</html>

Why the Empty-String Fields Become NULL, Not ''

creator, format_detail, release_year, and notes are all nullable columns (Chapter 2). A DVD genuinely has no author, and it's more honest for that field to store SQL NULL — "no value recorded" — than an empty string, which reads as "the value is the empty string," a subtly different and less accurate claim. The ternaries in the execute() array make that translation explicit rather than leaving it to chance.

Editing an Item

edit_item.php follows the same GET/POST split, but GET now has to fetch the existing row first (by id from the query string) so the form can be pre-filled, and POST runs an UPDATE instead of an INSERT:

edit_item.php

<?php
require_once __DIR__ . '/functions.php';

$id = (int) ($_GET['id'] ?? $_POST['id'] ?? 0);
if ($id < 1) {
    exit('Missing item id.');
}

$errors = [];

if ($_SERVER['REQUEST_METHOD'] === 'POST') {
    $title = trim($_POST['title'] ?? '');

    if ($title === '') {
        $errors[] = 'Title is required.';
    }

    if (empty($errors)) {
        $stmt = $pdo->prepare('
            UPDATE items
            SET item_type_id = :item_type_id,
                title = :title,
                creator = :creator,
                format_detail = :format_detail,
                release_year = :release_year,
                notes = :notes
            WHERE id = :id
        ');
        $stmt->execute([
            'item_type_id'  => (int) $_POST['item_type_id'],
            'title'         => $title,
            'creator'       => $_POST['creator'] !== '' ? $_POST['creator'] : null,
            'format_detail' => $_POST['format_detail'] !== '' ? $_POST['format_detail'] : null,
            'release_year'  => $_POST['release_year'] !== '' ? (int) $_POST['release_year'] : null,
            'notes'         => $_POST['notes'] !== '' ? $_POST['notes'] : null,
            'id'            => $id,
        ]);

        header("Location: /edit_item.php?id={$id}&updated=1");
        exit;
    }
}

$stmt = $pdo->prepare('SELECT * FROM items WHERE id = :id');
$stmt->execute(['id' => $id]);
$item = $stmt->fetch();

if (!$item) {
    exit('Item not found.');
}

$itemTypes = get_item_types($pdo);
?>
<!-- form markup follows the same shape as add_item.php, with each
     input's value pre-filled from $item, e.g.:
     <input type="text" name="title" value="<?= htmlspecialchars($item['title']) ?>">
     and a hidden <input type="hidden" name="id" value="<?= $id ?>"> -->

Always Fetch the Row Before Trusting the Form

Fetching the existing item first — even though the POST branch doesn't strictly need every field from it — means the edit page fails honestly with "Item not found" if someone edits the URL to a bogus id, rather than silently rendering a blank form that would submit an UPDATE against a row that was never actually loaded.

Deleting an Item

Delete is the simplest of the three, but it's the one most worth protecting against an accidental click — this project uses a plain confirmation link rather than a full modal, since a personal single-user catalogue doesn't need more ceremony than that:

delete_item.php

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

$id = (int) ($_POST['id'] ?? 0);

if ($_SERVER['REQUEST_METHOD'] === 'POST' && $id > 0) {
    $stmt = $pdo->prepare('DELETE FROM items WHERE id = :id');
    $stmt->execute(['id' => $id]);
}

header('Location: /index.php?deleted=1');
exit;

On the listing page (built in full in Chapter 7), each row's own delete action is a tiny <form method="post" action="delete_item.php"> containing just the hidden id field and a submit button — never a plain <a href> link.

Why Delete Must Be a POST, Not a GET Link

A DELETE FROM items ... triggered by a plain link means the browser (or a search-engine crawler, or a link-preview bot) could delete a real item just by fetching that URL — a GET request is supposed to be "safe" (no side effects) by HTTP's own convention, and browsers, proxies, and bots all rely on that convention. Requiring a POST means the delete can only actually happen from a real form submission on this page.

Where Tags Fit Later

This chapter's own add/edit forms deliberately don't touch tags or item_tags yet — Chapter 5 covers attaching and managing tags specifically, once the core item CRUD is solid on its own. Building both at once would make it harder to tell, if something breaks, whether the bug is in the item logic or the tag logic.

Hands-On Exercises

Exercise 1

Wire up add_item.php against the real catalogue database and add three items: one book, one CD, and one DVD. Confirm all three appear correctly with SELECT * FROM items;, including that each row's unused columns (e.g. a DVD's own creator) are stored as NULL, not an empty string.

📄 View solution

Exercise 2

Complete the full edit_item.php form markup (the part left as a comment in this chapter), pre-filling every field's own value attribute from $item, then use it to change one of your three items' own release_year.

📄 View solution

Exercise 3

Explain, in your own words, why delete_item.php requires a POST request rather than allowing a plain link, and describe one real, concrete way a GET-based delete link could be triggered by something other than a deliberate click.

📄 View solution

Chapter 3 Quick Reference

add_item.php — GET shows the form, POST validates and runs a prepared INSERT
edit_item.php — GET fetches the existing row and pre-fills the form, POST runs a prepared UPDATE
delete_item.php — POST-only, running a prepared DELETE, never triggerable via a plain link
Empty form fields — converted to SQL NULL, not stored as empty strings, for every nullable column
Tags — deliberately deferred to Chapter 5, kept out of this chapter's own item CRUD
Next chapter: Type-Specific Fields — one form, several real item shapes