Les bases de données :

Introduction :

Les sites internet utilisent beaucoup les bases de données pour stocker des données saisies par les utilisateurs, mais également pour extraire du contenu afin de générer des pages. Les bases de données peuvent vitr devenir un maillon faible si quelques précautions ne sont pas prises pour éviter de trop les solliciter. Cela passe bien évidemment par le cache, mais également par une bonne conception de la base de données et des appels optimisés à celle-ci.

La conception de la base d données :

Pour qu'une base de données soit efficace et donc plus green IT, elle doit être bien conçue. Tout d'abord, la base de données ne doit contenir que les données qui sont strictement nécessaires. Comme nous avons déjà pris garde de créer uniquement des pages et des fonctionnalités essentielles et de ne pas demander via nos formulaires que des informations indispensables, normalement le travail est déjà bien préparé !

Le choix du système de gestion de base de données :

Le choix d'un système de gestion de base de données (SGBD) est généralement dicté par des considérations plus économiques et techniques (nombre d'accès...) qu'écologiques. Le choix de l'hébergeur et sa formule impose bien souvent le SGBD. La plupart des offres proposent le système de gestion de base de données MySQL ou son fork communautaire MariaDB. C'est pourquoi ce syllabus se concentre sur ces SGBD. Néanmoins, son contenu est pour l'essentiel transposable aux autres SGBD.

Le choix du moteur de stockage :

