Exercice : interroger une base de données depuis une page web

📚 Présentation

Exercice « façon tableur » : une page PHP interroge une base MariaDB et affiche le résultat dans un tableau triable en cliquant sur les en-têtes de colonnes, comme dans un tableur (Excel, LibreOffice Calc…).

Objectifs pédagogiques

1. Comprendre le cycle formulaire → requête SQL → affichage
2. Savoir écrire une requête SELECT avec des conditions (WHERE)
3. Comprendre pourquoi on utilise des requêtes préparées plutôt que d'insérer directement les valeurs saisies dans la requête SQL (injection SQL)
4. Trier les résultats en cliquant sur un en-tête de colonne

🗂️ Fichiers à créer

Fichier	Rôle
setup.sql	Crée la base bibliotheque_exercice et une table livres avec 15 livres d'exemple
db.php	Connexion PDO à la base MariaDB
index.php	Formulaire de recherche + affichage des résultats en tableau
style.css	Mise en forme « façon tableur » (lignes alternées, tri cliquable)

---

⚙️ Prérequis

Environnement nécessaire

• Apache 2.4+ avec mod_php ou PHP-FPM
• PHP 7.4+ avec l'extension pdo_mysql activée
• MariaDB 10.3+ (ou MySQL 5.7+)

---

🗂️ Installation pas à pas

1. Créer la base et les données

mysql -u root -p < setup.sql

(ou coller le contenu de setup.sql dans phpMyAdmin, onglet SQL)

2. Adapter les identifiants dans db.php

Ajuster $utilisateur et $motDePasse selon votre configuration MariaDB.

Bonne pratique en classe

Plutôt que d'utiliser root, créer un utilisateur MariaDB dédié avec des droits limités à cette seule base :

CREATE USER 'exercice'@'localhost' IDENTIFIED BY 'un_mot_de_passe';
GRANT ALL PRIVILEGES ON bibliotheque_exercice.* TO 'exercice'@'localhost';
FLUSH PRIVILEGES;

3. Déployer sur Apache

Placer le dossier dans le répertoire servi par Apache, par exemple :

sudo cp -r . /var/www/html/exercice-web-bdd/

Puis ouvrir http://localhost/exercice-web-bdd/ dans un navigateur.

---

🧩 Fonctionnalités

Fonctionnalité	Mécanisme
Recherche par titre ou auteur	LIKE sur deux colonnes, jointes par OR
Filtre par genre	Liste déroulante remplie via SELECT DISTINCT genre
Filtre « disponibles uniquement »	Case à cocher → condition disponible = 1
Tri façon tableur	Clic sur un en-tête de colonne, conserve les filtres en cours

---

🔐 Sécurité : le point clé pédagogique

Ne jamais faire ceci

$sql = "SELECT * FROM livres WHERE titre LIKE '%$recherche%'";

Que se passe-t-il si un élève tape %' OR '1'='1 dans le champ de recherche ? C'est le déclic classique pour comprendre l'injection SQL et pourquoi on utilise des marqueurs (:recherche) exécutés séparément de la requête.

Un piège plus subtil : le tri

Les requêtes préparées protègent les valeurs, pas les noms de colonnes ou de tables. Le nom de colonne de tri (?tri=...) ne peut donc pas passer par un simple :parametre. La solution retenue ici est une liste blanche ($colonnesAutorisees) : toute valeur absente de cette liste est ignorée au profit d'une valeur par défaut.

• Anti-injection SQL sur les valeurs : requêtes préparées PDO avec placeholders nommés
• Anti-injection SQL sur les noms de colonnes : liste blanche de valeurs autorisées
• Anti-XSS : toutes les sorties HTML passent par htmlspecialchars()
• Identifiants de connexion en clair dans db.php : acceptable en local pour l'exercice, jamais en production — bon sujet de discussion en classe (pourquoi ne verse-t-on jamais un mot de passe de base de données sur GitHub ?)

---

🎓 Déroulé pédagogique suggéré

1. Lecture guidée : commencer par db.php, le plus court — il introduit PDO et la notion de connexion.
2. Suivre le trajet d'une requête : partir du formulaire HTML dans index.php, montrer comment $_GET récupère les valeurs saisies, puis comment elles se retrouvent (ou non !) dans la requête SQL.
3. Le point clé : les requêtes préparées (voir encadré ci-dessus).
4. Pistes d'extension, par ordre de difficulté :
   • Ajouter un filtre par année (avant/après une date donnée)
   • Ajouter une colonne « nombre de pages » et l'afficher
   • Limiter l'affichage à 10 résultats par page (pagination)
   • Ajouter un bouton pour exporter les résultats affichés en CSV
   • (Plus avancé) Ajouter une table auteurs séparée et faire une jointure — première approche du modèle relationnel

