Can the list change without editing PHP?
In lesson four the tasks came from an array in index.php. Keep it for a moment while you create two tasks in MySQL. Then change where PHP gets the list. The foreach that draws it can do the same job.
Prepare MySQL
MySQL Server stores the tasks. PHP connects through the PDO MySQL driver. If you need MySQL Server, choose your operating system in the installation guide. Start the service and sign in with your local administrator account in MySQL Workbench or the mysql client.
MySQL Server installation steps
In a terminal in the project folder, run php --ri pdo_mysql and look for PDO Driver for MySQL and enabled. On Windows, use php --ini to find the configuration file and enable extension=pdo_mysql. On Ubuntu or Debian, install the php-mysql package that matches your PHP installation.
Create the table and application account
Run these commands in your MySQL query window. This example assumes a fresh local setup.
CREATE DATABASE php_tasks CHARACTER SET utf8mb4;
USE php_tasks;
Create the table in the same query window. AUTO_INCREMENT gives each task an ID. The title column stores its text. A new row starts with done set to 0 unless the insert supplies a different value. Save this SQL as schema.sql if you want a local copy.
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;
Insert two rows once so we have something to list. The first task is done and the second is pending.
INSERT INTO tasks (title, done)
VALUES ('Read the lesson', 1), ('Run the example', 0);
Once the table exists, use the administrator session to create the application account. Replace the password placeholder with your own local password.
CREATE USER 'php_tasks'@'127.0.0.1' IDENTIFIED BY 'YOUR_LOCAL_PASSWORD';
GRANT SELECT, INSERT, UPDATE ON php_tasks.tasks TO 'php_tasks'@'127.0.0.1';
Start PHP with this account
Use the lesson four project folder that contains public. Stop the server with Ctrl+C if it is running. Choose the commands for your terminal and substitute the local password you just set. PHP can then read the connection settings from this terminal’s environment.
PowerShell
$env:DB_USER = 'php_tasks'
$env:DB_PASS = 'YOUR_LOCAL_PASSWORD'
php -S 127.0.0.1:8000 -t public
Bash / Zsh
export DB_USER='php_tasks'
export DB_PASS='YOUR_LOCAL_PASSWORD'
php -S 127.0.0.1:8000 -t public
We will add the PHP connection below. If it reports access denied, check the account and password. If it reports could not find driver, check pdo_mysql.
Read the rows in MySQL first
Select the php_tasks database in your query window and run this.
SELECT id, title, done FROM tasks ORDER BY id;
A clean setup shows “Read the lesson” and “Run the example.” ORDER BY id gives us an explicit order. PHP will ask for the same three columns.
Get the connection from its own file
Create app/db.php. Start with <?php and declare(strict_types=1);. Add these lines to read your connection settings.
$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');
getenv() reads values from the terminal environment we just set up. When a value is absent, the expression after ?: supplies the default. Add the connection itself next.
$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,
]);
The host and port identify the server. The database name selects the database. The DSN combines these settings into a connection string. new PDO(...) opens the connection. With ERRMODE_EXCEPTION, PDO reports a failure by throwing an exception. The file returns the PDO object, so we can assign the return value of require to $pdo.
Add this under the functions import in public/index.php.
$pdo = require __DIR__ . '/../app/db.php';
Refresh. The old array is still rendering. This is a chance to confirm the connection opens before depending on it for data.
Replace the array with the results
Replace the old $tasks = [...]; block in index.php with this.
$tasks = $pdo->query('SELECT id, title, done FROM tasks ORDER BY id')->fetchAll();
query() runs the fixed statement and fetchAll() collects its rows. FETCH_ASSOC gives the values names like title and done. Lesson four’s template already looks up those keys.
Compare the page with the SQL results. If the second sample has ID 2, run this in your local MySQL query window. Substitute its actual ID if yours differs.
UPDATE tasks SET title = 'Run the example again' WHERE id = 2;
Refresh the browser and look for the new title. The PHP file stayed the same and the new text came from the table. The form title still only appears as a preview. Saving it is the next lesson.
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';
$error = null;
$input = '';
$preview = null;
if (($_SERVER['REQUEST_METHOD'] ?? 'GET') === 'POST') {
$input = is_string($_POST['title'] ?? null) ? $_POST['title'] : '';
$preview = validateTitle($input);
if ($preview === null) {
http_response_code(422);
$error = 'Enter a title of 1 to 120 bytes.';
}
}
$tasks = $pdo->query('SELECT id, title, done FROM tasks ORDER BY id')->fetchAll();
require __DIR__ . '/../app/view.php';
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);
Files carried forward from the previous lesson
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="/">
<label for="title">Task title</label>
<input id="title" name="title" value="<?= e($input) ?>" required>
<button><?= 'Preview task' ?></button>
</form>
<?php if ($preview !== null): ?><p>Preview: <?= e($preview) ?></p><?php endif; ?>
<?php if ($tasks === []): ?><p>No matching tasks.</p><?php endif; ?>
<ul>
<?php foreach ($tasks as $task): ?>
<li>
<?= e($task['title']) ?> — <?= $task['done'] ? 'Completed' : 'Pending' ?>
</li>
<?php endforeach; ?>
</ul>
</html>
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;
}
Related Articles