Paginer avec PHP et SQL

Pour commencer, prenons un code simple qui affiche toutes ces données. Il nous servira aussi de base pour continuer le tutoriel, mais je n’en reprendrai que quelques parties au gré des points à observer.

<?php
$cnx = new PDO('mysql:host=localhost;dbname=my_database;charset=utf8', 'root', '');

// Partie "Requête"
$query = 'SELECT * FROM `my_table`';
$resultSet = $cnx->query($query);

// Partie "Boucle"
while ($element = $resultSet->fetch()) {
    // C'est là qu'on affiche les données  :)
}

Des solutions peu optimales…

Maintenant que nous avons notre code de travail, mettons-nous à l’ouvrage ! 
Il nous a été demandé de n’afficher que 10 éléments à la fois.

Le compteur d’itérations

En regardant la ligne 9, on voit de suite qu’on va avoir autant d’itérations qu’il y a de résultats.
Du coup, la première solution qui vient à l’esprit, c’est de placer une condition sur le nombre de résultats traités pour sortir de la boucle au bon moment, ce qui nous donnerait ceci :

<?php
// Partie "Boucle"
$limite = 10;
$compteur = 0;
while ($element = $resultSet->fetch()) {
    if ($compteur >= $limite) {
        break;
    }
    // C'est là qu'on affiche les données  :)
    $compteur++;
}

Cette méthode fonctionne, vous vous frottez les mains d’avoir trouvé une solution rapidement et qui plus est simple à mettre en place.
Malheureusement, elle reste consommatrice de mémoire de manière inutile : nous allons récupérer tous les résultats pour n’en utiliser au final que 10. Si ce n’est pas trop gênant quand il n’y a que 20 enregistrements, imaginez quand il y en a au-delà de 8000…

La requête dans la boucle

Si le souci est qu’on récupère trop de résultats, nous pouvons alors mettre la requête dans la boucle, ce qui nous donnerait quelque chose dans le genre de ce qui suit :

<?php
$cnx = new PDO('mysql:host=localhost;dbname=my_database;charset=utf8', 'root', '');

// Partie "Boucle"
$limite = 10;
for ($i = 0; $i < $limite; $i++) {
    // Partie "Requête"
    $query = 'SELECT * FROM `my_table`';
    $resultSet = $cnx->query($query);
    $element = $resultSet->fetch();
    // C'est là qu'on affiche les données  :)
}

Hé oui, ce qui semblait être une solution logique implique plus de changements que ce qu’on pourrait penser. Il faudrait savoir quel élément a déjà été affiché, et récupérer le premier qui ne l’a pas été. Nous pourrions simplifier, et ne garder que le dernier. Mais il faudrait…

Cette solution, outre la complexité qu’elle amène sans en avoir l’air, n’est toujours pas optimale. En plus de la mémoire nécessaire pour garder les résultats à portée de main, il faut aussi penser aux temps d’exécution des requêtes.

Un gestionnaire de bases de données est un serveur comme Apache, nginx ou IIS. On lui envoie une requête qu’il traite avant d’envoyer la réponse, que PHP stocke en mémoire. On voit donc qu’il y a au final plus d’un point à prendre en compte :

1. Le temps d’accès au serveur
   On ne peut pas trop influer dessus, cela dépend de la machine (ou des machines) sur laquelle (ou lesquelles) sont installés PHP et MySQL.
2. Le temps de traitement
   C’est très lié à la complexité de la requête et à la quantité de résultats, nous pouvons donc en tout cas essayer de demander juste ce qu’il nous faut. Mais ce n’est en général pas le plus gênant, les gestionnaires de bases de données sont suffisamment performants sur ce point. 
3. La mémoire pour enregistrer cette réponse
   Là aussi, essayer de demander uniquement ce que l’on va utiliser permet déjà de faire mieux.

