Probléme optimisation création croisé dynamique [Résolu]

[kamnouz]

Bonjour à tous ,

J'ai créé un croisé dynamique en procédant de la manière suivante :

- création d'un tableau à deux dimension qui contient des abonnées avec leur options.

ex :
abonne1 | option 1 | option2 | option3
abonne2 | option 2 | option3
etc

Voici le script :

Code :

1
2
3
4
5
6
 
$aboneOptions = array();
	while($uneligne=mysql_fetch_array($rq_abonne_options_codeService))
	{
		$aboneOptions[$uneligne['abonne']][] = "".rtrim($uneligne['options']). " (".rtrim($uneligne['code_service']).")";
	}

Ensuite, je créer dynamiquement une table MySQL qui recevra notre croisé dynamique qui ressemblera à cela:

abonne | nom | prénom| adresse| option1| option2| option3
-------------------------------------------------------------------------
abonne1 | zaza | toto | ..........| X | X |
abonne2 | zozo | titi | ..........| X | | X



Les champs (abonne, nom, prénom, adresse) sont des champs fixe mais les options sont des champs variables :

Voici le script SQL de la création de ma table dynamique

Code :

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
 
// creation d'une table dynamique
$requete = "CREATE TABLE `extract_excel` (
  `abonne` text NOT NULL,
  `nom` text NOT NULL,
  `prenom` text NOT NULL,
  `adresse` text NOT NULL,
  `num_abonne` text NOT NULL,
  ";
 
	while ($uneligne=mysql_fetch_array($rq_code_service)) 
	{  
		$requete.= "`".rtrim($uneligne['options']). " (".rtrim($uneligne['code_service']).")` text NOT NULL," ;//rtrim() permet de supprimer les espaces en fin de chaine 
	}
 $requete .= "`date_deconnexion` date NOT NULL) ;";
$creation_table=mysql_query($requete);
mysql_query("ALTER TABLE `extract_excel` DROP `date_deconnexion`");

Ensuite, je parcours le tableau à deux dimension, et lorsque je pointe sur un abonné j'injecte des données le concernant dans la table dynamique , et je met une croix en dessous de la bonne option avec une requête update :

Voici le script de mon tableau croisé dynamique :

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
 
$i=0;
$tab = array();
foreach($aboneOptions as $abone=>$options) 
{
$rq_2bis=mysql_query("SELECT DISTINCT abonne,nom,prenom,adresse from croise_dynamique where  abonne ='$abone' ");
$ligne=mysql_fetch_array($rq_2bis);
	$nom=$ligne['num_compte'];
	$prenom=$ligne['carte_jumelle'];
	$adresse=$ligne['fax'];
		// on injecte les donnees dans une table pour l'extraction excel 
		mysql_query ("insert into extract_excel(`abonne`,`nom`,`prenom`,`adresse`) values('$abonne','$nom','$prenom','$adresse')");
		$j=0;
		$cpt_true=0; // compteur pour determiner si option trouvé
			//on parcours les options
			foreach($options as $o) 
			{
				//tant qu'on a pas trouvé l'option de l'abonne on compare l'option  de la 'colone= $option[$j]' avec l'option de 'l'abonne=$o'
				if (isset($option[$j]))
				{
				while ((($cpt_true!=1)|| ($cpt_true==0)  ) and $j<=$nb_tablo)
				{
					if($option[$j]==$o) 
					{
					// on injecte les valeur de une table 
					mysql_query ("update extract_excel set`$o` ='X' ");
					$j++;
					$cpt_true=1;//1
					}
					else
					{
					$j++;
					}
				}
				$cpt_true=0;
				}
			}	
}

Le souci ici est que lorsqu’il y a 30 000 abonné par exemple le traitement est beaucoup trop long:
Quelqu’un aurait-il une solution d'optimisation?
Je désespère

[rawsrc]

Bonjour,

L'optimisation ne peut se faire que dans MySql, vu le nombre de données, la manière itérative est à proscrire (comme tu l'a constaté par toi-même)
Donnes la structure des tables gérant toutes ces informations.

