November 19, 2026 - 11:04
PHP Lesson 05 — Create a MySQL Table and Read Tasks with PDO Image
PHP Tutorials

PHP Lesson 05 — Create a MySQL Table and Read Tasks with PDO

0 Comments

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.

PDO MySQL

Create the table and application account

Run these commands in your MySQL query window. This example assumes a fresh local setup.

SQL
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.

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;

Insert two rows once so we have something to list. The first task is done and the second is pending.

SQL
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.

SQL
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

POWERSHELL
$env:DB_USER = 'php_tasks'
$env:DB_PASS = 'YOUR_LOCAL_PASSWORD'
php -S 127.0.0.1:8000 -t public

Bash / Zsh

BASH
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.

SQL
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.

PHP
$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.

PHP
$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.

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.

PHP
$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.

SQL
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
<?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
<?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
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
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
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
<?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;
}

PDO MySQL

Related Articles

Comments (0)

No comments yet. Be the first to comment!

Leave a Comment