MySQL a plusieurs moteurs de stockage, mais deux sortent du lot : MyISAM et InnoDB. MyISAM est le moteur historique. Il a l'avantage d'être performant, mais l'inconvénient de ne pas gérer l'intégrité des données (pas de contraintes d'intégrité, pas de clésétrangères, pas de transactions...). Au contraire, InnoDB gère bien toute l'intégrité des donées, mais au prix de performances moindres.

Pour un site internet, la performance est généralement un critère plus important que l'intégrité des données. MyISAM semble donc une solution à privilégier pour un site web écoconçu.

Syntaxe pour la création d'une table avec le moteur MyISAM :

CREATE TABLE nom
(
    ...
) ENGINE = MYISAM;

MariaDB a également plusieurs moteurs de stockage. MyISAM est également présent. InnoDB a changé de nom pour s'appeler XtraDB. Le moteur Aria a été développé dans l'objectif de remplacer MyISAM.

Remarque : Le nom Aria du moteur de stockage n'a rien à voir avec l'ARIA de l'accessibilité. En réalité, ce moteur se nommait Maria, commele système de bases de données. Le M a été supprimé pour éviter la confusion entre les deux.

À nouveau, MyISAM est le plus performant pour les requêtes simples sans jointures, souvent utilisées dans les sites internet. La syntaxe de création est la même que celle avec MySQL.

Les types de données :

Il est important de choisir avec soin les types pour les attributs des tables. Vu le nobre important d'enregistrements contenus dans chaque table, cela peut entraîner des conséquences importantes sur les performances.

Les entiers :

En fonction de la plage de valeurs prévue pour un attribut de type numérique, il est possible de choisir le type le plus adapté.

Les dates :

Le type DATE ne contient que la date, le type TIME correspond à l'horaire et le type DATETIME contient les deux. Il est donc judicieux d'utiliser DATE à la place de DATETIME si l'horaire n'a pas d'importance.

Les chaînes de caractères :

Le type CHAR représente une chaîne de caractères de taille fixe. Ainsi pour une colonne de type CHAR, tous les enregistrements auront le même nombre de caractères pour cet attribut. Une colonne de type VARCHAR permet de stocker des chaînes de caractères de tailles différentes d'un enregistremnt à l'autre.

Les bases de données MySQL et MariaDB supportent l'Unicode, en particulier l'UTF-8. Cela permet de pouvoir utiliser l'ensemble des caractères existant sur la terre, mais également d'occuper un minimum de place.

Syntaxe :

CREATE TABLE nomTable
(
    ...
) CHARACTER SET 'utf8' COLLATE 'utf8mb4_unicode_ci';

Remarque : Néanmoins, si vous ne stockez que des caractères classiques, comme du texte en français, un autre encodage est possible : l'ISO/IEC 8859-1 (ou Latin1). Il utilise toujours un seul octet.

Les clés primaires :

Toutes les tables doivent respecter les trois premières formes normales (cfr chapitre sur la normalisation). En d'autres termes, chaque attribut ne contient qu'une seule information et chaque attribut a une dépendance fonctionnelle vis-à-vis de la clé primaire.

Chaque table doit évidemment posséder une clé primaire. Afin d'avoir des requêtes et des jointures efficaces, mieux vaut ne pas constituer de clés primaires composites et leur préférer un identifiant technique. La clé composite devient alors une clé secondaire.

Les clés primaires pour être efficaces doivent être de type entier ou des chaînes de caractères de taille fixe, avec cette taille la plus petite possible.

Exemples :

• idVelo SMALLINT UNSIGNED CONSTRAINT pk_velos PRIMARY KEY...

• idPedalo INT UNSIGNED CONSTRAINT pk_pedalos PRIMARY KEY...

• idVoilier CHAR (2) CONSTRAINT pk_voiliers PRIMARY KEY...

Les jointures :

Les clés étrangères peuvent être nommées comme les clés primaires qu'elles référencent. Cela permet d'effectuer des jointures en utilisant le mot-clé USING à la place de ON suivi de la condition de jointure.

Exemple :

SELECT *
FROM employes
INNER JOIN services USING (codeService);

Ceci est plus conscis et évite de potentielles erreurs dans la condition de jointure.

Afin d'accélérer les jointures, il est recommandé de créer des index sur les clés étrangères. Il ne faut pas le faire sur les clés primaires qui en possèdent déjà un.

Les index :

Les index peuvent améliorer les performances à condition qu'ils soient utilisés à bon escient et qu'ils ne soient pas trop nombreux. Un trop grand nombre d'index est pire d'un point de vue performance que pas d'index du tout ! Néanmoins, il est intéressant d'avoir des index sur les attributs constituant une clé étrangère ou utilisés régulièrement dans des restrictions (clause WHERE) ou des tris (clause ORDER BY).

Syntaxe :

CREATE INDEX nomIndex ON nomTable (nomColonnes);

L'avantagedes requêtes provenant d'un site web est qu'elles sont connues à l'avance. Ainsi, il est possible de les analyser pour déterminer les attributs nécessitant des index.

Les index pour la recherche textuelle :

Lorsqu'un site internet met en place un moteur de recherche, il faut qu'il puisse trouver les pages qui contiennent les termes recherchés. Si les articles sont contenus dans une base de données et si lespages sont créées dynamiquement, il faut alors rechercher ces termes dans la base.

Soit la table Articles :

CREATE TABLE articles
(
    idArticle INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    adresse VARCHAR (20) NOT NULL UNIQUE,
    titre VARCHAR (20) NOT NULL,
    contenu TEXT NOT NULL
) ENGINE = MYISAM;

Pour rechercher les pages contenant le terme saisi, il faut alors effectuer une recherche du type :

SELECT adresse
FROM articles
WHERE title LIKE '%motCleRecherche%'
OR contenu LIKE '%motCleRecherche%';

Cette requête ne peut pas être accélérée en utilisant un index classique. Heureusement, les index pour la recherche textuelle (full-text search en anglais) permettent d'indexer l'ensemble des mots contenus dans un ensemble d'attributs. Les mots trop courts ou trop longs ne sont toutefois pas indexés. Pour des raisons d'efficacité, il est préférable d'insérer les données en premier, puis de créer l'index ensuite.

Syntaxe :

CREATE FULLTEXT INDEX nomIndex ON nomTable (attributs);

Exemple :

CREATE FULLTEXT INDEX in_articles_fulltext_titrecontenu ON articles (titre, contenu);

Il est alors possible d'utiliser les fonctions MATCH() et AGAINST() avec la syntaxe suivante :

MATCH (attributs) AGAINST ('termesRecherches' [ IN BOOLEAN MODE ])

La requête de l'exemple précédent devient alors :

SELECT adresse
FROM articles
WHERE MATCH (titre, contenu)
AGAINST ('termesRecherches');

Les avantages de cette technique sont multiples : en plus d'être performante, elle permet d'obtenirles résultats triés par pertinence, de facilement rechercher plusieurs termes et d'en exclure certains en utilisant les mots-clés IN BOOLEAN MODE.

Exemple :

SELECT adresse
FROM articles
WHERE MATCH (titre, contenu)
AGAINST ('+SQL -Oracle' IN BOOLEAN MODE);

Dans cet exemple, il s'agit de rechercher les articles contenant le terme SQL, mais pas le terme Oracle.

Les procédures et les fonctions stockées :

Les avantages :

Les procédures et les fonctions stockées ont de nombreux avantages d'un point de vue performance et don d'un point de vue green IT.

Tout d'abord, elles permettent d'éviter de sursolliciter le réseau en limitant le nombre de requêtes envoyées au serveur de base de données. Par exemple, pour la création d'un compte utilisateur, imaginons une requête INSERT qui ajoute l'utilisateur dans une table utilisateurs, puis une autre requête INSERT qui ajoute son adresse dans une autre table. Plutôt que d'envoyer ces deux requêtes au serveur de base de données, il est possible de créer une procédure stockée ajouterUtilisateur () qui réalise les deux insertions. Ainsi, une seule requête est envoyée à la base de données.

Ensuite, le deuxième avantage est que la procédure ou fonction stockée a déjà été analysée et compilée. Ces deux étapes n'ont donc pas à être réalisées lors de l'appel à la procédure ou fonction. Elle est directement exécutée. Cela permet de gagner encore plus en performance.

Enfin, d'un point de vue sécurité, les procédures ou fonctions stockées sont également très avantageuses. Il est possible d'accorder à l'utilisateur de la base de données des droits uniques sur ces procédures ou fonctions stockées et non sur les tables. Ainsi, la surface d'attaque est moindre. De plus, en passant par ces procédures et fonctions stockées, il est possible de s'assurer de l'intégrité des données, même si le moteur de stockage ne le permet pas.

La mise en place :

Illustrons ceci avec l'exemple de l'ajout d'un utilisateur. La première étape est la création des tables utilisateurs et adresses en utilisant PHPMyAdmin :

CREATE TABLE utilisateurs
(
    id_utilisateur INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    nom VARCHAR (20) NOT NULL,
    prenom VARCHAR (20) NOT NULL,
    email VARCHAR (40) NOT NULL UNIQUE,
    id_adresse INT NOT NULL
) ENGINE = MYISAM;

CREATE TABLE adresses
(
    id_adresse INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    ligne1 VARCHAR (40) NULL,
    ligne2 VARCHAR (40) NULL,
    code_postal MEDIUMINT (5) ZEROFILL NOT NULL,
    ville VARCHAR (30) NOT NULL
) ENGINE = MYISAM;

La deuxième étape est la création de la procédure stockée :

DELIMITER |
CREATE PROCEDURE ajouterUtilisateur (IN nom VARCHAR (20), IN prenom VARCHAR (20), IN email VARCHAR (40), IN ligne1_adresse VARCHAR (40), IN ligne2_adresse VARCHAR (40), IN code_postal MEDIUMINT (5), IN ville VARCHAR (30))
BEGIN
    INSERT INTO adresses (ligne1, ligne2, code_postal, ville) VALUES (ligne1_adresse, ligne2_adresse, code_postal, ville);
    INSERT INTO utilisateurs (nom, prenom, email, id_adresse) VALUES (nom, prenom, email, LAST_INSERT_ID());
END |
DELIMITER;

Il est possible de tester celle-ci de la manière suivante :

CALL ajouterUtilisateur('MARCHIX', 'Agathe', 'agathe.marchix@eni.fr', '2A rue Benjamin Franklin', NULL, 44800, 'Saint-Herblain');

La troisième étape est la création d'un utilisateur avec des droits uniquement sur l'exécution des procédures stockées :

CREATE USER siteweb@'%' IDENTIFIED BY 'OILQcvXhvIzQj75s';
GRANT USAGE ON greenitaccess.* TO siteweb@'%' REQUIRE NONE WITH
MAX_QUERIES_PER_HOUR 0 MAX_CONNECTIONS_PER_HOUR 0
MAX_UPDATES_PER_HOUR 0 MAX_USER_CONNECTIONS 0;
GRANT EXECUTE ON greenitaccess.* TO siteweb@'%';

Il est possible de tester la connexion et l'appel à cette procédure stockée pour cet utilisateur avec le code PHP suivant :

<?php
    require_once 'connexion.php';
    $query = 'CALL ajouterUtilisateur (:nom, :prenom, :email, :ligne1, :ligne2, NULL, :cp, :ville);';
    $prep = $pdo->prepare($query);
    $prep->bindValue(':nom', 'MIGIEU');
    $prep->bindValue(':prenom', 'Madeleine');
    $prep->bindValue(':email', 'madeleine.migieu@eni.fr');
    $prep->bindValue(':ligne1', 'place de Brou');
    $prep->bindValue(':cp', 1000);
    $prep->bindValue(':ville', 'Bourg-en-Bresse');
    $prep->execute();
?>

Le fichier connexion.php :

<?php
    $pdo = new PDO('mysql:host=localhost;dbname=greenitaccess', 'siteweb', 'OILQcvXhvIzQj75s', [PDO::MYSQL_ATTR_INIT_COMMAND => "SET NAMES utf8"]);
    $pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
?>

Si, tout se passe bien, la connexion à la base de données est effectuée avec l'interface d'abstraction à l'accès aux données PDO. Ensuite, la requête est préparée et tous ses paramètres sont initialisés. Il est essentiel de passer par des paramètres pour éviter la faille de l'injection SQL. Enfin, la requête est exécutée et rien ne s'affiche.

Si au contraire, un message s'affiche, c'est qu'une erreur s'est produite. C'est par exemple le cas si vous actualisez la page. La procédure stockée est à nouveau exécutée et provoque une erreur car l'adresse e-mail doit être unique. Or elle a déjà été ajoutée en base de données lors de la première exécution.

Vous pouvez vérifier les résultats à l'aide de PHPMyAdmin et constater les insertons des nouvelles lignes.

Pour prouver que l'utilisateur de base de données créé ne peut pas exécutér d'autres instructions que les procédures et fonctions stockées, vous pouvez essayer d'envoyer un autre type de requête.

Exemple :

<?php
    require_once 'connexion.php';
    $query = 'INSERT INTO adresses (code_postal, ville) VALUES (:cp, :ville);';
    $prep = $pdo->prepare($query);
    $prep->bindValue(':cp', 35000);
    $prep->bindValue(':ville', 'Rennes');
    $prep->execute();
?>

Voici le message obtenu lors de l'exécution de ce script PHP :

Ce message vous indique clairement que l'utilisateur ne dispose pas des privilèges suffisants pour exécuter cette requête.

La gestion des collections :

Lorsque nous avons à manipuler des collections de données, il est assez tentant de créer une boucle for en PHP et d'effectuer dans cette boucle un voire plusieurs appels à la base de données pour effectuer le traitement. Il est préférable de n'effectuer qu'un seul appel pour l'ensemble de la collection.

Imaginons une page listant des utilisateurs. Il est possible d'en sélectionner plusieurs pour les supprimer.

Voici la page utilisateurs.php créée à cet effet :

<!DOCTYPE html>
<html lang="fr">
    <head>
        <meta charset="UTF-8">
        <meta http-equiv="X-UA-Compatible" content="IE=edge">
        <meta name="viewport" content="width=device-width, initial-scale=1.0">
        <title>Utilisateurs</title>
    </head>
    <body>
        <h1>Les utilisateurs et utilisatrices</h1>
        <?php
            session_start();
            if (isset($_SESSION['message'])) {
                echo '<p role="alert">' . $_SESSION['message'] . '</p>';
                unset($_SESSION['message']);
            }
            require_once 'connexion.php';
            $query = 'CALL listerUtilisateurs();';
            $utilisateurs = $pdo->query($query)->fetchAll(PDO::FETCH_ASSOC);
            if (count($utilisateurs) === 0) :
        ?>
            <p>Il n'y a actuellement aucun utilisateur</p>
        <?php else : ?>
            <form method="POST">
                <table>
                    <caption>La liste des utilisateurs et des utilisatrices</caption>
                    <tr>
                        <th scope="col">sélection</th>
                        <th scope="col">nom</th>
                        <th scope="col">prénom</th>
                    </tr>
                    <?php
                        for ($i = 0; $i < count($utilisateurs); $i++) {
                            echo '<tr><td><input type="checkbox" name="idsUtilisateurs[' . $utilisateurs[$i]['id_utilisateur'] . ']" value="' . $utilisateurs[$i]['id_utilisateur'] . '" aria-label="' . $utilisateurs[$i]['nom'] . ' ' . $utilisateurs[$i]['prenom'] . '"></td><td>' . $utilisateurs[$i]['nom'] . '</td><td>' . $utilisateurs[$i]['prenom'] . '</td></tr>';
                        }
                    ?>
                </table>
                <input type="submit" value="Supprimer les personnes sélectionnées" name="suppr" formaction="supprimerUtilisateurs.php">
            </form>
        <?php endif; ?>
    </body>
</html>

La partie sous le titre de niveau 1, permettra d'afficher les messages d'erreur ou de succès lorsqu'elle sera à nouveau affichée après une suppression.

Ensuite, la procédure suivante est appelée :

DELIMITER |
CREATE PROCEDURE listerUtilisateurs()
BEGIN
    SELECT id_utilisateur, nom, prenom FROM utilisateurs ORDER BY nom, prenom;
END |
DELIMITER;

En fonction des données récupérées, le tableau est créé ou un message est affiché s'il n'y a aucun utilisaateur. Toutes les cases à cocher pour sélectionner les personnes ont été pourvues d'un attribut aria-label contenant le nom de la personne afin d'être accessibles avec une lecture d'écran. Elles ont toutes un nom constitué de idsUtilisateurs suivi de l'identifiants des personnes cochées se retrouvent dans les paramètres POST dans un tableau nommé idsUtilisateurs. Le bouton de suppression possède un attribut formaction. Ainsi, si nous ajoutions d'autres actions groupées, elles pourraient être dans le même formulaire avec les mêmes cases à cocher. Par exemple, il serait imaginable d'avoir une réinitialisation de mots de passe pour l'ensemble des personnes sélectionnées.

Lors d'un clic sur le bouton de suppression, la page supprimerUtilisateurs.php est appelée :

<?php
    if (filter_input(INPT_POST, 'suppr', FILTER_SANITIZE_STRING)) {
        session_start();
        if (!isset($_POST['idsUtilisateurs'])) {
            $_SESSION['message'] = 'Aucun utilisateur sélectionné.';
        } else {
            $ids = filter_var_array($_POST['idsUtilisateurs'], FILTER_VALIDATE_INT);
            require_once 'connexion.php';
            $query = 'CALL supprimerUtilisateurs (:ids);';
            $prep = $pdo->prepare($query);
            $prep->bindValue(':ids', implode(', ', $ids));
            if ($prep->execute()) {
                $_SESSION['message'] = 'Suppression effectuée avec succès.';
            } else {
                $_SESSION['message'] = 'La suppression n\'a pas pu être effectuée.';
            }
        }
    }
    header('location: utilisateurs.php');
?>

Cette page commence par s'assurer que le formulaire a bien été soumis via le bouton de suppression. Elle teste si au moins une case à cocher a été sélectionnée. Si c'est bien le cas, tous les identifiants sont récupérés et vérifiés. Ils sont concaténés, avec une virgule en guise de caractère séparateur, et envoyés comme paramètre à la procédure suivante :

DELIMITER |
CREATE PROCEDURE supprimerUtilisateurs (IN ids VARCHAR (100))
BEGIN
    DELETE FROM adresses WHERE id_adresse IN (SELECT id_adresse FROM utilisateurs WHERE FIND_IN_SET (id_utilisateur, ids) <> 0);
    DELETE FROM utilisateurs WHERE FIND_IN_SET (id_utilisateur, ids) <> 0;
END |
DELIMITER;

En fonction du résultat, un message de succès ou d'échec est enregistré dans une variable de session et une redirection vers la page de consultation de la liste des utilisateurs est effectuée. Le message enregistré en session est alors affiché et retiré de la session pour ne pas réapparaître une seconde fois.

L'optimisation des requêtes :

Le nombre de colonnes :

Par facilité, en tant que développeurs, nous avons la mauvaise habitude d'utiliser des requêtes de type SELECT *. Cela est effectivement très pratique lors de la phase de développement de la base de données ou pour vérifier les données d'une table. Néanmoins, ce type d'instruction ne devrait pas se retrouver en production. D'une part, cela est plus coûteux que de lister l'ensemble des attributs de la table puisque le moteur de base de données doit commencer par les rechercher. D'autre part, nous n'avons généralement pas besoin de l'ensemble des attributs mais uniquement de certains. La projection effectuée en indiquant les attributs que doit retourner la requête diminue grandement les données transférées.

Le nombr de lignes :

La clause LIMIT :

Une fois que nous avons réduit le nomnre de colonnes récupérées par la requête, l'autre moyen de baisser le volume des données transférées est de réduire le nombre de lignes retournées par la requête. Il est possible de mettre en place une pagination pour afficher, non pas tous les résultats d'une requête, mais seulement les n premiers, et d'afficher les suivants à la demande de l'utilisateur. L'expérience montre que généralement, seuls les premiers résultats sont utilisés et cela permet à la fois de moins solliciter le serveur de base de données et de produire des pages plus légères.

Avec MySQL ou MariaDB, il est possible de réaliser cela avec la clause LIMIT.

Syntaxe :

SELECT ...
LIMIT debut, nombre;

La valeur nombre correspond au nombre de lignes à récupérer, et debut au nombre de lignes à sauter avant de commencer à récupérer les résultats. Par exemple, LIMIT 3, 2 récupère deux lignes après avoir sauté les trois premières, soit dans l'exemple suivant les lignes 4 et 5.

La pagination des résultats du moteur de recherche :

Supposons que nous souhaitons mettre en place un moteur de recherche sur notre site internet. Nous pouvons alors créer la procédure suivante utilisant LIMIT :

DELIMITER |
CREATE PROCEDURE rechercher (IN motsclefs VARCHAR (50), IN debut SMALLINT UNSIGNED, IN nombre TINYINT UNSIGNED)
BEGIN
    SELECT adresse, titre
    FROM articles
    WHERE MATCH (titre, contenu)
    AGAINST (motsclefs)
    LIMIT debut, nombre;
END |
DELIMITER;

Une fonction stockée est également nécessaire pour connaître le nombre de résultats :

DELIMITER |
CREATE FUNCTION nbResultats (motsclefs VARCHAR (50)) RETURNS INT
BEGIN
    DECLARE nb INT;
    SELECT COUNT (*) INTO nb
    FROM articles
    WHERE MATCH (titre, contenu)
    AGAINST (motsclefs);
    RETURN nb;
END |
DELIMITER;

Voici l'extrait de la page permettant de lancer la recherche :

<div role="search">
    <form method="GET" action="recherche.php">
        <input type="search" name="motsClefs">
        <input type="submit" value="Rechercher">
    </form>
</div>

Remarque : Comme cela a été dit précédemment, il est souhaitable que ce formulaire de recherche soit intégré dans le template de page afin qu'il soit présent sur toutes les pages du site.

Voici enfin la page affichant les résultats de la recherche (recherche.php) :

<?php
    const NB_PAR_PAGE = 5;
    $motsClefs = filter_input(INPUT_GET, 'motsClefs', FILTER_SANITIZE_STRING);
    $page = filter_input(INPUT_GET, 'page', FILTER_VALIDATE_INT) ?? 1;
    $debut = ($page - 1) * NB_PAR_PAGE;
    require_once 'connexion.php';
    session_start();
    if (!isset($_SESSION['recherche'][$motsClefs])) {
        $query = 'SELECT nbResultats(:motsClefs);';
        $prep = $pdo->prepare($query);
        $prep->bindValue(':motsClefs', $motsClefs);
        $prep->execute();
        $_SESSION['recherche'][$motsClefs] = (int) $prep->fetch(PDO::FETCH_NUM)[0];
    }
?>
<!DOCTYPE html>
<html lang="fr">
    <head>
        <meta charset="UTF-8">
        <meta http-equiv="X-UA-Compatible" content="IE=edge">
        <meta name="viewport" content="width=device-width, initial-scale=1.0">
        <title>Résulats pour <?= $motsClefs; ?></title>
    </head>
    <body>
        <?php if ($_SESSION['recherche'][$motsClefs] === 0) : ?>
            <p>Désolé mais nous n'avons trouvé aucune page correspondant à votre recherche</p>
        <?php else : ?>
            <ul>
                <?php
                    $query = 'CALL rechercher (:motsClefs, :debut, :nb);';
                    $prep = $pdo->prepare($query);
                    $prep->bindValue(':motsClefs', $motsClefs);
                    $prep->bindValue(':debut', $debut);
                    $prep->bindValue(':nb', NB_PAR_PAGE);

                    $prep->execute();
                    $arr = $prep->fetchAll(PDO::FETCH_ASSOC);
                    for ($i = 0; $i < count($arr); $i++) {
                        echo '<li><a href="' . $arr[$i]['adresse'] . '">' . $arr[$i]['titre'] . '</a></li>';
                    }
                ?>
            </ul>
            <?php
                if ($page !== 1) {
                    echo '<a href="recherche.php?motsClefs=' . $motsClefs . '&page=' . ($page - 1) . '">&lt; page précédente</a>';
                }
                if ($page !== (int) ceil ($_SESSION['recherche'][$motsClefs] / NB_PAR_PAGE)) {
                    echo '<a href="recherche.php?motsClefs=' . $motsClefs . '&page=' . ($page + 1) . '">page suivante &gt;</a>';
                }
            ?>
        <?php endif; ?>
    </body>
</html>

La constante NB_PAR_PAGE permet de choisir le nombre de résultats affichés par page. Les données envoyées en GET sont ensuite analysées. Si aucune valeur n'a été passée pour le paramètre page, c'est qu'il faut afficher la première page de résultats. La variable $debut est initialisée pour correspondre au nombre de lignes à ignorer avec la clause LIMIT. Pour éviter de calculer le nombre de résultats à chaque page, celui-ci est calculé une fois pour chaque nouvelle recherche en faisant appel à la fonction stockée nbResultats(), puis il est sauvegardé en session.

La page web est ensuite affichée. Si aucun résultat n'a été trouvé, alors un message d'erreur est affiché, sinon la procédure stockée rechercher() est appelée pour récupérer les résultats pour la page courante. Enfin, les liens vers les pages de résultats précédents et suivants sont affichés si celles-ci existent.

Les sous-requêtes corrélées :

Il existe deux catégories de sous-requêtes : les sous-requêtes imbriquées et les sous-requêtes corrélées. La différence entre les deux est qu'il est possible d'exécuter seule une sous-requête imbriquée alors que ce n'est pas possible pour une sous-requête corrélée. Celle-ci dépend d'au moins un élément de la requête principale :

Exemple de sous-requête imbriquée :

SELECT ligne1, ligne2, code_postal, ville
FROM adresses
WHERE id_adresse NOT IN (SELECT DISTINCT id_adresse FROM utilisateurs);

Exemple de sous-requête corrélée :

SELECT ligne1, ligne2, code_postal, ville
FROM adresses a
WHERE NOT EXISTS (SELECT * FROM utilisateurs u WHERE a.id_adresse = u.id_adresse);

Dans l'exemple de la sous-requête imbriquée, il est possible d'exécuter seule la sous-requête. Cela donne l'ensemble des identifiants des adresses qui sont associées à un utilisateur. Elle est donc exécutée une seule fois, puis la requête principale est exécutée à son tour avec les résultats de la précédente.

Dans l'exemple de la sous-requête corrélée, il n'est pas possible d'exécuter seule la sous-requête. Celle-ci doit être exécutée pour chaque ligne de la requête principale. Elle est donc exécutée un grand nombre de fois. Vous l'aure bien compris : une requête utilisant une sous-requête corrélée est moins performante qu'une requête équivalente utilisant une sous-requête imbriquée.

La bonne nouvelle est qu'il est toujours possible d'écrire différement une requête utilisant une sous-requête corrélée pour qu'elle n'en contienne plus. Dans les exemples ci-dessus, les deux requêtes récupèrent les mêmes résultats : les adresses qui ne correspondent à aucun utilisateur.

L'étude d'un cas pratique :

Pour le site internet associé à ce syllabus, nous avons choisi de ne pas utiliser une base de données. Les pages internet sont toutes statiques, sauf la page de contact. Cette solution est la plus économique , même s'il est possible, avec un système de cache, d'obtenir des performances similaires.