---

🗄️ Structure de la table livres

CREATE TABLE livres (
    id          INT AUTO_INCREMENT PRIMARY KEY,
    titre       VARCHAR(150) NOT NULL,
    auteur      VARCHAR(100) NOT NULL,
    annee       SMALLINT     NOT NULL,
    genre       VARCHAR(50)  NOT NULL,
    disponible  TINYINT(1)   NOT NULL DEFAULT 1  -- 1 = emprunt possible, 0 = déjà emprunté
);

---

💻 Code source complet

Copiez chacun des blocs ci-dessous dans un fichier du même nom, dans le même dossier.

setup.sqldb.phpindex.phpstyle.css

-- ============================================================
--  setup.sql
--  Script de création de la base de données pour l'exercice
--  "Interroger une base de données depuis une page web"
-- ============================================================
--
--  À exécuter une seule fois, par exemple avec :
--     mysql -u root -p < setup.sql
--  ou en collant le contenu dans phpMyAdmin (onglet SQL).
-- ============================================================

-- On crée une base dédiée à l'exercice, pour ne pas mélanger
-- avec d'autres bases déjà présentes sur le serveur.
CREATE DATABASE IF NOT EXISTS bibliotheque_exercice
    CHARACTER SET utf8mb4
    COLLATE utf8mb4_unicode_ci;

USE bibliotheque_exercice;

-- Une seule table suffit pour l'exercice : c'est volontaire,
-- l'objectif est de comprendre le principe, pas de gérer un
-- schéma relationnel complexe (ça viendra plus tard !).
CREATE TABLE IF NOT EXISTS livres (
    id          INT AUTO_INCREMENT PRIMARY KEY,
    titre       VARCHAR(150) NOT NULL,
    auteur      VARCHAR(100) NOT NULL,
    annee       SMALLINT     NOT NULL,
    genre       VARCHAR(50)  NOT NULL,
    disponible  TINYINT(1)   NOT NULL DEFAULT 1  -- 1 = emprunt possible, 0 = déjà emprunté
);

-- On vide la table avant de réinsérer, pour pouvoir relancer
-- ce script sans dupliquer les données si besoin.
TRUNCATE TABLE livres;

INSERT INTO livres (titre, auteur, annee, genre, disponible) VALUES
('Le Petit Prince',                 'Antoine de Saint-Exupéry', 1943, 'Conte',          1),
('1984',                            'George Orwell',            1949, 'Science-fiction',1),
('Le Comte de Monte-Cristo',        'Alexandre Dumas',          1844, 'Aventure',       0),
('Fondation',                       'Isaac Asimov',             1951, 'Science-fiction',1),
('Vingt mille lieues sous les mers','Jules Verne',              1870, 'Aventure',       1),
('Germinal',                        'Émile Zola',               1885, 'Roman social',   0),
('Dune',                            'Frank Herbert',            1965, 'Science-fiction',1),
('Notre-Dame de Paris',             'Victor Hugo',              1831, 'Roman',          1),
('L''Étranger',                     'Albert Camus',             1942, 'Philosophie',    0),
('Le Nom de la rose',               'Umberto Eco',               1980, 'Policier',       1),
('Chronique des Bridgerton',        'Julia Quinn',               2000, 'Romance',        1),
('La Peste',                        'Albert Camus',              1947, 'Philosophie',    1),
('Cyrano de Bergerac',              'Edmond Rostand',            1897, 'Théâtre',        0),
('Voyage au centre de la Terre',    'Jules Verne',               1864, 'Aventure',       1),
('Le Meilleur des mondes',          'Aldous Huxley',             1932, 'Science-fiction',0);

<?php
/**
 * ============================================================
 *  db.php — Connexion à la base de données
 * ============================================================
 *
 *  Ce fichier ne fait qu'UNE chose : ouvrir une connexion vers
 *  la base MariaDB et la mettre à disposition des autres pages
 *  via la variable $pdo.
 *
 *  On sépare volontairement la connexion (ce fichier) de
 *  l'affichage (index.php) : c'est une bonne habitude à prendre
 *  dès le début, même sur un petit projet.
 *
 *  POURQUOI "PDO" ?
 *  -----------------
 *  PDO (PHP Data Objects) est une interface commune pour parler
 *  à différentes bases de données (MySQL/MariaDB, SQLite,
 *  PostgreSQL...). Le code d'accès aux données change très peu
 *  si un jour vous changez de moteur de base de données.
 * ============================================================
 */

