04.4 Project: a database-backed resource directory

01 | BUILD A USEFUL SMALL APPLICATION

Your class has useful links scattered through messages. Build a directory with a title, web address and category for each resource. Signed-in readers can search and open links. Editors can add links and maintain only their own entries.

This is a manageable project because it has a clear boundary: text and links, three categories, two roles, no file uploads and no administration dashboard. Complete and test that boundary before adding extra features.

Use PHP 8.1 or later with PDO MySQL and a running local MariaDB/MySQL server. The PHP built-in server does not install or start the database. Download the complete Section 04 example files , extract them and open the php04-examples folder in VS Code.

The public folder contains browser-facing pages. The private and tools folders are siblings outside that document root. Keep the complete folder structure: copying individual PHP pages without their shared files will not work.

02 | CHECK THE DATA MODEL

In your database administration client, run setup.sql once in a new local practice environment . Replace the password placeholder first. Copy private/config.example.php to private/config.php and enter the same account, host, port and database details. Never put your administrator credentials in the application.

The setup creates Users, Resources and Messages. If you already ran it, do not keep rerunning CREATE DATABASE; inspect the existing practice database or ask your teacher for a clean one. Use fictional details throughout.

CREATE TABLE Resources (
    resource_id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    owner_id INT UNSIGNED NOT NULL,
    title VARCHAR(120) NOT NULL,
    url VARCHAR(500) NOT NULL,
    category VARCHAR(10) NOT NULL,
    FOREIGN KEY (owner_id) REFERENCES Users(user_id),
    CONSTRAINT allowed_category CHECK (category IN ('PHP', 'SQL', 'HTML'))
) ENGINE=InnoDB;

This is already created by setup.sql. Each resource has one owner; an account can own many resources. The foreign key prevents a resource referring to a missing account. The application takes owner_id from the authenticated account, never from a hidden owner field supplied by the browser.

The database’s role/category checks support integrity, while the handlers apply permissions. A foreign key alone does not decide who may edit a row.

03 | MAP FILES TO RESPONSIBILITIES

Reuse the complete login and shared code from 04.1 and 04.2 in the download. Account setup is in tools/create-user.php; create Alex and Sam as editors and Robin as a reader before testing.

04 | READ AND SEARCH THE DIRECTORY

public/resources.php

<?php
require __DIR__ . '/../private/bootstrap.php';
$db = openDatabase();
$user = requireUser($db);
$search = $_GET['q'] ?? '';
if (!is_string($search) || strlen($search) > 100) {
    http_response_code(400);
    exit('Search must be text up to 100 bytes.');
}
$q = $db->prepare('SELECT resource_id, owner_id, title, url, category FROM Resources WHERE title LIKE :pattern ORDER BY title, resource_id');
$q->execute(['pattern' => '%' . $search . '%']);
$rows = $q->fetchAll();
?>
<!doctype html><html lang="en"><head><meta charset="utf-8"><title>Resource directory</title></head><body>
<h1>Resource directory</h1>
<p>Signed in as <?= h($user['username']) ?> (<?= h($user['role']) ?>).</p>
<form method="get"><label for="q">Title search : </label><input id="q" name="q" maxlength="100" value="<?= h($search) ?>"> <button>Search</button></form>
<?php if ($user['role'] === 'editor'): ?><p><a href="/resource-edit.php">Add a resource</a></p><?php endif; ?>
<table><caption>Matching resources</caption><thead><tr><th scope="col">Title</th><th scope="col">Category</th><th scope="col">Actions</th></tr></thead><tbody>
<?php foreach ($rows as $row): ?>
<tr><td><a href="<?= h($row['url']) ?>" target="_blank" rel="noopener noreferrer"><?= h($row['title']) ?></a></td><td><?= h($row['category']) ?></td><td>
<?php if ($user['role'] === 'editor' && (int)$user['user_id'] === (int)$row['owner_id']): ?>
<a href="/resource-edit.php?id=<?= (int)$row['resource_id'] ?>">Edit</a>
<form method="post" action="/resource-delete.php">
<input type="hidden" name="csrf" value="<?= h($_SESSION['csrf']) ?>">
<input type="hidden" name="id" value="<?= (int)$row['resource_id'] ?>">
<label><input type="checkbox" name="confirm" value="yes" required> Confirm deletion of <?= h($row['title']) ?></label> <button>Delete</button>
</form>
<?php endif; ?></td></tr>
<?php endforeach; ?>
</tbody></table>
<?php if ($rows === []): ?><p>No matching resources.</p><?php endif; ?>
<form method="post" action="/logout.php"><input type="hidden" name="csrf" value="<?= h($_SESSION['csrf']) ?>"><button>Sign out</button></form>
</body></html>

