PDO — The basics

Guide 02 / 4

PDO essentials

An annotated cheat sheet: what each element is for, how to write it and when to use it.

Expected outcome :

Choose and use the essential elements to complete this step of the blog.

Reading
0%

01 · Configuring an understandable connection

Configuration belongs to the server, never to the JavaScript code sent to the browser.

Checking the extensions

The PHP binary that starts the server must know pdo_mysql and mbstring. php -m displays the available extensions.

php -v
php -m
Remember :

could not find driver generally indicates that the matching PDO driver is not enabled.

The application's MySQL account

With a local administrator account, create an account limited to the blog's database. Replace the example password and remember the same account for the environment variables.

CREATE USER 'blog_app'@'127.0.0.1' IDENTIFIED BY 'REMPLACER_PAR_UN_MOT_DE_PASSE_LOCAL';
GRANT SELECT, INSERT, UPDATE, DELETE ON blog_recettes.* TO 'blog_app'@'127.0.0.1';
Remember :

Run once only in MySQL, not in PHP. The account does not need to create or drop tables; adapt the host to the local installation if necessary.

Environment variables and local server

In the Bash terminal that will start PHP, set the account and type the password without placing it in a saved command. Other systems use their equivalent mechanism to supply the same variables.

export DB_HOST=127.0.0.1
export DB_PORT=3306
export DB_NAME=blog_recettes
export DB_USER=blog_app
read -r -s -p 'Mot de passe MySQL : ' DB_PASSWORD
export DB_PASSWORD
php -S 127.0.0.1:8000 -t public
Remember :

These lines are typed from mini-blog/. Do not add SQL quotes to the password you type. No .env file is loaded automatically by these examples.

PDO constructor options

Exception mode makes error handling easier. FETCH_ASSOC provides named arrays. Disabling emulation asks the MySQL driver for native preparation.

$options = [
    PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION,
    PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC,
    PDO::ATTR_EMULATE_PREPARES => false,
];
Remember :

The complete includes/db.php file returns the configured connection; the pages load it with require.

02 · Preparing, executing and reading

First write the expected SQL, then add external values as parameters.

query for a fixed query

query directly runs a query whose text is entirely fixed. The categories do not depend on user input.

$categories = $pdo->query('SELECT id, nom FROM categories ORDER BY nom')->fetchAll();
Remember :

As soon as an external value is involved, use prepare and parameters.

prepare and execute

prepare describes the query's structure; execute supplies its values and runs it. The array keys match the parameter names.

$requete = $pdo->prepare('SELECT titre FROM recettes WHERE id = :id');
$requete->execute(['id' => 1]);
$recette = $requete->fetch();

bindValue with an explicit type

bindValue sets a value before execute. PARAM_INT specifies that it is an integer, useful for identifiers and limits.

$requete = $pdo->prepare('SELECT titre FROM recettes WHERE id = :id');
$requete->bindValue(':id', 1, PDO::PARAM_INT);
$requete->execute();
Remember :

For a given query, choose either execute with an array, or bindValue then execute without an array.

fetch, fetchAll and no result

fetch returns one row or false. fetchAll returns an array of rows, empty if there are none. An absence is not necessarily an SQL error.

$recette = $requete->fetch();
if ($recette === false) {
    http_response_code(404);
    exit("Recette introuvable.");
}
Remember :

For the small blog, fetchAll is readable. A large collection would call for pagination rather than loading everything.

LIKE and bound pattern

The parameter holds the complete pattern. Add % to the value passed, without putting it around the parameter name in the SQL.

$requete = $pdo->prepare('SELECT titre FROM recettes WHERE titre LIKE :recherche');
$requete->execute(['recherche' => '%' . $q . '%']);
Remember :

In this workshop, entered % and _ keep their meaning as SQL wildcards. It is a pattern search, not always a literal search.

Counting the read results

For a small result already loaded, use count. To count in the database, request SELECT COUNT(*) then fetchColumn.

$requete = $pdo->query('SELECT COUNT(*) FROM recettes');
$total = (int) $requete->fetchColumn();
Remember :

Do not use rowCount to count the rows of a SELECT: this behaviour is not portable.

03 · Writing and protecting the boundaries

The database stores raw text; HTML is escaped at display time.

INSERT and lastInsertId

List the columns and bind each value. The complete add file first checks all the fields and the category.

$requete = $pdo->prepare(
    'INSERT INTO categories (nom) VALUES (:nom)'
);
$requete->execute(['nom' => 'Goûter']);
$id = (int) $pdo->lastInsertId();
Remember :

Isolated excerpt to try only once: nom is UNIQUE. The complete workshop adds a recipe, not a category.

UPDATE and DELETE by identifier

For a modification or deletion, the checked identifier limits the row concerned. The excerpt below is a server-side trial, not an action triggered by a link.

$pdo->beginTransaction();
$requete = $pdo->prepare('UPDATE recettes SET duree_minutes = :duree WHERE id = :id');
$requete->execute(['duree' => 25, 'id' => 1]);
$pdo->rollBack();
Remember :

For a deletion: DELETE FROM recettes WHERE id = :id, with a parameter. An editing interface would require POST, a CSRF token and permission checks.

Transaction across several writes

When several writes must succeed together, start a transaction, commit after the last one, and roll back on exception. A single INSERT is already atomic in the workshop.

$pdo->beginTransaction();
try {
    // Exécuter ici les écritures préparées liées entre elles.
    $pdo->commit();
} catch (PDOException $erreur) {
    if ($pdo->inTransaction()) { $pdo->rollBack(); }
    throw $erreur;
}
Remember :

This template explains the sequence; it does not replace the project's queries.

The four form checks

Validation protects business rules; parameters protect the SQL placeholders; h protects HTML display; the token checks the form's expected origin. Each acts at a different place.

Entrée → vérifier type et bornes
Écriture → lier les valeurs SQL
Affichage → échapper avec h
Formulaire POST → comparer le jeton de session
Remember :

The examples are for a local workshop. A deployment with shared writing must also define who can add, modify or delete a recipe.