// --- Paramètres de connexion ---------------------------------
// À adapter selon votre installation (utilisateur et mot de
// passe MariaDB, nom d'hôte...).
$hote        = '127.0.0.1';
$nomBase     = 'bibliotheque_exercice';
$utilisateur = 'root';        // ⚠️ à remplacer par un utilisateur dédié en dehors du cadre pédagogique
$motDePasse  = '';             // ⚠️ idem : à définir selon votre configuration MariaDB

// DSN = "Data Source Name" : une chaîne qui décrit où et
// comment se connecter.
$dsn = "mysql:host={$hote};dbname={$nomBase};charset=utf8mb4";

// Options : on demande à PDO de nous signaler les erreurs sous
// forme d'exceptions PHP, beaucoup plus faciles à repérer et à
// comprendre qu'un simple "false" silencieux.
$options = [
    PDO::ATTR_ERRMODE            => PDO::ERRMODE_EXCEPTION,
    PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC, // résultats sous forme de tableaux associatifs
];

try {
    $pdo = new PDO($dsn, $utilisateur, $motDePasse, $options);
} catch (PDOException $e) {
    // En développement/pédagogie, on affiche l'erreur pour comprendre.
    // Dans une vraie application en production, on ne montrerait
    // JAMAIS le détail de l'erreur à l'utilisateur (fuite d'information).
    die('Erreur de connexion à la base de données : ' . $e->getMessage());
}

<?php
/**
 * ============================================================
 *  index.php — Exercice : interroger une base de données
 *              et afficher le résultat façon "tableur"
 * ============================================================
 *
 *  Objectifs pédagogiques de cet exercice :
 *   1. Comprendre le cycle "formulaire → requête SQL → affichage"
 *   2. Savoir écrire une requête SELECT avec des conditions (WHERE)
 *   3. Comprendre POURQUOI on utilise des requêtes PRÉPARÉES
 *      plutôt que d'insérer directement les valeurs de
 *      l'utilisateur dans la requête SQL (sécurité : injection SQL)
 *   4. Trier les résultats en cliquant sur les en-têtes de colonnes,
 *      comme dans un tableur (Excel, LibreOffice Calc...)
 * ============================================================
 */

require 'db.php'; // nous donne accès à $pdo (voir db.php)

// ------------------------------------------------------------
// 1) RÉCUPÉRATION DES CRITÈRES ENVOYÉS PAR LE FORMULAIRE
// ------------------------------------------------------------
// Le formulaire utilise la méthode GET : les critères se
// retrouvent donc dans l'URL (ex: ?recherche=dune&genre=...).
// C'est pratique pour un exercice : on peut partager le lien
// d'une recherche, et on voit "ce qui se passe" dans l'URL.

// On utilise "?? ''" (opérateur de coalescence nulle) pour dire :
// "si ce champ n'existe pas dans le formulaire, prends une chaîne vide".
$recherche   = trim($_GET['recherche'] ?? '');
$genre       = trim($_GET['genre'] ?? '');
$disponibles = isset($_GET['disponibles']); // case à cocher : présente ou non dans $_GET

// Colonne et sens de tri (façon tableur : on clique sur un en-tête).
$triColonne = $_GET['tri'] ?? 'titre';
$triSens    = $_GET['sens'] ?? 'asc';

// ⚠️ POINT DE SÉCURITÉ IMPORTANT ⚠️
// Le nom de la colonne et le sens de tri viennent de l'utilisateur
// (donc potentiellement d'une personne malveillante). On ne peut
// PAS les mettre tels quels dans la requête SQL, même avec une
// requête préparée (les requêtes préparées protègent les VALEURS,
// pas les noms de colonnes ou de tables).
// La solution : une liste blanche ("whitelist") des valeurs autorisées.
$colonnesAutorisees = ['titre', 'auteur', 'annee', 'genre', 'disponible'];
if (!in_array($triColonne, $colonnesAutorisees, true)) {
    $triColonne = 'titre'; // valeur de secours si quelqu'un bidouille l'URL
}
$triSens = ($triSens === 'desc') ? 'DESC' : 'ASC';

