[MYSQL][PHP] Requetes imbriquees - votre avis pour passer le code en MySQL < 4.1
Bonjour à tous,
Voila j'ai developpé un code php/mysql pour générer des stats au format csv.
Les tests sont passés sauf que finalement le serveur MySQL ou sera installé ce code est de version < 4.1.
J'utilise les requetes imbriquées pour rendre le code plus simple et celles ci ne sont pas compatible mysql <4.1.
Voila, je diffuse la partie du code contenant les requetes imbriquées et j'aimerai connaitre vos avis sur la facon la plus simple de reconvertir mes requetes en mysql < 4.1
Merci par avance
Code:
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
|
// On recupère toutes les années présentes dans la base
$query_years = "SELECT DISTINCT YEAR(DATE_ENR) AS year FROM ".$pref_table."commandes";
$years = mysql_query($query_years, $mysql);
// Debut requete
$query_fournisseurs = "SELECT f.CODEPDV AS CODEPDV, f.LIBFOURNISSEUR AS LIBFOURNISSEUR";
// Pour chaque année
while( $year = mysql_fetch_array($years) )
{
$year = $year['year'];
// Définit les titres des colonnes au format mm-yyyy
$str_titres_colonnes .= "01-".$year.";02-".$year.";03-".$year.";04-".$year.";05-".$year.";06-".$year.";07-".$year.";08-".$year.";09-".$year.";10-".$year.";11-".$year.";12-".$year.";";
// Champs à récupérer sous forme de requetes imbriquées
// Volume par mois et par année
$query_fournisseurs .= ",(SELECT SUM(c1".$year.".volume) FROM ".$pref_table."commandes c1".$year." WHERE MONTH(c1".$year.".DATE_ENR)=1 AND f.IDFOURNISSEUR = c1".$year.".IDFOURNISSEUR AND YEAR(c1".$year.".DATE_ENR)=".$year." GROUP BY MONTH(c1".$year.".DATE_ENR) ) AS VOL1".$year.",
(SELECT SUM(c2".$year.".volume) FROM ".$pref_table."commandes c2".$year." WHERE MONTH(c2".$year.".DATE_ENR)=2 AND f.IDFOURNISSEUR = c2".$year.".IDFOURNISSEUR AND YEAR(c2".$year.".DATE_ENR)=".$year." GROUP BY MONTH(c2".$year.".DATE_ENR) ) AS VOL2".$year.",
(SELECT SUM(c3".$year.".volume) FROM ".$pref_table."commandes c3".$year." WHERE MONTH(c3".$year.".DATE_ENR)=3 AND f.IDFOURNISSEUR = c3".$year.".IDFOURNISSEUR AND YEAR(c3".$year.".DATE_ENR)=".$year." GROUP BY MONTH(c3".$year.".DATE_ENR) ) AS VOL3".$year.",
(SELECT SUM(c4".$year.".volume) FROM ".$pref_table."commandes c4".$year." WHERE MONTH(c4".$year.".DATE_ENR)=4 AND f.IDFOURNISSEUR = c4".$year.".IDFOURNISSEUR AND YEAR(c4".$year.".DATE_ENR)=".$year." GROUP BY MONTH(c4".$year.".DATE_ENR) ) AS VOL4".$year.",
(SELECT SUM(c5".$year.".volume) FROM ".$pref_table."commandes c5".$year." WHERE MONTH(c5".$year.".DATE_ENR)=5 AND f.IDFOURNISSEUR = c5".$year.".IDFOURNISSEUR AND YEAR(c5".$year.".DATE_ENR)=".$year." GROUP BY MONTH(c5".$year.".DATE_ENR) ) AS VOL5".$year.",
(SELECT SUM(c6".$year.".volume) FROM ".$pref_table."commandes c6".$year." WHERE MONTH(c6".$year.".DATE_ENR)=6 AND f.IDFOURNISSEUR = c6".$year.".IDFOURNISSEUR AND YEAR(c6".$year.".DATE_ENR)=".$year." GROUP BY MONTH(c6".$year.".DATE_ENR) ) AS VOL6".$year.",
(SELECT SUM(c7".$year.".volume) FROM ".$pref_table."commandes c7".$year." WHERE MONTH(c7".$year.".DATE_ENR)=7 AND f.IDFOURNISSEUR = c7".$year.".IDFOURNISSEUR AND YEAR(c7".$year.".DATE_ENR)=".$year." GROUP BY MONTH(c7".$year.".DATE_ENR) ) AS VOL7".$year.",
(SELECT SUM(c8".$year.".volume) FROM ".$pref_table."commandes c8".$year." WHERE MONTH(c8".$year.".DATE_ENR)=8 AND f.IDFOURNISSEUR = c8".$year.".IDFOURNISSEUR AND YEAR(c8".$year.".DATE_ENR)=".$year." GROUP BY MONTH(c8".$year.".DATE_ENR) ) AS VOL8".$year.",
(SELECT SUM(c9".$year.".volume) FROM ".$pref_table."commandes c9".$year." WHERE MONTH(c9".$year.".DATE_ENR)=9 AND f.IDFOURNISSEUR = c9".$year.".IDFOURNISSEUR AND YEAR(c9".$year.".DATE_ENR)=".$year." GROUP BY MONTH(c9".$year.".DATE_ENR) ) AS VOL9".$year.",
(SELECT SUM(c10".$year.".volume) FROM ".$pref_table."commandes c10".$year." WHERE MONTH(c10".$year.".DATE_ENR)=10 AND f.IDFOURNISSEUR = c10".$year.".IDFOURNISSEUR AND YEAR(c10".$year.".DATE_ENR)=".$year." GROUP BY MONTH(c10".$year.".DATE_ENR) ) AS VOL10".$year.",
(SELECT SUM(c11".$year.".volume) FROM ".$pref_table."commandes c11".$year." WHERE MONTH(c11".$year.".DATE_ENR)=11 AND f.IDFOURNISSEUR = c11".$year.".IDFOURNISSEUR AND YEAR(c11".$year.".DATE_ENR)=".$year." GROUP BY MONTH(c11".$year.".DATE_ENR) ) AS VOL11".$year.",
(SELECT SUM(c12".$year.".volume) FROM ".$pref_table."commandes c12".$year." WHERE MONTH(c12".$year.".DATE_ENR)=12 AND f.IDFOURNISSEUR = c12".$year.".IDFOURNISSEUR AND YEAR(c12".$year.".DATE_ENR)=".$year." GROUP BY MONTH(c12".$year.".DATE_ENR) ) AS VOL12".$year;
}
$str_csv .= $str_titres_colonnes."Total;\n";
// Fin requete
$query_fournisseurs .= " ,(SELECT SUM(total.volume) FROM ".$pref_table."commandes total WHERE f.IDFOURNISSEUR = total.IDFOURNISSEUR GROUP BY f.IDFOURNISSEUR ) AS VOL_TOTAL FROM ".$pref_table."fournisseurs f
ORDER BY f.LIBFOURNISSEUR,f.CODEPDV";
$errors='';
$fournisseurs = mysql_query($query_fournisseurs);
$errors[] = mysql_error();
/* print_r($errors);
echo $query_fournisseurs;*/
while( $fournisseur = mysql_fetch_row($fournisseurs) )
{
foreach($fournisseur as $value)
{
$str_csv .= $value.";";
}
$str_csv .= "\n";
} |
