Make two lists from one table
In lesson seven, complete one task and leave another pending. Run these two statements in your MySQL query window.
SELECT id, title, done FROM tasks ORDER BY id;
SELECT id, title, done FROM tasks WHERE done = 0 ORDER BY id;
The completed task disappears from the second result and returns in the first. Filtering decides which rows to read for this request.
Read the address first
In public/index.php, add this just above the existing one-line $tasks = $pdo->query(...) statement.
$filter = $_GET['filter'] ?? 'all';
if (!in_array($filter, ['all', 'pending', 'done'], true)) {
$filter = 'all';
}
$_GET carries the value from an address such as ?filter=pending. With no filter, ?? 'all' gives a default. The strict in_array(..., true) check accepts only the three supported strings. The query still reads everything at this point.
Give the query two paths
Replace the existing list query with this block. The first branch is our original query. The second adds WHERE done = ?. PHP maps the filter to 0 or 1 and binds the number instead of putting raw URL text into the SQL string.
if ($filter === 'all') {
$tasks = $pdo->query('SELECT id, title, done FROM tasks ORDER BY id')->fetchAll();
} else {
$statement = $pdo->prepare('SELECT id, title, done FROM tasks WHERE done = ? ORDER BY id');
$statement->execute([$filter === 'done' ? 1 : 0]);
$tasks = $statement->fetchAll();
}
Open /?filter=pending by hand. Only pending tasks should appear. Use /?filter=unknown and expect the full list. If the selected filter matches nothing, the template already shows No matching tasks..
Make the addresses clickable
In app/view.php, put these links above the No matching tasks. check.
<nav aria-label="Task filters">
<a href="/?filter=all">All</a>
<a href="/?filter=pending">Pending</a>
<a href="/?filter=done">Completed</a>
</nav>
<p>Filter: <?= e($filter) ?></p>
Click Completed and copy the address into a second tab. The URL carries the choice, so the second tab uses the same filter. GET suits this read. The earlier POST form still handles status changes.
Complete working files
Start with the files that change in this lesson. If you completed the previous lesson, the other files are already in your project.
public/index.php
<?php
declare(strict_types=1);
require __DIR__ . '/../app/functions.php';
$pdo = require __DIR__ . '/../app/db.php';
session_start();
$_SESSION['csrf'] ??= bin2hex(random_bytes(32));
$csrf = $_SESSION['csrf'];
$error = null;
$input = '';
if (($_SERVER['REQUEST_METHOD'] ?? 'GET') === 'POST') {
$submittedToken = $_POST['csrf'] ?? null;
if (!is_string($submittedToken) || !hash_equals($csrf, $submittedToken)) {
http_response_code(403);
exit('Invalid form token.');
}
$action = $_POST['action'] ?? '';
if ($action === 'state') {
$id = positiveId($_POST['id'] ?? null);
$done = $_POST['done'] ?? null;
if ($id === null || !in_array($done, ['0', '1'], true)) {
http_response_code(400);
exit('Invalid task state.');
}
$statement = $pdo->prepare('UPDATE tasks SET done = ? WHERE id = ?');
$statement->execute([(int) $done, $id]);
redirectToList();
}
if (!in_array($action, ['add'], true)) {
http_response_code(400);
exit('Invalid action.');
}
$input = is_string($_POST['title'] ?? null) ? $_POST['title'] : '';
$title = validateTitle($input);
if ($title === null) {
http_response_code(422);
$error = 'Enter a title of 1 to 120 bytes.';
} else {
$statement = $pdo->prepare('INSERT INTO tasks (title) VALUES (?)');
$statement->execute([$title]);
redirectToList();
}
}
$filter = $_GET['filter'] ?? 'all';
if (!in_array($filter, ['all', 'pending', 'done'], true)) {
$filter = 'all';
}
if ($filter === 'all') {
$tasks = $pdo->query('SELECT id, title, done FROM tasks ORDER BY id')->fetchAll();
} else {
$statement = $pdo->prepare('SELECT id, title, done FROM tasks WHERE done = ? ORDER BY id');
$statement->execute([$filter === 'done' ? 1 : 0]);
$tasks = $statement->fetchAll();
}
require __DIR__ . '/../app/view.php';
app/view.php
<!doctype html>
<html lang="en">
<meta charset="utf-8">
<title>Task list</title>
<h1>Task list</h1>
<?php if ($error !== null): ?>
<p role="alert"><?= e($error) ?></p>
<?php endif; ?>
<form method="post" action="/">
<input type="hidden" name="csrf" value="<?= e($csrf) ?>">
<input type="hidden" name="action" value="add">
<label for="title">Task title</label>
<input id="title" name="title" value="<?= e($input) ?>" required>
<button>Add task</button>
</form>
<nav aria-label="Task filters">
<a href="/?filter=all">All</a>
<a href="/?filter=pending">Pending</a>
<a href="/?filter=done">Completed</a>
</nav>
<p>Filter: <?= e($filter) ?></p>
<?php if ($tasks === []): ?><p>No matching tasks.</p><?php endif; ?>
<ul>
<?php foreach ($tasks as $task): ?>
<li>
<?= e($task['title']) ?> — <?= $task['done'] ? 'Completed' : 'Pending' ?>
<form method="post" action="/">
<input type="hidden" name="csrf" value="<?= e($csrf) ?>">
<input type="hidden" name="action" value="state">
<input type="hidden" name="id" value="<?= (int) $task['id'] ?>">
<input type="hidden" name="done" value="<?= $task['done'] ? 0 : 1 ?>">
<button><?= $task['done'] ? 'Mark pending' : 'Complete' ?></button>
</form>
</li>
<?php endforeach; ?>
</ul>
</html>
Files carried forward from the previous lesson
app/functions.php
<?php
declare(strict_types=1);
function e(string $value): string
{
return htmlspecialchars($value, ENT_QUOTES | ENT_SUBSTITUTE, 'UTF-8');
}
function validateTitle(mixed $value): ?string
{
if (!is_string($value)) {
return null;
}
$title = trim($value);
return $title !== '' && strlen($title) <= 120 ? $title : null;
}
function redirectToList(): never
{
header('Location: /', true, 303);
exit;
}
function positiveId(mixed $value): ?int
{
if (!is_string($value)) {
return null;
}
$id = filter_var($value, FILTER_VALIDATE_INT, ['options' => ['min_range' => 1]]);
return $id === false ? null : $id;
}
app/db.php
<?php
declare(strict_types=1);
$host = getenv('DB_HOST') ?: '127.0.0.1';
$port = getenv('DB_PORT') ?: '3306';
$database = getenv('DB_NAME') ?: 'php_tasks';
$user = getenv('DB_USER') ?: 'php_tasks';
$password = getenv('DB_PASS');
$dsn = "mysql:host=$host;port=$port;dbname=$database;charset=utf8mb4";
return new PDO($dsn, $user, $password === false ? '' : $password, [
PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION,
PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC,
PDO::ATTR_EMULATE_PREPARES => false,
]);
schema.sql
CREATE TABLE tasks (
id INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
title VARCHAR(120) NOT NULL,
done TINYINT UNSIGNED NOT NULL DEFAULT 0,
CHECK (done IN (0, 1))
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
seed.sql
INSERT INTO tasks (title, done)
VALUES ('Read the lesson', 1), ('Run the example', 0);
After saving the files, run php -S 127.0.0.1:8000 -t public in the project folder and open http://127.0.0.1:8000/. If the server is already running from that project folder, refresh the page.
Related Articles