// ------------------------------------------------------------
// 2) CONSTRUCTION DE LA REQUÊTE SQL
// ------------------------------------------------------------
// On construit la requête morceau par morceau, en ajoutant des
// conditions WHERE seulement si l'utilisateur a rempli le champ
// correspondant. Les VALEURS ne sont jamais écrites directement
// dans le texte de la requête : on met des "marqueurs" (:recherche,
// :genre) et on fournit les vraies valeurs séparément avec
// execute(). C'est ça, une "requête préparée".

$sql = 'SELECT id, titre, auteur, annee, genre, disponible FROM livres WHERE 1=1';
$parametres = [];

if ($recherche !== '') {
    // LIKE avec des % permet une recherche "contient" sur le titre OU l'auteur.
    $sql .= ' AND (titre LIKE :recherche OR auteur LIKE :recherche)';
    $parametres['recherche'] = '%' . $recherche . '%';
}

if ($genre !== '') {
    $sql .= ' AND genre = :genre';
    $parametres['genre'] = $genre;
}

if ($disponibles) {
    $sql .= ' AND disponible = 1';
}

// Le nom de colonne et le sens de tri sont ici injectés directement
// dans la chaîne SQL — ce qui n'est autorisé QUE parce qu'on les a
// validés juste au-dessus via la liste blanche. Ne jamais faire ça
// avec une valeur non contrôlée !
$sql .= " ORDER BY {$triColonne} {$triSens}";

// ------------------------------------------------------------
// 3) EXÉCUTION DE LA REQUÊTE
// ------------------------------------------------------------
$requete = $pdo->prepare($sql);
$requete->execute($parametres);
$livres = $requete->fetchAll(); // tableau associatif de tous les résultats

// Pour remplir la liste déroulante des genres, on récupère aussi
// la liste distincte de tous les genres existants dans la table.
$genresDisponibles = $pdo->query('SELECT DISTINCT genre FROM livres ORDER BY genre')
                          ->fetchAll(PDO::FETCH_COLUMN);

/**
 * Petite fonction utilitaire : construit l'URL de tri pour un
 * en-tête de colonne donné, en conservant les autres critères
 * de recherche déjà saisis (comme dans un vrai tableur, trier
 * ne doit pas effacer les filtres en cours).
 */
function urlTri(string $colonne, string $triColonneActuelle, string $triSensActuel): string
{
    $nouveauSens = ($colonne === $triColonneActuelle && $triSensActuel === 'ASC') ? 'desc' : 'asc';
    $parametresUrl = array_merge($_GET, ['tri' => $colonne, 'sens' => $nouveauSens]);
    return '?' . http_build_query($parametresUrl);
}
?>
<!DOCTYPE html>
<html lang="fr">
<head>
    <meta charset="UTF-8">
    <title>Exercice : base de données façon tableur</title>
    <link rel="stylesheet" href="style.css">
</head>
<body>

<h1>📚 Bibliothèque — recherche de livres</h1>
<p class="intro">
    Cet exercice illustre comment une page web interroge une base de données
    (MariaDB) et affiche le résultat sous forme de tableau, comme dans un
    tableur : cliquez sur un en-tête de colonne pour trier.
</p>

<!-- ============================================================
     FORMULAIRE DE RECHERCHE
     Méthode GET : les critères apparaissent dans l'URL, ce qui
     permet de bien observer "ce qui part vers le serveur".
     ============================================================ -->
<form method="get" class="formulaire-recherche">

    <label for="recherche">Titre ou auteur :</label>
    <input
        type="text"
        id="recherche"
        name="recherche"
        value="<?= htmlspecialchars($recherche) ?>"
        placeholder="ex : Verne, Dune...">

    <label for="genre">Genre :</label>
    <select id="genre" name="genre">
        <option value="">— Tous —</option>
        <?php foreach ($genresDisponibles as $g): ?>
            <option value="<?= htmlspecialchars($g) ?>" <?= $g === $genre ? 'selected' : '' ?>>
                <?= htmlspecialchars($g) ?>
            </option>
        <?php endforeach; ?>
    </select>

    <label class="case-a-cocher">
        <input type="checkbox" name="disponibles" <?= $disponibles ? 'checked' : '' ?>>
        Disponibles uniquement
    </label>

    <!-- On conserve la colonne et le sens de tri même après une nouvelle recherche -->
    <input type="hidden" name="tri" value="<?= htmlspecialchars($triColonne) ?>">
    <input type="hidden" name="sens" value="<?= htmlspecialchars(strtolower($triSens)) ?>">

    <button type="submit">Rechercher</button>
    <a href="?" class="lien-reinitialiser">Réinitialiser</a>