The page requires a current user before reading records. The search is a prepared LIKE pattern and the order is fixed. An empty search lists all resources; percent and underscore retain their SQL LIKE wildcard meaning. This is pattern search, not an exact literal substring search.

Every title, category and URL is escaped when inserted into HTML. The URL is also validated when saved, because escaping alone would not make a javascript: link safe.

05 | THE FORM NEEDS TWO MODES

With no id, the edit page prepares a blank create form. With a positive id, it fetches that row, checks ownership and fills the form. The hidden id carries the requested identity into POST; the server checks it again.

GET is used for reading a record and showing a form. POST is used for the actual change. Keeping that distinction makes the request journey easier to follow and avoids deletion from an ordinary link.

Missing records receive 404, malformed IDs receive 400, and forbidden changes receive 403. These are different outcomes and should be tested independently.

06 | CREATE AND UPDATE IN ONE COMPLETE HANDLER

public/resource-edit.php

<?php
require __DIR__ . '/../private/bootstrap.php';
$db = openDatabase();
$user = requireUser($db);
if ($user['role'] !== 'editor') { http_response_code(403); exit('Editors only.'); }
$isPost = $_SERVER['REQUEST_METHOD'] === 'POST';
if ($isPost) checkCsrf();
$rawId = $isPost ? ($_POST['id'] ?? '') : ($_GET['id'] ?? '');
$id = null;
if ($rawId !== '') {
    $id = is_string($rawId) ? filter_var($rawId, FILTER_VALIDATE_INT, ['options' => ['min_range' => 1]]) : false;
    if ($id === false) { http_response_code(400); exit('Invalid resource ID.'); }
}
$title = $url = '';
$category = 'PHP';
$errors = [];
if ($id !== null) {
    $q = $db->prepare('SELECT * FROM Resources WHERE resource_id = :id');
    $q->execute(['id' => $id]);
    $resource = $q->fetch();
    if ($resource === false) { http_response_code(404); exit('Resource not found.'); }
    requireOwner($user, $resource);
    $title = $resource['title']; $url = $resource['url']; $category = $resource['category'];
}
if ($isPost) {
    $title = postText('title'); $url = postText('url'); $category = postText('category');
    if ($title === '' || strlen($title) > 120) $errors[] = 'Title must be 1 to 120 bytes.';
    $scheme = strtolower((string)parse_url($url, PHP_URL_SCHEME));
    if (strlen($url) > 500 || !filter_var($url, FILTER_VALIDATE_URL) || !in_array($scheme, ['http', 'https'], true)) {
        $errors[] = 'Use an HTTP or HTTPS URL up to 500 bytes.';
    }
    if (!in_array($category, ['PHP', 'SQL', 'HTML'], true)) $errors[] = 'Choose an available category.';
    if ($errors === []) {
        if ($id === null) {
            $q = $db->prepare('INSERT INTO Resources (owner_id, title, url, category) VALUES (:owner, :title, :url, :category)');
            $q->execute(['owner' => $user['user_id'], 'title' => $title, 'url' => $url, 'category' => $category]);
        } else {
            $q = $db->prepare('UPDATE Resources SET title = :title, url = :url, category = :category WHERE resource_id = :id AND owner_id = :owner');
            $q->execute(['title' => $title, 'url' => $url, 'category' => $category, 'id' => $id, 'owner' => $user['user_id']]);
        }
        redirect('/resources.php');
    }
}
?>
<!doctype html><html lang="en"><head><meta charset="utf-8"><title>Edit resource</title></head><body>
<h1><?= $id === null ? 'Add resource' : 'Edit resource' ?></h1>
<?php foreach ($errors as $error): ?><p role="alert"><?= h($error) ?></p><?php endforeach; ?>
<form method="post" action="/resource-edit.php">
<input type="hidden" name="csrf" value="<?= h($_SESSION['csrf']) ?>">
<input type="hidden" name="id" value="<?= $id === null ? '' : (int)$id ?>">
<p><label for="title">Title : </label><input id="title" name="title" required maxlength="120" value="<?= h($title) ?>"></p>
<p><label for="url">URL : </label><input id="url" name="url" type="url" required maxlength="500" value="<?= h($url) ?>"></p>
<p><label for="category">Category : </label><select id="category" name="category">
<?php foreach (['PHP', 'SQL', 'HTML'] as $choice): ?><option value="<?= h($choice) ?>" <?= $choice === $category ? 'selected' : '' ?>><?= h($choice) ?></option><?php endforeach; ?>
</select></p><button>Save resource</button>
</form><p><a href="/resources.php">Back to directory</a></p>
</body></html>

