03.4 Create, read, update and delete records

01 | WHAT DOES CRUD MEAN?

Use PHP 8.1 or later for these examples. Create a project folder with public and private subfolders. Start the local server from the project root with php -S 127.0.0.1:8000 -t public , then open the requested .php page through HTTP.

The public folder is the document root: files the browser can request. The private folder is a sibling, not a child of public. PHP can read it through filesystem paths, but it is not directly served by this configuration.

The examples are for local single-user practice. Database pages require a running MariaDB or MySQL server and the PDO MySQL driver. PHP’s built-in web server does not install or start the database.

The example is intentionally single-user local practice with no real authentication. Every reachable user would have the application’s allowed operations. Do not deploy it as a public administration interface.

02 | KEEP THE LAYERS VISIBLE

The browser submits action and record fields. PHP checks the request, validates values, runs one fixed prepared statement and redirects. The following GET reads the current table and renders it again.

The database persists records between requests. The session stores a form token, not the club table. HTML output escaping and parameter binding operate at different points in this journey.

Reuse setup.sql, private/config.php, private/database.php and public/helpers.php from the preceding guides.

03 | ADD THE REQUEST BOOTSTRAP

public/bootstrap.php

<?php
require_once __DIR__ . '/helpers.php';
// LOCAL HTTP PRACTICE ONLY: use secure => true on an HTTPS deployment.
ini_set('session.use_strict_mode', '1');
ini_set('session.use_only_cookies', '1');
session_set_cookie_params([
    'lifetime' => 0,
    'path' => '/',
    'secure' => false,
    'httponly' => true,
    'samesite' => 'Lax',
]);
if (!session_start()) {
    throw new RuntimeException('Could not start a session.');
}
if (!isset($_SESSION['csrf'])) {
    $_SESSION['csrf'] = bin2hex(random_bytes(32));
}
function validPostToken(): bool
{
    $token = $_POST['csrf'] ?? null;
    return is_string($token) && hash_equals($_SESSION['csrf'], $token);
}

Load this before any output. It starts the local session, prepares an unpredictable CSRF token and defines the checker used for POST actions.

The Secure cookie setting is false only for this local HTTP example. HTTPS deployments need an appropriate secure configuration. The token does not authenticate the user or grant permission to change a record.

04 | COMPLETE CRUD PAGE

public/clubs.php

<?php
require_once __DIR__ . '/bootstrap.php';
require_once dirname(__DIR__) . '/private/database.php';
$errors = [];
$rows = [];
$editingId = null;
$name = '';
$capacityText = '';
$databaseReady = false;

