Guide 01 / 4
PDO glossary
Give meaning to PDO terms by tying them to the recipe mini-blog.
Explain the concepts in your own words and recognise them in an example.
01 · Getting your bearings before you start
Step 6/6 of the “À table !” running project, a recipe mini-blog. Go at your own pace: read, reproduce, modify, then check.
The path and the outcome of this step
Connect PHP to the MySQL database from the previous course: search the recipes, view a recipe page and save a new recipe that remains available after reloading. The same dataset runs through all six courses: Soupe de carottes (20 min), Salade de tomates (10 min), Tarte aux pommes (45 min).
Example
01. HTML : structurer trois recettes dans une page sémantique.
02. CSS : rendre le blog lisible sur mobile et sur ordinateur.
03. JavaScript : filtrer les recettes sans recharger la page.
04. PHP : produire les pages depuis des tableaux et traiter un formulaire.
05. MySQL : conserver les recettes dans une base relationnelle.
06. PDO : lire et ajouter des recettes depuis PHP avec des requêtes préparées.
Each course has four guides: glossary, essentials, workshop and resources. The complete files are at the end of the workshop.
Prerequisites and environment
Have finished PHP and MySQL. Have the blog_recettes database created with the MySQL course's schema.sql, the three reference recipes and PHP 8.x with pdo_mysql and mbstring. Keep functions.php from the PHP course and style.css from the CSS course. The MySQL server must be started and reachable from PHP. This workshop is done on your local machine.
Example
mini-blog/
index.html # HTML, CSS et JavaScript
style.css # ajouté à l’étape CSS
app.js # ajouté à l’étape JavaScript
public/ # pages PHP accessibles par HTTP
includes/ # fonctions, connexion et données PHP
database/ # scripts SQL
Create this working folder separately from the Technipédia course files. The example paths are relative to mini-blog/. Do not replace the HTML page of this platform.
The method: understand, reproduce, transform
Read a sheet, then type its example. Explain what changes on screen. Change one thing at a time and compare with the expected result. Keep a copy of the previous step before moving on.
Example
1. Je lis et je reformule.
2. Je reproduis dans mon mini-blog.
3. Je change une valeur ou une règle.
4. Je vérifie et j’explique le résultat.
Short snippets illustrate a concept; only the files announced as complete are to be copied as they are. Fragments are not to be concatenated into a single file.
02 · Connecting PHP to the SQL engine
PDO is a PHP data-access interface, not a language nor an additional database.
PDO and driver
PDO offers an interface to talk to databases. The pdo_mysql driver lets it talk to MySQL. Queries are still written in the SQL dialect of the targeted server.
Example
PHP → PDO → pilote pdo_mysql → serveur MySQL
MySQL → lignes → tableaux PHP → HTML
Changing driver does not automatically make all the SQL compatible with another engine.
DSN
The DSN describes the connection type, the host, the port, the database name and the encoding. The account and password are passed separately to the constructor.
Example
$dsn = "mysql:host=127.0.0.1;port=3306;dbname=blog_recettes;charset=utf8mb4";
localhost may use a socket depending on the configuration; 127.0.0.1 here means a local TCP connection.
PDO instance and method
new PDO creates a connection object. A method is an action of that object, called with ->, such as prepare or query.
Example
$pdo = new PDO($dsn, $utilisateur, $motDePasse, $options);
$requete = $pdo->prepare("SELECT titre FROM recettes WHERE id = :id");
PDOStatement
A PDOStatement represents a prepared query or its result. execute starts the execution; fetch and fetchAll then read the rows.
Example
$requete->execute(["id" => 1]);
$recette = $requete->fetch();
Bound parameter
A parameter is a placeholder for an external value, separate from the SQL text. :id is a named parameter. It must not be wrapped in apostrophes in the query.
Example
$requete = $pdo->prepare("SELECT titre FROM recettes WHERE id = :id");
$requete->execute(["id" => 1]);
A parameter stands for a whole value, not a column name or an ORDER BY clause.
SQL injection
An injection occurs when an external value changes the structure of the query. Separating SQL and parameters stops that value from being interpreted as SQL code in the bound placeholders.
Example
// La valeur arrive séparément du SQL.
$requete = $pdo->prepare("SELECT titre FROM recettes WHERE titre = :titre");
$requete->execute(["titre" => $titre]);
Avoid concatenating $_GET or $_POST into the SQL. Validation is still needed for business rules.
03 · Understanding writing from a form
The form goes through several checks before saving.
Exception and try/catch
With ERRMODE_EXCEPTION, PDO reports a failure with an exception. try wraps the operation; catch intercepts the problem so you can respond without showing connection details.
Example
try {
$requete->execute();
} catch (PDOException $erreur) {
http_response_code(500);
exit("Opération indisponible.");
}
Session and CSRF token
A session links requests from the same browser through a cookie. A random token kept in the session and sent in the form lets you check that a write comes from the expected form.
Example
session_start();
$_SESSION['csrf'] = bin2hex(random_bytes(32));
The complete version generates the token only if it is missing and compares it with hash_equals. This token replaces neither authentication nor authorisations.
POST, redirect and GET
After a successful save via POST, the server responds with a 303 redirect to the recipe page via GET. Refreshing that page does not resubmit the form.
Example
header('Location: recette.php?id=' . $nouvelId, true, 303);
exit;
This organisation avoids resubmission caused by refreshing the final page; it does not guarantee the absence of duplicates if there are two separate submissions.
Inserted identifier and persistence
lastInsertId retrieves the identifier generated on this connection. After INSERT, the data is in MySQL and remains available to subsequent queries.
Example
$nouvelId = (int) $pdo->lastInsertId();
Do not assume the new recipe will have id 4: AUTO_INCREMENT can have gaps.