Au final, c’est donc le temps d’accès qui pose problème. Ce temps d’accès va rester quasiment identique à chaque requête exécutée, donc il faut trouver un moyen pour le diminuer.
Si nous arrivions à mettre en place la seconde proposition, ce temps serait conséquent. La solution est donc de demander directement à MySQL uniquement 10 résultats à la fois. Une seule requête au lieu de 10, donc :

• nous n’avons pas 10 fois le temps d’accès qui entre en compte
• nous ne surchargeons pas la mémoire de résultats inutiles
• nous ne nous cassons plus trop la tête pour savoir lequel est affiché, lequel ne l’est pas 

Néanmoins, il nous manque encore la manière de faire pour dire à MySQL de se limiter à 10 résultats.

Une solution optimale avec LIMIT

Pour ceux qui connaissent un peu le SQL ou qui sont allés chercher des solutions ailleurs, vous connaissez peut-être déjà la clause LIMIT. Et pour ceux qui la découvrent maintenant, on va expliquer ce qu’elle permet.  

LIMIT : rien que le mot est évocateur. Et il se trouve que c’est bien l’anglais pour « limite ». Il sert donc à limiter le nombre de résultats retournés par une requête. Nous avons désormais de quoi n’avoir que les 10 enregistrements souhaités. Si nous reprenons la partie "Requête" de notre code de travail de départ et que nous l’adaptons, nous avons ceci :

<?php
$cnx = new PDO('mysql:host=localhost;dbname=my_database;charset=utf8', 'root', '');

$limite = 10;

// Partie "Requête"
/* On construit la requête, en remplaçant les valeurs par des marqueurs. Ici, on
 * n'a qu'une valeur, limite. On place donc un marqueur là où la valeur devrait se
 * trouver, sans oublier les deux points « : » */
$query = 'SELECT * FROM `my_table` LIMIT :limite';
/* On prépare la requête à son exécution. Les marqueurs seront identifiés */
$query = $cnx->prepare($query);
/* On lie ici une valeur à la requête, soit remplacer de manière sûre un marqueur par
 * sa valeur, nécessaire pour que la requête fonctionne. */
$query->bindValue(
    'limite',         // Le marqueur est nommé « limite »
     $limite,         // Il doit prendre la valeur de la variable $limite
     PDO::PARAM_INT   // Cette valeur est de type entier
);
/* Maintenant qu'on a lié la valeur à la requête, on peut l'exécuter */
$query->execute();

// Partie "Boucle"
/* Ce qui se trouvait avant dans $resultSet est désormais dans $query, donc on doit
 * modifier ici aussi */
while ($element = $query->fetch()) {
    // C'est là qu'on affiche les données  :)
}

Nous avons désormais un code qui nous affiche juste le nombre souhaité d’éléments, qui est rapide, et qui ne consomme pas trop de mémoire. C’est là un bon début.

Ce qu’il nous faut maintenant, c’est de quoi afficher les éléments suivants. Car si nous n’avons plus 10 fois le même, les autres que les 10 premiers doivent être accessibles aussi ! 

Maintenant que nous savons comment afficher quelques éléments à la fois, nous allons voir comment faire en sorte d’avoir la possibilité de voir toutes les pages, pas seulement la première.

Comme auparavant, reprenons une base de départ.

<?php
$cnx = new PDO('mysql:host=localhost;dbname=my_database;charset=utf8', 'root', '');

$limite = 10;

// Partie "Requête"
$query = 'SELECT * FROM `my_table` LIMIT :limite';
$query = $cnx->prepare($query);
$query->bindValue('limite', $limite, PDO::PARAM_INT);
$query->execute();

// Partie "Boucle"
while ($element = $query->fetch()) {
    // C'est là qu'on affiche les données  :)
}

Et, une fois n’est pas coutume, un peu de réflexion s’impose avant de foncer tête baissée dans le code.

Nous savons que l’on aimerait afficher 10 éléments à la fois. Seulement, il nous faut de quoi afficher les 10 suivants, et aussi les 10 précédents, bref, de pouvoir naviguer d’une page à la suivante ou la précédente.

Liens « Suivant » et « Précédent »

