PDO — The basics

Guide 03 / 4

PDO workshop · À table !

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.

Expected outcome :

Reproduce a working result, test it, then succeed at an exercise without copying.

Reading
0%

01 · Assembling the connected blog

The five complete files below replace reading arrays with reading MySQL.

Step 1 · Checking the schema and the folders

Use exactly the MySQL course's schema.sql: table and column names, categories and initial data. Create db.php then replace index.php and recette.php with their PDO versions. Add ajouter.php and keep functions.php.

mini-blog/
├── public/
│   ├── index.php       # version PDO
│   ├── recette.php     # version PDO
│   ├── ajouter.php     # nouveau formulaire
│   └── style.css       # copie du cours CSS
├── includes/
│   ├── functions.php
│   └── db.php
└── database/
    └── schema.sql     # cours MySQL
Remember :

recettes.php and proposer.php from the previous step are no longer used by these new pages. Do not load the JavaScript course's DOM filtering script on these PHP pages.

Step 2 · Configuring and displaying the list

Create the limited MySQL account and set the environment variables as in the essentials. Start PHP, then open the list. Observe the join result and test the GET form.

http://127.0.0.1:8000/index.php
http://127.0.0.1:8000/index.php?q=soupe
Remember :

Expected on the initial database: three cards with no filter, one card for soupe, each with its category name.

Step 3 · Testing a recipe page

Open recette.php?id=1 then try a non-existent identifier and a text. The valid identifier is bound to the prepared query before reading.

id=1 → fiche de soupe
id=999999 → 404 si absent
id=abc → 400
id[]=1 → 400
Remember :

No technical SQL message should appear in the page.

Step 4 · Adding a complete compote

Open ajouter.php. Fill in all the fields, choose Dessert and submit. The page redirects to the new recipe page. Go back to the list, reload and find the recipe.

Titre : Compote de pommes
Durée : 15
Personnes : 2
Catégorie : Dessert
Ingrédients : 2 pommes [nouvelle ligne] Un peu d’eau
Préparation : Couper les pommes. [nouvelle ligne] Cuire puis écraser.
Remember :

Expected: four recipes if the database was initial. The new recipe remains in the database after stopping and restarting the PHP server.

02 · Checking the boundaries and extending

Test after succeeding with the normal path.

Check · Refusing inconsistent data

Temporarily remove the HTML limits in your browser tools. Send duration -1, servings 0, an empty title or a non-existent category. Server errors must prevent the insertion while keeping the other fields entered.

Attendu :
- erreurs lisibles ;
- aucune nouvelle ligne ;
- formulaire de nouveau disponible.
Remember :

The foreign key is a second check if a category disappears between validation and writing.

Check · Keeping special characters as data

Create a recipe whose title contains an apostrophe and angle brackets. Find it and check that the title is rendered literally.

Titre : Soupe d'automne <maison>
Attendu : insertion réussie et chevrons visibles dans la fiche.
Remember :

Values are bound for SQL and escaped for HTML. These are two distinct treatments.

Check · Checking the token and reloading

In your own form, change the value of the csrf field with the browser tools then submit. Reload the form to try again normally. After a successful add, refresh the recipe page.

Jeton modifié → réponse 403, aucune insertion.
Actualisation après redirection → lecture de la fiche, aucune nouvelle insertion.
Remember :

The csrf field is hidden by its HTML type; hidden does not mean tamper-proof. The server must compare it with the token kept in the session.

Exercise · A quick recipes page

In a copy of index.php, add a fixed 20-minute limit to the SQL. Preserve the optional title search and use an integer parameter for this limit.

Attendu avant les ajouts : soupe et salade. Après ajout de la compote : trois recettes rapides.

Solution · A fixed SQL structure and two bound values

Replace the building and execution of the list query with this processing. Each named parameter appears only once in the SQL.

$sql = 'SELECT r.id, r.titre, r.duree_minutes, r.portions, c.nom AS categorie
        FROM recettes AS r JOIN categories AS c ON c.id = r.categorie_id
        WHERE r.duree_minutes <= :limite';
if ($q !== '') { $sql .= ' AND r.titre LIKE :recherche'; }
$sql .= ' ORDER BY r.duree_minutes, r.id';
$requete = $pdo->prepare($sql);
$requete->bindValue(':limite', 20, PDO::PARAM_INT);
if ($q !== '') { $requete->bindValue(':recherche', '%' . $q . '%', PDO::PARAM_STR); }
$requete->execute();
$recettes = $requete->fetchAll();
Remember :

Keep this processing in the page's try/catch. Concatenating fixed SQL fragments is possible; external values stay bound.