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.
Reproduce a working result, test it, then succeed at an exercise without copying.
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.
Example
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
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.
Example
http://127.0.0.1:8000/index.php
http://127.0.0.1:8000/index.php?q=soupe
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.
Example
id=1 → fiche de soupe
id=999999 → 404 si absent
id=abc → 400
id[]=1 → 400
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.
Example
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.
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.
Example
Attendu :
- erreurs lisibles ;
- aucune nouvelle ligne ;
- formulaire de nouveau disponible.
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.
Example
Titre : Soupe d'automne <maison>
Attendu : insertion réussie et chevrons visibles dans la fiche.
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.
Example
Jeton modifié → réponse 403, aucune insertion.
Actualisation après redirection → lecture de la fiche, aucune nouvelle insertion.
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.
Example
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.
Example
$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();
Keep this processing in the page's try/catch. Concatenating fixed SQL fragments is possible; external values stay bound.