Dans un premier temps, nous allons nous contenter de deux simples liens pour passer d’une page à l’autre. Mais d’abord, réfléchissons.

Une page, ce sont 10 éléments d’affichés. La page suivante, ce sont les 10 éléments suivants, la page précédente, ce sont les 10 éléments précédents. Il nous faut donc adapter notre requête afin de pouvoir dire, d’une certaine manière, que nous n’avons pas besoin des éléments des pages précédentes.

Ce moyen est proposé par un autre mot-clé SQL : OFFSET. Celui-ci s’utilise très simplement de la manière OFFSET :debut. Comme vous pouvez l’avoir déjà compris, ce mot-clé permet de déterminer à partir de quel enregistrement l’on aimerait récupérer les données. D’une certaine manière, c’est donc aussi le nombre d’enregistrements que l’on souhaite "éviter" ou "ignorer". Je n’ai donc pas choisi les marqueurs sans y réfléchir !

Maintenant, le souci est de calculer cette valeur de début. Regardons de plus près quels sont les enregistrements que nous souhaitons récupérer pour chaque page. Afin de nous y retrouver, nous allons simplement les numéroter, mais pour des questions pratiques, je vais commencer à 0 comme pour les tableaux en PHP. Ce choix se justifie quand nous apprenons que pour SQL, le premier enregistrement est l’enregistrement 0 — comme dans beaucoup de langages de programmation.

Sur la première page, nous récupérons donc les enregistrements 0, 1, 2, 3, …, 9 (vous pouvez compter, ça fait bien 10 ).
Sur la seconde page, nous allons récupérer les enregistrements 10, 11, 12, …, 19.
Sur la troisième : 20, 21, … 29.
Vous voyez où je veux en venir ?

En fait, sur chaque page, on commence avec l’enregistrement dont la dizaine correspond au numéro de page moins un. Dit autrement, le numéro du premier enregistrement à récupérer pour une page correspond à l’expression ci-dessous :

$debut = (numeroDePage - 1) * 10$

Il y a juste encore le 10 qui peut gêner, bien qu’on puisse rapidement se rendre compte que ce 10 correspond au nombre d’éléments par page. Ce serait donc intéressant de le remplacer par $limite$.

$debut = (numeroDePage - 1) * limite$

Nous pouvons désormais adapter la requête pour y ajouter ce que nous avons vu plus haut, du fait que nous savons maintenant comment calculer ce dont elle a besoin pour être utilisable comme on le souhaite.

<?php
// Partie "Requête"
/* On calcule le numéro du premier élément à récupérer */
$debut = ($page - 1) * $limite;
/* La requête contient désormais l'indication de l'élément de départ,
 * avec le nouveau marqueur … */
$query = 'SELECT * FROM `my_table` LIMIT :limite OFFSET :debut';
$query = $cnx->prepare($query);
$query->bindValue('limite', $limite, PDO::PARAM_INT);
/* … auquel il faut aussi lier la valeur, comme pour la limite */
$query->bindValue('debut', $debut, PDO::PARAM_INT);
/* Le reste se passe comme auparavant */
$query->execute();

Nous sommes allés un peu trop vite, il nous manque quelque chose, là !
Maintenant que nous avons de quoi calculer le numéro du premier enregistrement pour la page, il nous faut encore savoir sur quelle page on se trouve. Pour ce faire, nous allons rester simple : nous allons passer le numéro de la page souhaitée dans l’URL en utilisant un paramètre de chaîne de requête page. Voilà, nous allons enfin pouvoir passer aux liens vers les pages précédentes et suivantes, en ajoutant simplement la partie "Liens". 

<?php
$cnx = new PDO('mysql:host=localhost;dbname=my_database;charset=utf8', 'root', '');

$page = $_GET['page'];
$limite = 10;

// Partie "Requête"
/* On calcule donc le numéro du premier enregistrement */
$debut = ($page - 1) * $limite;
/* On ajoute le marqueur pour spécifier le premier enregistrement */
$query = 'SELECT * FROM `my_table` LIMIT :limite OFFSET :debut';
$query = $cnx->prepare($query);
$query->bindValue('limite', $limite, PDO::PARAM_INT);
/* On lie aussi la valeur */
$query->bindValue('debut', $debut, PDO::PARAM_INT);
$query->execute();

