I. La problématique▲
Soit une base MySQL comprenant 4 tables aux structures suivantes :
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=MyISAMCREATE 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;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;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.