07 | VALIDATE LINKS AS LINKS

The handler requires a non-blank title up to 120 bytes, a URL up to 500 bytes and one of the three known categories. The URL must pass FILTER_VALIDATE_URL and have an http or https scheme.

A syntactically valid URL is not a promise that the destination is safe, available or suitable for students. This app does not fetch submitted URLs on the server. A real shared directory would need a content and moderation policy appropriate to its users.

The app stores plain values, then escapes them in the list and edit fields. The validation checks meaning and shape; escaping handles HTML output; prepared statements handle SQL values. Each has a different job.

08 | KEEP UPDATES TARGETED

For a new row, INSERT receives the current user’s ID. For an existing row, UPDATE includes both resource_id and owner_id. The handler has already checked the fetched row with requireOwner.

Changing a hidden ID to Sam’s resource must fail before the update. The ownership condition in SQL further ties the write to the account. Identifiers sent by the browser never establish permission by themselves.

Updating a field to its existing value is still a valid request. A changed-row count of zero does not necessarily mean a row did not exist, so the example checks existence with SELECT first.

This small project uses one-statement writes and last-write-wins updates. It has no version checking for concurrent edits. If simultaneous editing becomes a requirement, add an explicit concurrency strategy rather than claiming the pre-check alone prevents races.

09 | CONFIRM AND AUTHORISE DELETION

public/resource-delete.php

<?php
require __DIR__ . '/../private/bootstrap.php';
if ($_SERVER['REQUEST_METHOD'] !== 'POST') {
    header('Allow: POST'); http_response_code(405); exit('Use the delete form.');
}
$db = openDatabase();
$user = requireUser($db);
checkCsrf();
$raw = $_POST['id'] ?? null;
$id = is_string($raw) ? filter_var($raw, FILTER_VALIDATE_INT, ['options' => ['min_range' => 1]]) : false;
if ($id === false) { http_response_code(400); exit('Invalid ID.'); }
$q = $db->prepare('SELECT resource_id, owner_id FROM Resources WHERE resource_id = :id');
$q->execute(['id' => $id]);
$row = $q->fetch();
if ($row === false) { http_response_code(404); exit('Resource not found.'); }
requireOwner($user, $row);
if (postText('confirm') !== 'yes') { http_response_code(400); exit('Confirm deletion.'); }
$q = $db->prepare('DELETE FROM Resources WHERE resource_id = :id AND owner_id = :owner');
$q->execute(['id' => $id, 'owner' => $user['user_id']]);
redirect('/resources.php');

The form must POST with a valid token, a positive resource ID and confirm=yes. The server fetches the row, checks the current editor owns it and performs a targeted DELETE.

The checkbox confirms intent; it does not establish identity or permission. Likewise, a valid CSRF token does not grant access to another user’s row. The owner check is required even when all other form values look valid.

10 | AN END-TO-END WALKTHROUGH

In the VS Code terminal, from the project root, start the server once:

php -c development.ini -S 127.0.0.1:8000 -t public

Keep this terminal running. Hover over http://127.0.0.1:8000 and follow the link, then add the required filename in the browser. Save PHP changes in VS Code and refresh the browser. You do not need to restart the server for every source edit; configuration changes may require a restart.

Use this local PHP server rather than a static Live Server extension. The browser must request a page through PHP for server-side code to execute.

1. Open /login.php and sign in as Alex.
2. Add “PHP manual” with https://www.php.net/manual/en/ and category PHP.
3. Search for PHP, then edit the title to include an ampersand.
4. Sign out and sign in as Sam; try Alex’s edit URL directly.
5. Sign in as Robin and confirm the directory is readable but creation is forbidden.
6. Return as Alex and delete the test resource with confirmation.

Check the stored rows after each write in your SQL client. Test a forbidden POST as well as a forbidden GET: the backend must guard both.

11 | MAKE YOUR TEST EVIDENCE CONVINCING

12 | NEXT STEPS WITH A CLEAR PURPOSE

Add pagination when the list is large, an automated permission test suite when handlers change often, and optimistic concurrency when simultaneous editing matters. Each extension should solve a stated problem and come with evidence that it works.

Before public deployment, replace the local-only assumptions with reviewed authentication, session expiry, login abuse controls, HTTPS, private configuration, appropriate logging and backups. The examples demonstrate the core flow; they are not a ready-made public account platform.

KEEP LEARNING

Previous page 04 Mini projects and next steps: challenges

Download complete Section 04 example files

Official references: PHP error configuration · Password hashing · Password verification · Session identifier rotation