PDO — The basics

Guide 01 / 4

PDO glossary

Give meaning to PDO terms by tying them to the recipe mini-blog.

Expected outcome :

Explain the concepts in your own words and recognise them in an example.

Reading
0%

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

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.
Remember :

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.

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
Remember :

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.

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.
Remember :

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.

PHP → PDO → pilote pdo_mysql → serveur MySQL
MySQL → lignes → tableaux PHP → HTML
Remember :

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.

$dsn = "mysql:host=127.0.0.1;port=3306;dbname=blog_recettes;charset=utf8mb4";
Remember :

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.

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

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

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

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.

// La valeur arrive séparément du SQL.
$requete = $pdo->prepare("SELECT titre FROM recettes WHERE titre = :titre");
$requete->execute(["titre" => $titre]);
Remember :

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.

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.

session_start();
$_SESSION['csrf'] = bin2hex(random_bytes(32));
Remember :

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.

header('Location: recette.php?id=' . $nouvelId, true, 303);
exit;
Remember :

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.

$nouvelId = (int) $pdo->lastInsertId();
Remember :

Do not assume the new recipe will have id 4: AUTO_INCREMENT can have gaps.