[MYSQL][PHP] Requetes imbriquees - votre avis pour passer le code en MySQL < 4.1

[hackrobat]

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";
  }

[Maximilian]

Salut,

Peux-tu nous donner un exemple de résultat d'une telle requête ?

[hackrobat]

désolé pour le retard

je me suis limité à 1 année(2005)

Code :

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
 
 
SELECT f.CODEPDV AS CODEPDV, 
f.LIBFOURNISSEUR AS LIBFOURNISSEUR,
(SELECT SUM(c12005.volume) FROM fioulcommande_commandes c12005 WHERE MONTH(c12005.DATE_ENR)=1 AND f.IDFOURNISSEUR = c12005.IDFOURNISSEUR AND YEAR(c12005.DATE_ENR)=2005 GROUP BY MONTH(c12005.DATE_ENR) ) AS VOL12005, 
(SELECT SUM(c22005.volume) FROM fioulcommande_commandes c22005 WHERE MONTH(c22005.DATE_ENR)=2 AND f.IDFOURNISSEUR = c22005.IDFOURNISSEUR AND YEAR(c22005.DATE_ENR)=2005 GROUP BY MONTH(c22005.DATE_ENR) ) AS VOL22005, 
(SELECT SUM(c32005.volume) FROM fioulcommande_commandes c32005 WHERE MONTH(c32005.DATE_ENR)=3 AND f.IDFOURNISSEUR = c32005.IDFOURNISSEUR AND YEAR(c32005.DATE_ENR)=2005 GROUP BY MONTH(c32005.DATE_ENR) ) AS VOL32005, 
(SELECT SUM(c42005.volume) FROM fioulcommande_commandes c42005 WHERE MONTH(c42005.DATE_ENR)=4 AND f.IDFOURNISSEUR = c42005.IDFOURNISSEUR AND YEAR(c42005.DATE_ENR)=2005 GROUP BY MONTH(c42005.DATE_ENR) ) AS VOL42005, 
(SELECT SUM(c52005.volume) FROM fioulcommande_commandes c52005 WHERE MONTH(c52005.DATE_ENR)=5 AND f.IDFOURNISSEUR = c52005.IDFOURNISSEUR AND YEAR(c52005.DATE_ENR)=2005 GROUP BY MONTH(c52005.DATE_ENR) ) AS VOL52005, 
(SELECT SUM(c62005.volume) FROM fioulcommande_commandes c62005 WHERE MONTH(c62005.DATE_ENR)=6 AND f.IDFOURNISSEUR = c62005.IDFOURNISSEUR AND YEAR(c62005.DATE_ENR)=2005 GROUP BY MONTH(c62005.DATE_ENR) ) AS VOL62005, 
(SELECT SUM(c72005.volume) FROM fioulcommande_commandes c72005 WHERE MONTH(c72005.DATE_ENR)=7 AND f.IDFOURNISSEUR = c72005.IDFOURNISSEUR AND YEAR(c72005.DATE_ENR)=2005 GROUP BY MONTH(c72005.DATE_ENR) ) AS VOL72005, 
(SELECT SUM(c82005.volume) FROM fioulcommande_commandes c82005 WHERE MONTH(c82005.DATE_ENR)=8 AND f.IDFOURNISSEUR = c82005.IDFOURNISSEUR AND YEAR(c82005.DATE_ENR)=2005 GROUP BY MONTH(c82005.DATE_ENR) ) AS VOL82005, 
(SELECT SUM(c92005.volume) FROM fioulcommande_commandes c92005 WHERE MONTH(c92005.DATE_ENR)=9 AND f.IDFOURNISSEUR = c92005.IDFOURNISSEUR AND YEAR(c92005.DATE_ENR)=2005 GROUP BY MONTH(c92005.DATE_ENR) ) AS VOL92005, 
(SELECT SUM(c102005.volume) FROM fioulcommande_commandes c102005 WHERE MONTH(c102005.DATE_ENR)=10 AND f.IDFOURNISSEUR = c102005.IDFOURNISSEUR AND YEAR(c102005.DATE_ENR)=2005 GROUP BY MONTH(c102005.DATE_ENR) ) AS VOL102005, 
(SELECT SUM(c112005.volume) FROM fioulcommande_commandes c112005 WHERE MONTH(c112005.DATE_ENR)=11 AND f.IDFOURNISSEUR = c112005.IDFOURNISSEUR AND YEAR(c112005.DATE_ENR)=2005 GROUP BY MONTH(c112005.DATE_ENR) ) AS VOL112005, 
(SELECT SUM(c122005.volume) FROM fioulcommande_commandes c122005 WHERE MONTH(c122005.DATE_ENR)=12 AND f.IDFOURNISSEUR = c122005.IDFOURNISSEUR AND YEAR(c122005.DATE_ENR)=2005 GROUP BY MONTH(c122005.DATE_ENR) ) AS VOL122005 ,
(SELECT SUM(total.volume) FROM fioulcommande_commandes total WHERE f.IDFOURNISSEUR = total.IDFOURNISSEUR GROUP BY f.IDFOURNISSEUR ) AS VOL_TOTAL 
 
FROM fioulcommande_fournisseurs f 
 
ORDER BY f.LIBFOURNISSEUR,f.CODEPDV

merci

[Maximilian]

Heu non, je parlais du jeu de résultats. Au passage il doit y avoir beaucoup plus simple (et plus rapide) pour ta requête.

[hackrobat]

ok d'acc désolé

voici le resultat du explain

Code :

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
 
1    PRIMARY    f    ALL                    322    USING filesort
14    DEPENDENT SUBQUERY    total    ALL                    20    USING WHERE; USING TEMPORARY; USING filesort
13    DEPENDENT SUBQUERY    c122005    ALL                    20    USING WHERE
12    DEPENDENT SUBQUERY    c112005    ALL                    20    USING WHERE
11    DEPENDENT SUBQUERY    c102005    ALL                    20    USING WHERE
10    DEPENDENT SUBQUERY    c92005    ALL                    20    USING WHERE
9    DEPENDENT SUBQUERY    c82005    ALL                    20    USING WHERE
8    DEPENDENT SUBQUERY    c72005    ALL                    20    USING WHERE
7    DEPENDENT SUBQUERY    c62005    ALL                    20    USING WHERE
6    DEPENDENT SUBQUERY    c52005    ALL                    20    USING WHERE
5    DEPENDENT SUBQUERY    c42005    ALL                    20    USING WHERE
4    DEPENDENT SUBQUERY    c32005    ALL                    20    USING WHERE
3    DEPENDENT SUBQUERY    c22005    ALL                    20    USING WHERE
2    DEPENDENT SUBQUERY    c12005    ALL                    20    USING WHERE