[kamnouz]

Merci de ta réponse

Voici la structure de ma table mysql qui rempli mon tableau à deux dimensions :

Code :

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
 
CREATE TABLE IF NOT EXISTS `croise_dynamique` (
  `code_ent` text NOT NULL,
  `num_compte` text NOT NULL,
  `abonne` text NOT NULL,
  `nom_utili` text NOT NULL,
  `ref_client` text NOT NULL,
  `num_gsm` text NOT NULL,
  `num_sim` text NOT NULL,
  `carte_jumelle` text NOT NULL,
  `fax` text NOT NULL,
  `data` text NOT NULL,
  `imei` text NOT NULL,
  `code_tarif` text NOT NULL,
  `date_connexion` text NOT NULL,
  `dure_contrat` text NOT NULL,
  `options` text NOT NULL,
  `code_service` text NOT NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

Voici la structure de ma table dynamique qui va recevoir mon tableau croisé dynamique:

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
 
CREATE TABLE IF NOT EXISTS `extract_excel` (
  `code_groupe` text NOT NULL,
  `code_entreprise` text NOT NULL,
  `entite` text NOT NULL,
  `compte_client` text NOT NULL,
  `num_abonne` text NOT NULL,
  `utilisateur` text NOT NULL,
  `reference` text NOT NULL,
  `gsm` text NOT NULL,
  `num_sim` text NOT NULL,
  `num_carte_jumelle` text NOT NULL,
  `num_fax` text NOT NULL,
  `num_data` text NOT NULL,
  `num_imei` text NOT NULL,
  `code_tarif` text NOT NULL,
  `date_connexion` text NOT NULL,
  `duree_contrat` text NOT NULL,
  `la messagerie vocale (100021)` text NOT NULL,
  `la conférence mobile (100025)` text NOT NULL,
  `option visiophonie (100026)` text NOT NULL,
  `mode international (100060)` text NOT NULL,
  `la présentation du numéro (100130)` text NOT NULL,
  `secret appel par appel (100140)` text NOT NULL,
  `téléchargement du répertoire (100220)` text NOT NULL,
  `abonnement permanent (200280)` text NOT NULL,
  `Ajout couverture monde (DSI030)` text NOT NULL,
  `pass à l'usage (PGPD00)` text NOT NULL,
  `service WIFI (WIFI01)` text NOT NULL,
  `WIFI internet (WIFIFI)` text NOT NULL,
  `roaming WIFI international (WIFIIN)` text NOT NULL,
  `roaming WIFI national (WIFINA)` text NOT NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

[rawsrc]

Non, on s'est pas compris, c'est pas la structure des tables à remplir qui est importante mais la structure des tables gérant tes abonnées et leurs options.
Histoire de voir s'il est possible de procéder de manière ensembliste.

je parcours le tableau à deux dimension, et lorsque je pointe sur un abonné j'injecte des données le concernant dans la table dynamique

C'est pour éviter cette étape.

[kamnouz]

Il s'agit du 1er script, il gère les abonnes (3eme champs) et les options (avant dernier champs)
J' établie mon croisé dynamique à partir de cette table.

[rawsrc]

Comment tu stockes tes options ? A la queue le leu ? Avec un séparateur, texte code ?

[kamnouz]

De la manière suivante:


Abonne options


Abonne1 option1
Abonne1 option4
Abonne1 option6
Abonne1 option12
Abonne2 option6
Abonne2 option4
............ ........

[rawsrc]

Une piste, c'est d'extraire toutes les données en une passe en utilisant GROUP_CONCAT()
Quelque chose comme ça :

Code :

1
2
3
4
5
6
7
8
SELECT 
   `t_dvp_abonne`.`id`, 
   `t_dvp_abonne`.`nom`,
   GROUP_CONCAT(`t_dvp_option`.`idOption` ORDER BY `t_dvp_option`.`idOption` SEPARATOR " ") AS `opts`
FROM
   `t_dvp_abonne` INNER JOIN `t_dvp_option` ON `t_dvp_abonne`.`id` = `t_dvp_option`.`idAbonne`
GROUP BY
  `t_dvp_abonne`.`id`

Tu récupères tes options sous forme de chaine avec un espace pour séparateur

[kamnouz]

Mais comment puis-je intégrer ta requête dans mon croisé dynamique avec mes boucles foreach ?

[rawsrc]

Il y a une moyen d'obtenir ton tableau croisé dynamique de manière instantané sans passer par les tables temporaires. C'est rigide mais ça a l'avantage d'être performant.
On suppose que tu as deux tables : t_dvp_abonne(#id, nom) et t_dvp_option(#id, idAbonne, idOption)On pose que tes idOption correspondent à un libellé spécifique (je repris certains de tes libellés).
Pour obtenir ton tableau croisé dynamique, il suffit de faire quelque chose comme ça :

Code :

1
2
3
4
5
6
7
8
9
10
11
12
SELECT 
	`t_dvp_abonne`.`nom`,
	IF(SUM(IF(`t_dvp_option`.`idOption`=10,1,0)),'X', NULL) AS 'Mode international',
	IF(SUM(IF(`t_dvp_option`.`idOption`=15,1,0)),'X', NULL) AS 'Présentation du numéro',
	IF(SUM(IF(`t_dvp_option`.`idOption`=26,1,0)),'X', NULL) AS 'Secret appel par appel',
	IF(SUM(IF(`t_dvp_option`.`idOption`=40,1,0)),'X', NULL) AS 'Abonnement permanent',
	IF(SUM(IF(`t_dvp_option`.`idOption`=45,1,0)),'X', NULL) AS 'Couverture monde'
FROM
	`t_dvp_abonne`
	INNER JOIN `t_dvp_option` ON (`t_dvp_abonne`.`id` = `t_dvp_option`.`idAbonne`)
GROUP BY
   `t_dvp_abonne`.`nom`

Le seul point important c'est de bien te rendre compte que si tu rajoutes une option, il faudra penser à modifier en dur ta requête tableau croisé dynamique.

[kamnouz]

Bonjour je viens de construire ma requête pour créer mon croise dynamique:

Code :

1
2
3
4
5
6
7
8
9
10
11
 
$croise="
SELECT $table_temp_abonne.abonne as abonne,";
	while($uneligne=mysql_fetch_array($rq2))
	{
		$croise.="	IF(SUM(IF(`$table_temp `.`code_service`=".$uneligne['code_service'].",1,0)),'X', NULL) AS `".$uneligne['options']."`";
	} 
		$croise.="FROM $table_temp_abonne
		INNER JOIN $table_temp ON (`$table_temp_abonne.abonne` = `$table_temp`.`abonne`)
		GROUP BY $table_temp_abonne.abonne
" OR DIE ("Erreur insertion requete croise");

Mais lorsque je veux afficher le résultat comme ceci

Code :

1
2
3
4
5
6
 
while($ligne=mysql_fetch_array($resultat_croise))
{
	echo $ligne['abonne'];
 
}

Mais j'ai l'erreur suivante :

Code :

1
2
 
mysql_fetch_array() expects parameter 1 to be resource, boolean

Quelqu'un peut-il m'aider ?

[rawsrc]

Bonjour,

Tu concatènes une string avec OR d'où le booléan
Voici comment procéder :

Code :

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
<?php
$sql = "SELECT $table_temp_abonne.abonne as abonne,";
while($uneligne = mysql_fetch_array($rq2)) {
   $croise[] = " IF(SUM(IF(`$table_temp`.`code_service`='".$uneligne['code_service']."',1,0)),'X', NULL) AS `".$uneligne['options']."`";
} 
$sql .= implode(', ', $croise);
$sql .= " FROM $table_temp_abonne
                INNER JOIN $table_temp ON (`$table_temp_abonne.abonne` = `$table_temp`.`abonne`)
          GROUP BY $table_temp_abonne.abonne";
 
$exec = mysql_query($sql);
if ( ! $exec) {
   die('Erreur croisé : ' . mysql_error());
}
 
while($ligne = mysql_fetch_assoc($exec)) {
   echo $ligne['abonne'];
}
?>

PS : Pas de question technique par MP

[kamnouz]

Merci pour ta réponse dsl pour le MP

Avec ce que tu m'a donné j'ai l'erreur de syntaxe suivante :

Code :

1
2
 
Erreur croisé : Erreur de syntaxe près de 'IF(SUM(IF(`SKQX7502_temp`.`code_service`=ENGI24,1,0)),'X', NULL) AS `réengagemen' à la ligne 1

[rawsrc]

J'ai repris le code, car le code service est un texte et pas un numerique (manquait les guillemets)

[kamnouz]

EN faite avec tout les paramètre que j'ai mis dans ma requête je me retrouve avec cette syntaxe :

Code :

1
2
3
4
5
6
7
 
SELECT SKQX7502_abonne_temp.abonne as abonne,
 IF(SUM(IF(`SKQX7502_temp`.`code_service`=200280,1,0)),'X', NULL) AS `abonnement permanent ` 
IF(SUM(IF(`SKQX7502_temp`.`code_service`=ENGI24,1,0)),'X', NULL) AS `reengagement iPhone 24 mois ` 
IF(SUM(IF(`SKQX7502_temp`.`code_service`=IPHONE,1,0)),'X', NULL) AS `routage iPhone `
 FROM SKQX7502_abonne_temp 
INNER JOIN SKQX7502_temp ON (`SKQX7502_abonne_temp.abonne` = `SKQX7502_temp`.`abonne`) GROUP BY SKQX7502_abonne_temp.abonne

Erreur

Code :

1
2
 
Erreur croisé : Erreur de syntaxe près de 'IF(SUM(IF(`SKQX7502_temp`.`code_service`=ENGI24,1,0)),'X', NULL) AS `réengagemen' à la ligne 1

Je n'arrive pas a voir mon erreur de syntaxe ...

[rawsrc]

Je t'ai dis que j'avais repris le code, reprends le aussi.
Il te manque des virgules entres les IF.

Code :

permanent ` IF(S

[kamnouz]

J'ai essayé de mettre des simples cote autour de mes codes services

Code :

1
2
 
IF(SUM(IF(`SKQX7502_temp`.`code_service`='200280',1,0)),'X', NULL) AS `abonnement permanent `

Mais rien n'y fait ..

[rawsrc]

Envoyé par kamnouz

Mais rien n'y fait ..

Voici la dernière version du code (copie du msg 12) :

Code :

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
<?php
$sql = "SELECT $table_temp_abonne.abonne as abonne,";
while($uneligne = mysql_fetch_array($rq2)) {
   $croise[] = " IF(SUM(IF(`$table_temp`.`code_service`='".$uneligne['code_service']."',1,0)),'X', NULL) AS `".$uneligne['options']."`";
} 
$sql .= implode(', ', $croise);
$sql .= " FROM $table_temp_abonne
                INNER JOIN $table_temp ON (`$table_temp_abonne.abonne` = `$table_temp`.`abonne`)
          GROUP BY $table_temp_abonne.abonne";
 
$exec = mysql_query($sql);
if ( ! $exec) {
   die('Erreur croisé : ' . mysql_error());
}
 
while($ligne = mysql_fetch_assoc($exec)) {
   echo $ligne['abonne'];
}
?>

[kamnouz]

Merci beaucoup !!
après quelques modif de syntaxe ça marche

Voici la dernière version du code :

Code :

1
2
3
4
5
6
7
8
9
 
while($uneligne = mysql_fetch_array($rq2)) {
   $croise[] = " IF(SUM(IF(`$table_temp`.`code_service`='".$uneligne['code_service']."',1,0)),'X', NULL) AS `".$uneligne['options']."`";
} 
$sql = "SELECT $table_temp_abonne.abonne as abonne,";
$sql .= implode(', ', $croise);
$sql .= " FROM $table_temp_abonne
                INNER JOIN $table_temp ON ($table_temp_abonne.abonne = `$table_temp`.`abonne`)
          GROUP BY $table_temp_abonne.abonne";