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

Date de publication : 04 Mars 2009 , Date de mise à jour : 15 Mars 2009

Par 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.

Version PDF (Miroir) Version hors-ligne (Miroir)

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.

Version PDF (Miroir) Version hors-ligne (Miroir)

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. Cette page est déposée.

Responsable bénévole de la rubrique MySQL : Emmanuel Lecoester -

Developpez.com

Services

Partenaires