Un tableau croisé [quasi] dynamique en PHP/MySQL

Maljuna Kris

Bien souvent le simple langage SQL ne suffit pas à obtenir ce que nous cherchons à extraire de notre base de données. Il est alors indispensable de générer une requête SQL dynamiquement grâce à un langage applicatif. Ici nous verrons un cas où cette pratique s'impose avec PHP et MySQL.

- Titre : Un tableau croisé [quasi] dynamique en PHP/MySQL

- Auteur : Maljuna Kris

- Parution : 4 mars 2009

- Mise à jour : 15 mars 2009

- Licence : Copyright ® 2009 Maljuna Kris. Aucune reproduction, même partielle, ne peut être faite de ce site et de l'ensemble de son contenu : textes, documents, images, etc. sans l'autorisation expresse de l'auteur. Sinon vous encourez selon la loi jusqu'à trois ans de prison et jusqu'à 300 000 € de dommages et intérêts.

Sommaire

- I. La problématique

- II. Première ébauche

- III. Ça se complique

- IV. Peut-on faire mieux ?

- V. Toujours plus

I - La problématique

Soit une base MySQL comprenant 4 tables aux structures suivantes :

Table etudiants

CREATE TABLE `etudiants` (
`INE` VARCHAR(11) NOT NULL DEFAULT '',
`CIVILITE` ENUM('M.','Mme','Mlle'),
`DATENAISSANCE` DATE NOT NULL
DEFAULT '0000-00-00 00:00:00'
`UAIRNE` VARCHAR(8) NOT NULL DEFAULT '',
`FORMATION` CHAR(3) NOT NULL DEFAULT '',
`NIVEAU` CHAR(1) NOT NULL DEFAULT '',
`IDAIDE` CHAR(2) NOT NULL DEFAULT '',
`DECISION` CHAR(2) NOT NULL DEFAULT '',
`CPETUDIANT` CHAR(5) DEFAULT NULL,
`CPPARENTS` CHAR(5) DEFAULT NULL,
`CSP_PERE`CHAR(3) NOT NULL,
`CSP_MERE`CHAR(3) NOT NULL,
PRIMARY KEY  (`INE`)
) ENGINE=MyISAM

Table lignescreance