// Partie "Boucle"
while ($element = $query->fetch()) {
    // C'est là qu'on affiche les données  :)
}

// Partie "Liens"
/* Notez que les liens ainsi mis vont bien faire rester sur le même script en passant
 * le numéro de page en paramètre */
?>
<a href="?page=<?php echo $page - 1; ?>">Page précédente</a>
—
<a href="?page=<?php echo $page + 1; ?>">Page suivante</a>

Il reste un point à traiter avant que ce script ne soit utilisable.
Si l’on arrive dessus sans spécifier de numéro de page, on aura droit à un message comme quoi l’index page n’existe pas. Même si nous savons que l’on doit arriver sur la première page, notre script ne le sait pas ! 

Il ne manque pas grand-chose pour régler cela, il suffit de modifier un petit peu la récupération du numéro de page comme suit :

<?php
$page = (!empty($_GET['page']) ? $_GET['page'] : 1);

Voilà, nous avons maintenant quelque chose de fonctionnel, qu’on va pouvoir continuer d’améliorer.

Maintenant que nous savons comment afficher quelques éléments à la fois, nous allons voir comment faire en sorte d’avoir la possibilité de savoir combien de pages il y a, et permettre de passer à d’autres pages que celles qui suivent ou précèdent immédiatement.

Comme auparavant, reprenons une base de départ.

<?php
$cnx = new PDO('mysql:host=localhost;dbname=my_database;charset=utf8', 'root', '');

$page = (!empty($_GET['page']) ? $_GET['page'] : 1);
$limite = 10;

// Partie "Requête"
$debut = ($page - 1) * $limite;
$query = 'SELECT * FROM `my_table` LIMIT :limite OFFSET :debut';
$query = $cnx->prepare($query);
$query->bindValue('debut', $debut, PDO::PARAM_INT);
$query->bindValue('limite', $limite, PDO::PARAM_INT);
$query->execute();

// Partie "Boucle"
while ($element = $query->fetch()) {
    // C'est là qu'on affiche les données  :)
}

// Partie "Liens"
?><a href="?page=<?php echo $page - 1; ?>">Page précédente</a>
—
<a href="?page=<?php echo $page + 1; ?>">Page suivante</a>

Comme toujours, un peu de réflexion s’impose avant de foncer tête baissée dans le code. Nous savons que nous aurons 10 éléments par page, mais nous ne savons pas combien de fois cela va faire. Il nous faut donc un moyen de récupérer ce nombre, qui correspondra au nombre de pages total.

Un peu de réflexion (ou de recherche ) nous permet de trouver que le nombre de pages total se calcule de la sorte :

$nombreDePages = \left \lceil{\dfrac{nombredElements_{total}}{limite}}\right \rceil$

La notation $\lceil{quelqueChose}\rceil$ se lit comme la fonction plafond de $quelqueChose$, et consiste à prendre l’entier supérieur ou égal à l’argument.
Du coup, pour une explication en français : le nombre de pages total est égal à la fonction plafond appliquée sur le résultat de la division du nombre total d’éléments à afficher par le nombre d’éléments par page.
Prenez le temps de relire si vous ne comprenez pas.

Parmi tout ce qu’il y a dans cette expression, il nous manque maintenant $nombredElements_{total}$.

Récupérer le nombre d’éléments total

Nous pourrions commencer par nous dire qu’il nous faut récupérer tous les résultats, puis les compter. Seulement, nous avons déjà vu que récupérer tous les résultats n’est pas une bonne solution quand on n’en affiche que quelques-uns : nous nous retrouvons dans la première situation de la première partie. Il nous faut donc éviter cela.