try {
    $pdo = openDatabase();
    $databaseReady = true;
    if ($_SERVER['REQUEST_METHOD'] === 'POST') {
        if (!validPostToken()) {
            http_response_code(403);
            $errors[] = 'Invalid form token. Reload the page.';
        } else {
            $action = $_POST['action'] ?? '';
            if (!is_string($action) || !in_array($action, ['create', 'update', 'delete'], true)) {
                $errors[] = 'Unknown action.';
            } else {
                $id = null;
                if ($action !== 'create') {
                    $rawId = $_POST['id'] ?? '';
                    $id = is_string($rawId) ? filter_var($rawId, FILTER_VALIDATE_INT,
                        ['options' => ['min_range' => 1]]) : false;
                    if ($id === false) {
                        $errors[] = 'Choose a valid record ID.';
                    } else {
                        $statement = $pdo->prepare('SELECT club_id FROM Clubs WHERE club_id = ?');
                        $statement->execute([$id]);
                        if ($statement->fetch() === false) {
                            $errors[] = 'That record no longer exists.';
                        }
                    }
                }
                if ($action === 'delete') {
                    if (($_POST['confirm'] ?? '') !== 'yes') {
                        $errors[] = 'Confirm this deletion.';
                    }
                    if (!$errors) {
                        $statement = $pdo->prepare('DELETE FROM Clubs WHERE club_id = ?');
                        $statement->execute([$id]);
                        header('Location: clubs.php', true, 303);
                        exit;
                    }
                } else {
                    $rawName = $_POST['name'] ?? '';
                    $rawCapacity = $_POST['capacity'] ?? '';
                    $name = is_string($rawName) ? trim($rawName) : '';
                    $capacityText = is_string($rawCapacity) ? trim($rawCapacity) : '';
                    $capacity = filter_var($capacityText, FILTER_VALIDATE_INT,
                        ['options' => ['min_range' => 1, 'max_range' => 200]]);
                    if ($name === '' || strlen($name) > 100) {
                        $errors[] = 'Enter a name of 1 to 100 bytes.';
                    }
                    if ($capacity === false) {
                        $errors[] = 'Capacity must be a whole number from 1 to 200.';
                    }
                    if ($action === 'update' && is_int($id)) {
                        $editingId = $id;
                    }
                    if (!$errors) {
                        if ($action === 'create') {
                            $statement = $pdo->prepare('INSERT INTO Clubs (club_name, capacity) VALUES (?, ?)');
                            $statement->execute([$name, $capacity]);
                        } else {
                            $statement = $pdo->prepare('UPDATE Clubs SET club_name = ?, capacity = ? WHERE club_id = ?');
                            $statement->execute([$name, $capacity, $id]);
                        }
                        header('Location: clubs.php', true, 303);
                        exit;
                    }
                }
            }
        }
    } elseif (isset($_GET['edit'])) {
        $rawId = $_GET['edit'];
        $id = is_string($rawId) ? filter_var($rawId, FILTER_VALIDATE_INT,
            ['options' => ['min_range' => 1]]) : false;
        if ($id === false) {
            http_response_code(400);
            $errors[] = 'Invalid edit ID.';
        } else {
            $statement = $pdo->prepare('SELECT club_id, club_name, capacity FROM Clubs WHERE club_id = ?');
            $statement->execute([$id]);
            $club = $statement->fetch();
            if ($club === false) {
                http_response_code(404);
                $errors[] = 'Club not found.';
            } else {
                $editingId = (int) $club['club_id'];
                $name = $club['club_name'];
                $capacityText = (string) $club['capacity'];
            }
        }
    }
} catch (PDOException | RuntimeException $exception) {
    error_log($exception->getMessage());
    $errors[] = 'The database operation could not be completed. Check the server log; a name may already exist.';
    http_response_code(500);
}
if ($databaseReady) {
    try {
        $rows = $pdo->query('SELECT club_id, club_name, capacity FROM Clubs ORDER BY club_id')->fetchAll();
    } catch (PDOException $exception) {
        error_log($exception->getMessage());
        $errors[] = 'The directory could not be read.';
        http_response_code(500);
    }
}
?>
<!DOCTYPE html>
<html lang="en">
<head><meta charset="UTF-8"><title>Local club CRUD</title></head>
<body>
<h1>Local club CRUD</h1>
<p>Local single-user practice. No authentication or per-user permissions are implemented.</p>
<?php if ($errors): ?>
    <ul role="alert"><?php foreach ($errors as $error): ?><li><?= escapeHtml($error) ?></li><?php endforeach; ?></ul>
<?php endif; ?>
<h2><?= $editingId === null ? 'Add club' : 'Edit club' ?></h2>
<form action="clubs.php" method="post">
    <input type="hidden" name="csrf" value="<?= escapeHtml($_SESSION['csrf']) ?>">
    <input type="hidden" name="action" value="<?= $editingId === null ? 'create' : 'update' ?>">
    <?php if ($editingId !== null): ?><input type="hidden" name="id" value="<?= $editingId ?>"><?php endif; ?>
    <p><label for="name">Name : </label>
    <input id="name" name="name" required value="<?= escapeHtml($name) ?>"></p>
    <p><label for="capacity">Capacity : </label>
    <input id="capacity" name="capacity" type="number" min="1" max="200" required value="<?= escapeHtml($capacityText) ?>"></p>
    <button type="submit">Save</button>
    <a href="clubs.php">Cancel editing</a>