CREATE TABLE `lignescreance` (
`INE` VARCHAR(11) NOT NULL,
`CATAIDE` VARCHAR(1) NOT NULL,
`ECHELON` TINYINT(1) NOT NULL,
`MENSUALITE` TINYINT(1) NOT NULL,
`DATEPAIEMENT` DATETIME NULL,
`MONTANT` FLOAT,
`STATUTLCR` VARCHAR(1) NOT NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

Table etablissements

CREATE TABLE `etablissements` (
`UAIRNE` VARCHAR(8) NOT NULL DEFAULT '',
`SECTEUR` CHAR(1) NOT NULL DEFAULT '',
`NOMUSUEL` VARCHAR(35) NOT NULL DEFAULT '',
`LIBELLECOURT` VARCHAR(15) NOT NULL DEFAULT '',
`ADR1` VARCHAR(32) NOT NULL,
`ADR2` VARCHAR(32) NOT NULL,
`CODEPOSTAL` VARCHAR(5) NOT NULL,
`LOCALITE` VARCHAR(27) NOT NULL,
PRIMARY KEY  (`UAIRNE`),
INDEX `SECTEUR` (`SECTEUR`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

Table villes

CREATE TABLE `villes` (
`CODEVILLE` CHAR(1) NOT NULL DEFAULT '',
`LIBVILLE` VARCHAR(15) NOT NULL DEFAULT '',
`SECTEUR` CHAR(1) NOT NULL DEFAULT '',
`DEPARTEMENT` ENUM('022','029','035','056')
NOT NULL DEFAULT '035',
PRIMARY KEY  (`codeville`),
INDEX `SECTEUR` (`SECTEUR`),
INDEX `DEPARTEMENT` (`DEPARTEMENT`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

II - Première ébauche

On demande de réaliser un tableau qui affiche, pour chaque date de paiement, le montant dépensé, l'effectif des étudiants aidés et une dernière ligne affichant les totaux généraux.

La solution apparaît simplissime : une requête qui compte les identifiants d'étudiants [INE] distincts et additionne les [MONTANT]s en regroupant par [DATEPAIEMENT] les créances réglées, c'est-à-dire dont le [STATUTLCR] contient 'R'.

SELECT DATEPAIEMENT,
COUNT(DISTINCT INE) AS Effectif,
SUM(MONTANT) AS Depense
FROM lignescreance
WHERE STATUTLCR = 'R'
GROUP BY DATEPAIEMENT WITH ROLLUP
ORDER BY DATEPAIEMENT;

III - Ça se complique

On veut cette fois-ci un tableau qui fasse figurer en lignes les codes d'aide [etudiants.IDAIDE] et en colonnes les [DATEPAIEMENT], le tout en balance carrée, c'est-à-dire avec des colonnes TOTAUX et une ligne TOTAUX (dépense et effectif).

Le premier réflexe est de modifier la première requête comme suit :

SELECT IDAIDE, DATEPAIEMENT,
COUNT(DISTINCT INE) AS Effectif,
SUM(MONTANT) AS Depense
FROM lignescreance
INNER JOIN etudiants ON etudiants.INE = lignescreance.INE
WHERE STATUTLCR = 'R'
GROUP BY IDAIDE,DATEPAIEMENT WITH ROLLUP
ORDER BY IDAIDE,DATEPAIEMENT;

et le tour semble joué. On déporte sur un processus de langage procédural la mise en colonnes des données fournies en lignes, à savoir les [DATEPAIEMENT], ainsi que leur totalisation en fin de ligne.

IV - Peut-on faire mieux ?

On se heurte à un problème : comment construire la requête puisqu'on ne connaît pas, a priori, le nombre de colonnes [DATEPAIEMENT] ?
MS Access propose nativement dans son langage SQL une fonctionnalité non-standard qui permet de réaliser ce genre d'opération.
Non-standard, c'est bien notre problème.

SELECT DISTINCT DATEPAIEMENT
FROM lignescreance
ORDER BY DATEPAIEMENT;

En parcourant le résultat de cette requête en PHP, on doit pouvoir construire dynamiquement la requête avant de la soumettre à MySQL.

On peut connaître les différentes [DATEPAIEMENT] :

<?php
include 'connexion.inc.php';
$result=mysql_query("SELECT DATE_FORMAT(DATEPAIEMENT,'%d/%m/%Y') AS DP
FROM lignescreance
ORDER BY DATEPAIEMENT") or die (mysql_error());
$datesp=array();
while($ligne=mysql_fetch_assoc($result)){$datesp[]=$ligne['DP'];}
mysql_free_result($result);
$groupes=NULL;
foreach($datesp as $item){
$groupes.=" ,SUM(IF(DATE_FORMAT(DATEPAIEMENT,'%d/%m/%Y')='$item',
COUNT(DISTINCT etudiants.INE),0)) AS P_".$item;
$groupes.=" ,SUM(IF(DATE_FORMAT(DATEPAIEMENT,'%d/%m/%Y')='$item',MONTANT,0))
AS D_".$item;
}
$sql="SELECT IDAIDE, $groupe,
COUNT(DISTINCT etudiants.INE) as P_TOT,
SUM(MONTANT) AS D_TOT
FROM lignescreance
INNER JOIN etudiants ON etudiants.INE = lignescreance.INE
WHERE STATUTLCR = 'R'
GROUP BY IDAIDE WITH ROLLUP
ORDER BY IDAIDE";
echo "\n"; /* pour voir le source de la requête soumise à MySQL
en commentaire dans le source HTML de la page */
$result=mysql_query($sql);
/* ici on parcourt le résultat pour affichage, édition
ou génération d'un document pdf (avec FPDF par exemple) */
mysql_close();
?>

Les colonnes, c'est-à-dire les [DATEPAIEMENT] non concernées par certaines aides, n'afficheront même pas d'affreux NULL, en montants et effectifs, mais bien 0.

V - Toujours plus

Si l'on regarde le contenu des tables étudiants, villes et établissements on peut envisager toutes sortes de demandes à finalité statistiques, par sexe, âge, formation, niveau, secteur géographique, catégorie socio-professionelle des parents ...

Je souhaite qu'elle puisse se voir transposée par quiconque voudrait l'adapter à son domaine.
Il va de soi que l'emploi des fonctionnalités équivalentes existant dans les tableurs doit être privilégié chaque fois que le volume des données le permet.