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 : Sélectionner tout - Visualiser dans une fenêtre à part
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";
  }