</form>
<table>
    <caption>Current clubs</caption>
    <thead><tr><th>ID</th><th>Name</th><th>Capacity</th><th>Actions</th></tr></thead>
    <tbody>
        <?php foreach ($rows as $club): ?>
        <tr>
            <td><?= (int) $club['club_id'] ?></td>
            <td><?= escapeHtml($club['club_name']) ?></td>
            <td><?= (int) $club['capacity'] ?></td>
            <td>
                <a href="clubs.php?edit=<?= (int) $club['club_id'] ?>">Edit</a>
                <form action="clubs.php" method="post">
                    <input type="hidden" name="csrf" value="<?= escapeHtml($_SESSION['csrf']) ?>">
                    <input type="hidden" name="action" value="delete">
                    <input type="hidden" name="id" value="<?= (int) $club['club_id'] ?>">
                    <label><input type="checkbox" name="confirm" value="yes" required> Confirm deletion of <?= escapeHtml($club['club_name']) ?></label>
                    <button type="submit">Delete</button>
                </form>
            </td>
        </tr>
        <?php endforeach; ?>
    </tbody>
</table>
<?php if (!$rows): ?><p>No clubs to display.</p><?php endif; ?>
</body>
</html>

05 | TRACE CREATE AND THE REDIRECT

The initial GET displays a blank Add club form and the current table. A create POST supplies action, name, capacity and token. PHP checks the token, validates a trimmed name of 1–100 bytes and an integer capacity of 1–200, then runs INSERT with bound values.

A successful write sends a 303 Location redirect and exits. The browser makes a new GET, which reads the updated table. Refreshing that GET does not repeat the POST.

A redirect reduces accidental resubmission, but is not a guarantee against every duplicate request. The database’s unique club-name rule also rejects duplicate names under its configured collation.

06 | TRACE AN EDIT BUTTON

Each generated row has an Edit link containing its known integer ID. GET with edit=2 requests an edit form; it does not write data.

PHP validates the ID, reads the matching record and fills the form. A hidden id field carries the selected record into the later update POST. Hidden does not mean trusted, so the POST validates and checks it again.

The UPDATE includes WHERE club_id = ?. Without it, every row could be targeted. An unchanged-value submission is not automatically treated as a missing record.

07 | TRACE A DELETE FORM

Each row also has its own POST form containing the record ID, delete action, token and confirmation checkbox. There is no state-changing delete link using GET.

PHP validates the ID, checks the current record and confirmation, then executes a parameterised DELETE for that ID. Cancelling or omitting confirmation prevents the intended deletion.

The checkbox supports user intent, not authority: a client can forge it. A real protected admin system must check authenticated permission at the trusted server layer for every action.

08 | FAILURES AND CONCURRENT CHANGES

Validation errors preserve the edited values so users can correct them. Unknown edit IDs produce a not-found response. Database failures are logged while the browser receives a controlled message.

This small example has no optimistic version field. Another process can alter a record between lookup and write; concurrent edits can overwrite values or leave zero affected rows. Treat it as single-user local practice.

Each INSERT, UPDATE or DELETE here is a single statement under normal autocommit. A future operation spanning several dependent writes may require a transaction with rollback and appropriate concurrency controls.

09 | TEST EVERY PATH

Use fictional data and keep a before/after record for each test. A browser success response alone does not prove that the intended row changed.

10 | BUILD THE NEXT REPORT

Stretch: add authentication and per-action permissions only after studying the later access-control section. Do not label this local demonstration production-ready because it uses prepared statements.

KEEP LEARNING

Previous page 03 Files and databases: challenges

Official references: JSON decoding · PDO connections · Prepared statements · MariaDB grants