Technique : afficher le résultat d'une requête par tranches successives avec PHP


Quelques rares SGDBR (comme MySQL et Postgres) proposent le mot clef LIMIT dans la syntaxe du SELECT, ce qui permet d'afficher très simplement un gros volume de résultats d'une recherche sur plusieurs écrans avec une navigation entre les "tranches" de résultats.

Néanmoins, tous les SGBDR ne connaissent pas cette syntaxe : Oracle ou Sybase, par exemple, ne permettent pas de limiter directement les résultats. Rappelons à ce propos que Oracle tout comme Sybase sont disponibles gratuitement pour les développeurs, sur plateforme Linux.

Voici donc une technique totalement compatible et portable quel que soit le SGBDR utilisé (y compris MySQL et PostGres donc). Nous verrons aussi un exemple de construction dynamique de requête SQL.

Cette méthode, très simple, utilise une table de stockage temporaire et un identifiant requête, et sera illustrée ici sur un moteur de recherche de fichiers MP3 avec une URL directe vers le fichier servant de clef primaire (choix discutable, mais c'est seulement un exemple)


Voici la définition de la table principale, en SQL standard, à adapter selon votre SGBDR (avec ou sans ; en fin d'instruction, etc...)

CREATE TABLE mes_mp3 (
titre VARCHAR(30),
groupe VARCHAR(30),
genre VARCHAR(10),
url VARCHAR(255) NOT NULL,
uploaded_by_IP VARCHAR(30)
)

ALTER TABLE mes_mp3 ADD CONSTRAINT pk_mes_mp3 PRIMARY KEY (url)

Remarque : sous Oracle, préférer du VARCHAR2 à du VARCHAR.


On va ensuite créer une table contenant, pour chaque requête client, le résultat de la recherche à afficher par la suite. Cette technique va donc prendre une "photo" (snapshot) de la table mes_mp3 au moment de la requête, et ne la consultera plus par la suite. Attention donc en applicant cette méthode si des données volatiles (cours de bourse par exemple) font partie des critères de recherche, gérer alors la colonne "time out" avec une valeur faible.


Cette table contenant des données temporaires (mais elle même table persistante, il ne s'agit pas d'une table temporaire #nom_de_table au sens Sybase, qui n'a d'existence que le temps de la transaction en cours) va avoir deux utilités. Tout d'abord, l'équivalent d'une vue, afin de ne considérer que les colonnes qui nous intéressent, et non des renseignement "internes" (comme, par exemple ici, l'adresse IP de provenance uploaded_by_IP). Ensuite, cette table va être remplie avec trois colonnes supplémentaires : un time_out, un identifiant de requête et un ordre d'affichage. Cette table permettra aussi de donner un nombre maximum de réponses à chaque recherche.


CREATE TABLE tempo_recherche 
(
titre VARCHAR(30),
groupe VARCHAR(30),
genre VARCHAR(10),
url VARCHAR(255),
time_out DATETIME, /* ou TIMESTAMP, peu importe */
id_requete VARCHAR(20) NOT NULL,
ordre_affichage INT NOT NULL
)
ALTER TABLE tempo_recherche ADD CONSTRAINT pk_tempo PRIMARY KEY (id_requete, ordre_affichage)

On remarquera concernant la clef que la colonne ordre_affichage en fait partie. En effet, il va falloir créer "à la main" l'équivalent de LIMIT, et pour chaque identifiant requête, disposer d'un compteur permettant un ORDER BY et des comparaisons simples.


Remarque : on pourrait aussi inclure dans la clef primaire pk_tempo la clef de la table mes_mp3 mais elle sera implicite par remplissage de tempo_recherche.


On dispose à ce stade des structures de données nécessaires.


Voyons maintenant la requête initiale, qui va prendre un "snapshot" selon les critères de recherche. Le but est de transférer les données sélectionnées et de remplir les colonnes supplémentaires. Il serait possible de faire ceci à l'aide d'une procédure stockée Transac-SQL ou PL/SQL, mais voyons comment le faire en PHP, sur un exemple avec des fonctions MySQL (adapter pour les autres SGBDR). On a reçu par un formulaire les trois variables $in_titre, $in_groupe, $in_genre, les deux premièrs sous forme de saisie de texte libre, et le genre par un menu drop-down (Techno, Classique, Années 80, etc...).


On dispose aussi d'un identifiant requête. La fonction fx_filtrer($in_texte) teste la validité des caractères de la chaîne en entrée, en les comparant un à un à une liste exhaustive de caractères explicitement autorisés et peut mettre une limite à la chaine de caractères retournée (précaution supplémentaire pour gêner les amateurs de buffer overflows). Le résultat est "trimmé" (on enlève les espaces en début et fin de chaîne), et renvoyé par return( ).


<?
$loc_titre=fx_filtrer($in_titre);
$loc_groupe=fx_filtrer($in_groupe);
$loc_genre=fx_filtrer($in_genre);

/* La fonction fx_get_identifiant( ) retourne un identifiant unique, selon le même principe qu'un identifiant de session. Par exemple, on pourrait très simplement prendre le PID du process courant concaténé à l'heure de la requête en nombre de secondes depuis le 1er janvier 1970. */

$out_id_requete=fx_get_identifiant( );

/* La fonction fx_connect( ) appelle mysql_connect( ) et mysql_db_select( ), et renvoie un descripteur d'accès à la base de données, database access descriptor */

$dad_select=fx_connect( );
$requete=" SELECT titre, groupe, genre, url FROM mes_mp3 WHERE TRUE ";
if($loc_titre!="")
{
$requete.=" AND titre like '%$loc_titre%' ";
}
if($loc_groupe!="")
{
$requete.=" AND groupe like '%$loc_groupe%' ";
}
if($loc_genre!="tous" && $loc_genre!="" )
{
$requete.=" AND genre='$loc_genre' ";
}

A ce stade, la requête du moteur a été dynamiquement créée. Notons une petite astuce de syntaxe introduite par le TRUE dans la clause WHERE du select afin de ne pas avoir à gérer de tests sur l'ajout ou non du mot clef AND à chaque condition supplémentaire.

On peut employer une construction identique en mettant WHERE FALSE pour des successions de conditions OR. Les conditions de recherche, avec des valeurs encadrées par %, seront très gourmandes et peu performantes. On devrait probablement créer un index sur les deux colonnes "titre" et "groupe", car les ajouts dans la table seront peu nombreux en comparaison au nombre de SELECTs qui seront faits.


$result_buffer=mysql_query($requete, $dad_select);

/* étant donné la simplicité de la requête, on passe par des indices, et MAX_RANGS a été défini par define(MAX_RANGS, 500); par exemple. */

$dad_ins=fx_connect( );
$out_nbre_rangs=0;
while ( $rang=mysql_fetch_row( $result_buffer) && $nbre_rangs<MAX_RANGS)
{
$out_nbre_rang++;
$requete=" INSERT INTO tempo_recherche (titre, groupe, genre,url, time_out, id_requete, ordre_affichage ) ";
$requete.=" VALUES ( '$row[0]', '$row[1]', '$row[2]', '$row[3]', ";
$requete.=" getdate( ),'$out_id_requete', $out_nbre_rang ";
$exec=mysql_query($requete, $dad_ins);
if($exec!=TRUE) { print("Erreur Syst&egrave;me ! <BR>\n"); exit();}

/* on ne teste pas ici l'exécution réelle en regardant la valeur du retour de mysql_affected_rows, mais ce serait mieux de le faire pour gérer proprement les erreurs */

}

/* A ce stade du code, la table tampon contenant les résultats de la recherche à afficher par tranche est remplie. Ici, on va encore devoir afficher les résultats de la première tranche, donc on libère des ressources :*/

mysql_free_result($exec);
mysql_free_result($rang);
mysql_close($dad_select);
mysql_close($dad_ins);

fx_afficher_tranche(1, 10, $out_id_requete, $out_nbre_rang);
exit( );
?>

La requête initiale de recherche a fait tout le travail. Maintenant, il suffit de passer à une fonction générique les arguments suivants :

- borne inférieure de la tranche : ici, 1 car c'est la première tranche
- nombre de rangs à afficher par tranche, mettons 10 (encore un bon candidat pour un define( ) d'ailleurs, ou alors saisi par l'utilisateur et transitant dans l'URL avec les critères de sélection utilisateur).
- l'identifiant requête : $out_id_requete
- petit plus pour faire "joli" : le nombre de rangs réellement à afficher au total ($nbre_rang ). Ceci permettra de ne pas mettre un lien inutile vers "suivant" sur la dernière page, ou si le nombre de résultats est un multiple de 10, un lien vers une page vide.


Il ne restera plus qu'à gérer l'engorgement de cette table tempo_recherche. Si on a accès à une cron table (sous unix-like), il est simple de lancer régulièrement une purge selon la valeur de la colonne time_out. Sinon, il est possible de lancer une purge systématique à chaque requête de recherche, par exemple.


Voyons maintenant très rapidement le code de la fonction générique affichant les résultats, fx_afficher_tranche( ). C'est elle qui se chargera également de la création des liens "Précédent" et "Suivant" quand nécessaire, en se préparant à elle même les paramètres qui lui seront passés lors du prochain appel, quand l'utilisateur cliquera sur le lien. Cet appel aura lieu au travers d'un script, afficher.php3, qui récupèrera les variables, les filtrera avec la même fonction fx_filter( ) et appellera fx_afficher_tranche().

Notons que l'habillage de la page est absent du code ci-dessous, seules les parties "intéressantes" pour le sujet ont été indiquées. De plus, afin de ne pas passer des URLs trop longues, on pourrait donner des noms plus courts aux variables, même s'ils seraient moins explicites.


function fx_afficher_tranche($io_borneinf, $io_nbre_rangs_tranche, $io_id_requete, $io_nbre_rang_total)
{
/* vérifications de cohérence */
if($io_borneinf<1 || $io_nbre_rangs_tranche <1 || $io_id_requete=="" || $io_nbre_rang_total <1)
{
print("Erreur interne<br>\n");
return(FALSE); /* ou exit() si vous ne souhaitez pas traiter le retour dans le script appelant */
}

/* Lien vers précédent */
if($io_borneinf>1)
{
$out_borne_inf_precedent=$io_borneinf-$io_nbre_rangs_tranche;
/* attention, les navigateurs n'aiment pas les retours à la ligne dans les URLs */
print("<BR><A HREF=\"./afficher.php3?io_borneinf=$out_borne_inf_precedent&io_nbre_rangs_tranche=$io_nbre_rangs_tranche&io_nbre_rang_total=$io_nbre_rang_total&io_id_requete=$io_id_requete\">");
print("$io_nbre_rangs_tranche r&eacute;sultats pr&eacute;c&eacute;dents</A> <BR><BR><HR>\n");
}

/* on pourrait aussi ouvrir cette connexion dans le script appelant et la passer en paramètre, par exemple pour éviter d'avoir au total à ouvrir 3 fois une connexion lors de la première requête. */

$dad_select=fx_connect( );

/* On va maintenant donner la requête à lancer sur la table tampon. */
$out_borne_inf_suivant=$io_borneinf+$io_nbre_rangs_tranche;

$requete="SELECT titre, groupe, url FROM tempo_recherche ";
$requete.=" WHERE id_requete ='$io_id_requete' ";
$requete.=" AND ordre_affichage >=$io_borneinf "; 
$requete.=" AND ordre_affichage < $out_borne_inf_suivant ";

$result_buffer=mysql_query($requete, $dad_select);
/* Très originalement, présentons les résultats dans un tableau */
print("<TABLE WIDTH=\"95%\">\n");
print("<TR><TD WIDTH=\"30%\"> Titre </TD><TD WIDTH=\"20%\"> Groupe </TD><TD WIDTH=\"50%\">URL</TD></TR>");
while ( $rang=mysql_fetch_row( $result_buffer) )
{
print("<TR>\n");
print("<TD>$row[0]</TD>\n<TD>$row[1]</TD>\n");
print("<TD><A HREF=\"$row[2]\">$row[2]</A></TD>\n");
print("</TR>\n");
}
print("<TABLE>\n<BR><BR><HR>\n");
/* Rappel : $out_borne_inf_suivant contient la valeur de la borne inférieure de la tranche suivante */
if($out_borne_inf_suivant<=$io_nbre_rang_total)
{
print("<BR><A HREF= \"./afficher.php3?io_borneinf=$out_borne_inf_suivant&io_nbre_rangs_tranche=$io_nbre_rangs_tranche&io_nbre_rang_total=$io_nbre_rang_total&io_id_requete=$io_id_requete\">");
print("$io_nbre_rangs_tranche r&eacute;sultats suivants</A> <BR><BR><HR>\n");
}

/* fin de la fonction, on libère les ressources, qui sinon ne le seront qu'après fin du script */

mysql_free_result($result_buffer);
mysql_close($dad_select);
return(TRUE);
}

Récapitulatif :


Cet exemple a permis de revoir la notion de modularité et donc de réutilisation de code (la même fonction fx_afficher_tranche( ) sera appelée autant de fois que nécessaire, seule la valeur de la borne inférieure est changée au cours des appels), de construction dynamique de requête SQL, de persistance de la requête côté serveur. Notons à ce propos que l'identifiant requête ne doit pas être confondu avec un identifiant de session, et devra être généré à chaque nouvelle requête, sans quoi un même utilisateur qui ouvrirait deux fenêtres dans son navigateur après avoir "acquis" un identifiant de session verrait les résultats de ses deux recherches totalement mélangées. On peut aussi remarquer que l'intégralité des traitements a eut lieu sur le serveur, et reste donc totalement indépendante du navigateur client et des préférences de l'internaute (cookies, JS...)