Pour compter un nombre d’enregistrements, il existe la fonction SQL count(). Elle s’utilise sur une colonne, et compte le nombre de valeurs non-nulles qui s’y trouvent. Seulement, si vous arrivez à la combiner avec la version courante de notre requête, vous ne récupérerez plus jamais qu’un seul enregistrement.

Mais c’est cependant la solution standard que d’effectuer deux requêtes : une qui compte, et une autre qui ira récupérer les éléments voulus.

Pour l’instant, notre unique requête se présente ainsi :

SELECT * FROM `my_table` LIMIT :limite OFFSET :debut

Avec celle-ci, nous récupérons toutes les colonnes des enregistrements. Pour compter, il ne nous faudra que le nombre de résultats, ce qui peut donc se faire tout simplement ainsi :

SELECT count(id) FROM `my_table`

Au final, notre partie "Requête" va se présenter ainsi :

<?php
// Partie "Requête"
/* On commence par récupérer le nombre d'éléments total. Comme c'est une requête,
 * il ne faut pas oublier qu'on ne récupère pas directement le nombre.
 * Ici, comme la requête ne contient aucune donnée client pour fonctionner,
 * on peut l'exécuter ainsi directement */
$resultFoundRows = $cnx->query('SELECT count(id) FROM `my_table`');
/* On doit extraire le nombre du jeu de résultat */
$nombredElementsTotal = $resultFoundRows->fetchColumn();

$debut = ($page - 1) * $limite;
/* Cette requête ne change pas */
$query = 'SELECT * FROM `my_table` LIMIT :limite OFFSET :debut';
$query = $cnx->prepare($query);
$query->bindValue('debut', $debut, PDO::PARAM_INT);
$query->bindValue('limite', $limite, PDO::PARAM_INT);
$query->execute();

Il ne nous reste maintenant plus qu’à afficher les liens vers les autres pages selon leur numéro.

Liste des pages

Nous avons vu une expression mathématique un peu plus haut. Dans un premier temps, nous allons l’adapter en PHP. Vous l’avez peut-être remarqué, les noms de fonctions en PHP sont en anglais. On parle de fonction plafond, est-ce que “ceiling” serait le nom de la fonction ? Presque, c’est la version abrégée ! La fonction PHP qui permet de récupérer l’entier supérieur ou égal le plus proche est la fonction ceil(). Du coup, nous aurons notre calcul qui se présentera ainsi :

<?php
$nombreDePages = ceil($nombredElementsTotal / $limite);

Et maintenant que nous avons toutes les données, il nous faut les afficher. Rien de plus simple qu’une boucle pour ce faire.

<?php
// Partie "Liens"
/* On calcule le nombre de pages */
$nombreDePages = ceil($nombredElementsTotal / $limite);

/* Si on est sur la première page, on n'a pas besoin d'afficher de lien
 * vers la précédente. On va donc ne l'afficher que si on est sur une autre
 * page que la première */
if ($page > 1):
    ?><a href="?page=<?php echo $page - 1; ?>">Page précédente</a> — <?php
endif;

/* On va effectuer une boucle autant de fois que l'on a de pages */
for ($i = 1; $i <= $nombreDePages; $i++):
    ?><a href="?page=<?php echo $i; ?>"><?php echo $i; ?></a> <?php
endfor;

/* Avec le nombre total de pages, on peut aussi masquer le lien
 * vers la page suivante quand on est sur la dernière */
if ($page < $nombreDePages):
    ?>— <a href="?page=<?php echo $page + 1; ?>">Page suivante</a><?php
endif;
?>

Nous avons désormais notre pagination définitive : nous pouvons accéder à toutes les pages en un clic, nous pouvons passer d’une page à l’autre dans les deux "directions". Au niveau de l’expérience utilisateur nous avons :

• organisé l’information en petites parties qu’il est plus simple pour l’utilisateur de parcourir ;
• diminué le temps de chargement des pages ;
• réparti un tant soit peu la charge en demandant le plus d’informations possible à MySQL plutôt que de tout faire en PHP ;
• minimisé l’empreinte mémoire nécessaire

Le jeu en valait donc la chandelle, non ?