</form>

<p class="compteur">
    <?= count($livres) ?> résultat<?= count($livres) > 1 ? 's' : '' ?>
</p>

<!-- ============================================================
     TABLEAU DE RÉSULTATS — style "tableur"
     ============================================================ -->
<table class="tableur">
    <thead>
        <tr>
            <th><a href="<?= urlTri('titre', $triColonne, $triSens) ?>">Titre</a></th>
            <th><a href="<?= urlTri('auteur', $triColonne, $triSens) ?>">Auteur</a></th>
            <th><a href="<?= urlTri('annee', $triColonne, $triSens) ?>">Année</a></th>
            <th><a href="<?= urlTri('genre', $triColonne, $triSens) ?>">Genre</a></th>
            <th><a href="<?= urlTri('disponible', $triColonne, $triSens) ?>">Disponible</a></th>
        </tr>
    </thead>
    <tbody>
        <?php if (empty($livres)): ?>
            <tr>
                <td colspan="5" class="aucun-resultat">Aucun livre ne correspond à ces critères.</td>
            </tr>
        <?php else: ?>
            <?php foreach ($livres as $livre): ?>
                <tr>
                    <td><?= htmlspecialchars($livre['titre']) ?></td>
                    <td><?= htmlspecialchars($livre['auteur']) ?></td>
                    <td class="colonne-nombre"><?= (int)$livre['annee'] ?></td>
                    <td><?= htmlspecialchars($livre['genre']) ?></td>
                    <td class="colonne-centre">
                        <?= $livre['disponible'] ? '✅' : '❌' ?>
                    </td>
                </tr>
            <?php endforeach; ?>
        <?php endif; ?>
    </tbody>
</table>

</body>
</html>

/* ============================================================
   style.css — apparence "tableur" pour l'exercice
   ============================================================ */

body {
    font-family: "Segoe UI", Arial, sans-serif;
    max-width: 900px;
    margin: 2rem auto;
    padding: 0 1rem;
    color: #222;
    background: #f7f7f7;
}

h1 {
    font-size: 1.5rem;
}

.intro {
    color: #555;
    margin-bottom: 1.5rem;
}

/* --- Formulaire --- */
.formulaire-recherche {
    display: flex;
    flex-wrap: wrap;
    align-items: center;
    gap: 0.6rem 1rem;
    background: #fff;
    border: 1px solid #ddd;
    border-radius: 6px;
    padding: 1rem;
    margin-bottom: 1rem;
}

.formulaire-recherche label {
    font-weight: 600;
    font-size: 0.9rem;
}

.formulaire-recherche input[type="text"],
.formulaire-recherche select {
    padding: 0.35rem 0.5rem;
    border: 1px solid #ccc;
    border-radius: 4px;
}

.case-a-cocher {
    display: flex;
    align-items: center;
    gap: 0.3rem;
    font-weight: normal;
}

.formulaire-recherche button {
    background: #2c6e49;
    color: white;
    border: none;
    padding: 0.45rem 1rem;
    border-radius: 4px;
    cursor: pointer;
}

.formulaire-recherche button:hover {
    background: #1f4f34;
}

.lien-reinitialiser {
    font-size: 0.85rem;
    color: #666;
    text-decoration: underline;
}

.compteur {
    font-size: 0.85rem;
    color: #666;
    margin-bottom: 0.4rem;
}

/* --- Tableau "façon tableur" --- */
table.tableur {
    width: 100%;
    border-collapse: collapse;
    background: white;
    box-shadow: 0 1px 3px rgba(0,0,0,0.08);
}

table.tableur th,
table.tableur td {
    border: 1px solid #ddd;
    padding: 0.5rem 0.7rem;
    text-align: left;
    font-size: 0.92rem;
}

table.tableur thead th {
    background: #e9ede9;
    position: sticky;
    top: 0;
}

table.tableur thead th a {
    color: #222;
    text-decoration: none;
    display: block;
}

table.tableur thead th a:hover {
    text-decoration: underline;
}

table.tableur tbody tr:nth-child(even) {
    background: #fafafa;
}

table.tableur tbody tr:hover {
    background: #f0f4f0;
}

.colonne-nombre {
    text-align: right;
}

.colonne-centre {
    text-align: center;
}

.aucun-resultat {
    text-align: center;
    color: #888;
    font-style: italic;
    padding: 1.2rem;
}