[Résolu] Split d'une chaine en lignes (continuation)

Bonjour,

Je continue un sujet clos, qui n'avait pas trouvé de réponses:
link alsacreations

Dans ce cas, j'ai un problème similaire:

il me faut séparer grace a mysql ceci:

|  id   |     test                            

|  1    | 123 = 123 , 543 = 987, ... 
|  2    | 65 = 21 , 569 = 212 , 6543 = 123  

vers

| p	|	v      |
+-----------------+
|123 |	321  |
| 543|	987  |
| ...	|	...	|
+------------------+

Le php est une solution actuelle, mais elle est trop ralentissante.
Le travail depuis mysql serait plus optimisé.

le code:

SELECT *,
SUBSTRING_INDEX(SUBSTRING_INDEX( `test_1` , ',', 1 ),'=',1) as p ,
SUBSTRING_INDEX(SUBSTRING_INDEX( `test_1` , ',', 1 ),'=',-1) as v
FROM test

marche tres bien, mais s'arrête a la premiere ligne valeur_1 | resultat_1, comment continuer vers valeur_2 | resultat_2 .... etc ?
Modifié par carlescampi (21 Nov 2013 - 21:29)

Tu ne peux pas produire plusieurs lignes à partir d'une seule, aucune fonction ne permet ça. Tu es donc obligé de le faire en php.

C'est le concepteur de la base qui est à blâmer: il aurait dû créer une table séparée pour ces valeurs, avec une clé étrangère en règle.

QuentinC a écrit :
Tu ne peux pas produire plusieurs lignes à partir d'une seule, aucune fonction ne permet ça. Tu es donc obligé de le faire en php.

Heureusement, depuis MySql 5, MySql est rentré dans la cour des grands, et cela n'est plus vrai du tout.

J'ai réussi avec une procédure (comme l'ancienne).

considérant les tables:

table1 :
  detail: varchar(255)

|         detail               |
––––––––––––––––––
| p1=v1,p2=v2,p3=v3 | 

table2:
  p: varchar(255)
  v: varchar(255)

j'ai d'abord créé une fonction pratique sur ma database (pas de moi, récupérée sur stackoverflow) :

CREATE FUNCTION SPLIT_STRING(str VARCHAR(255), delim VARCHAR(12), pos INT)
RETURNS VARCHAR(255)
RETURN REPLACE(SUBSTRING(SUBSTRING_INDEX(str, delim, pos),
       LENGTH(SUBSTRING_INDEX(str, delim, pos-1)) + 1),
       delim, '');

Ensuite j'ai créé une procédure:

DROP PROCEDURE if exists export_my_data;

DELIMITER #
CREATE PROCEDURE export_my_data()
BEGIN

DECLARE max int UNSIGNED DEFAULT 1000;
DECLARE counter int UNSIGNED DEFAULT 1;
DECLARE val varchar(255);
DECLARE line varchar(255);
DECLARE part varchar(255);
DECLARE value varchar(255);
DECLARE done INT DEFAULT FALSE;

DECLARE cur1 CURSOR FOR SELECT detail FROM table1;
DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;
OPEN cur1;
the_loop: LOOP
  FETCH cur1 INTO line;
  IF done THEN
    LEAVE the_loop;
  END IF;
  
  TRUNCATE TABLE table2;
  WHILE (CHAR_LENGTH(SPLIT_STRING(line,',',counter)) > 0) DO
    SET val = SPLIT_STRING(line,',',counter);
    SET part = SPLIT_STRING(val,'=',1);
    SET value = SPLIT_STRING(val,'=',2);
    INSERT INTO table2 (p, v) values (part, value);
    SET counter=counter+1;
  END WHILE;
  
END LOOP the_loop;
CLOSE cur1;
  
END #

Et ça marche!

– Je ne suis pas DBA, on peut sûrement améliorer cela.
– C'est la première fois que j'essaie ces procédures avancées
– on doit pouvoir optimiser le traitement avec des procédures préparées.

Si tu as des questions n'hésite pas

p.s: Alsacréations est orienté standards du web, on y trouve, outre des intégrateurs, des développeurs web, des graphistes, ou tout autre métier qui peuvent graviter autours.

L'administration de bases de données est un monde bien à part (et un métier aussi), et tu en trouvera peu ici (pas?). La plupart des développeurs web ont juste une connaissance empirique pour leur permettre de taper dedans. Pour ce genre de question, tu auras de meilleures réponses sur un forum spécialisé DBA ou MySql.

Merci pour vos réponses !

@kustolovic: c'est magnifique ce que tu viens de faire!! ça marche sur mon mysql local, .... mais sur mon hébergeur (infomaniak.ch), je n'ai pas le droit d'appliquer des PROCEDURES ni des FUNCTIONS

J'ai deux solution:
- je passe en serveur dedié et la pas de probleme (meilleure solution)
- je cherche des solutions alternatives...

Avant de passer en dédié, je vais suivre ton conseil, je vas créer un post dans un forum spécialisé, si je trouve réponse, je vous envoie la solution, sinon je crée la PROCEDURE dans un dédié.

@QuentinC: T'a tout a fait raison, c'est l'un des défauts de mysql, et la PROCEDURE ne sera jamais aussi rapide qu'en direct, mais ça vaut le coup d'essayer. Merci pour ta réponse aussi .
Modifié par carlescampi (22 Nov 2013 - 10:05)

Oh, désolé, je ne pensais pas qu'on pouvait faire ce genre de chose avec des procédures stockées. IL me semblais que les procédures stockées en MySQL, c'était plutôt pourri et ultra-limité.

Par contre, niveau performance, je pense que ça ne pourra sûrement jamais battre une jointure adéquate sur une table en bonne et due forme. Ca mériterait d'être testé dans un benchmark.

QuentinC a écrit :
C'est le concepteur de la base qui est à blâmer: il aurait dû créer une table séparée pour ces valeurs, avec une clé étrangère en règle.

+1 puisque les données ne sont pas canoniques et le type de données est redondant à vue de nez. En php, je vois pas trop le soucis. Ça coûte qu'un implode et une boucle supplémentaire.
Modifié par niuxe (22 Nov 2013 - 16:23)

Bonjour,

J'ai cherché sur le web, et j'ai trouvé dans tous les cas des PROCEDURES mysql pour effectuer l'équivalent de SPLIT.

C'est evident que c'est le "concepteur de la base qui est a blamer" (moi ), puisque j'ai crée un systeme de TAGS : "categorie" = "subcategorie" , "cat" = "sub" ... où mon but était qu'il soit illimité, puisque si tu établis une colonne par categorie, c'est limité.

Au niveau de la recherche il n'y a aucun problème, c'est même trés efficace.

Mon problème viens au niveau du système multilinguage, puisque chaque "cat" et "subcat" correspondent a des id qui doivent amener le mot ou la phrase traduite:
par exemple: 45363 = bonjour

et là c'a deviens lourd puisque:

1 - je cherche la ligne par mysql:
resultat: 1=2 , 765=123 ,764=5433 ...

2 - je décortique avec explode:
1 = 2
765 = 123 ...

3 - je dois faire un boucle en PHP, avec une sous requette MYSQL en boucle pour trouver le mot qui correspond a la ID.

mysql_result($sql,0); 1 = bonjour --> {boucle + mysql} 2 = salut --> etc

c'est là où ca devient trés lourd....

l'idée était celle de corresponde avec une seule requette les id et les traductions en une seule requette.

donc : bonjour = salut , tata = toto, etc...

Même si je n'ai pas trouvé la solution qui me va le mieux, j'ai insisté a écrire tout ce fonctionnement, pour aider les personnes qui ont trouvé des problèmes similaires.

le site est celui-ci : andorra.negocia.me et vous allez voir que le problème de la lenteur de la page est surtout lié a PHP (donc a mysql ...)...

Merci pour vos réponses.

La solution propre en vrai SQL n'est pas si compliquée que ça.

Tu pourrais imaginer quelque chose comme ceci :

Structure

create table traductions (
id int unsigned auto_increment,
val varchar(255) not null,
...
primary key(id));


create table correspondances (
left int unsigned not null,
right int unsigned not null,
primary key(left,right));

ET ce genre de requête pour récupérer toutes les correspondances :

select t1.val, t2.val
from correspondances c
join traductions t1 on t1.id = c.left
join traductions t2 on t2.id = c.right

ET si la liste de correspondances est différente selon l'article, il suffit d'ajouter un id_article dans la table correspondances et le where qui va bien dans la requête.

Et si tu as besoin de tous les articles qui utilisent un mot particulier, il suffit de requêter sur left ou right.

La table correspondance est ce qu'on appelle une table de liaison. C'est la bonne façon de faire quand on a une relation plusieurs à plusieurs entre deux entités A et B et qu'on doit potentiellement requêter dans les deux sens, c-à-d tous les B utilisés dans un certain A ou tous les A utilisés dans un certain B. Une bonne application de cette technique est un système de tags pour des articles, car un article a plusieurs tags, un même tag a en commun plusieurs articles, et on doit rapidement pouvoir récupérer la liste des tags d'un article particulier aussi bien que la liste des articles ayant un tag particulier.

J'espère que ça t'aide et que mes explications ne sont pas trop capilotractées !

carlescampi a écrit :

C'est evident que c'est le "concepteur de la base qui est a blamer" (moi ), puisque j'ai crée un systeme de TAGS : "categorie" = "subcategorie" , "cat" = "sub" ... où mon but était qu'il soit illimité, puisque si tu établis une colonne par categorie, c'est limité.

J'ai enfin compris pourquoi ta base est mal modélisée. En fait tu veux créer une base de données en arbre. Sur le net, tu trouveras pas mal d'exemples (structure arbre mysql/sql - taxonomy sql/mysql - etc.). Là, je viens de faire une petite recherche et je suis tombé sur cet article : http://stephanelegrand.wordpress.com/2009/01/03/gestion-dune-structure-darbre-sous-mysql/

Pour un système de tag, c'est différent. Il y a trois tables. Ex :
- table films
- table genres
- table films_genres

ex de données table films :

id | name
 1 | La mort de Pépette en prison
 2 | oui oui en vacances
 3 | Le chat de l'au delà 

ex de données table genres :

id | name
 1 | Comédie
 2 | Épouvante
 3 | action
 4 | SF
 5 | fantastique
 6 | policier
 7 | drame

ex de données table films_genres :

id | film_id | genre_id
1  |     1     |      1
2  |     1     |      3
3  |     1     |      5
4  |     2     |      6
5  |     2     |      7
6  |     3     |      3
7  |     3     |      4
8  |     3     |      5

Ce qui donnera si on fait ce genre de requête :

SELECT 
	f.name AS titre_film,
	g.name AS genre,
FROM 
	films AS f,
	genres AS g,
	films_genres AS fg 
WHERE 
	fg.film_id = f.id 
AND 
	fg.genre_id = g.id

* Fait de tête. Je peux m'être trompé
Ce qui te donnera en sortie :

titre_film                   | genre
La mort de Pépette en prison | Comédie
La mort de Pépette en prison | action
La mort de Pépette en prison | fantastique
oui oui en vacances          | policier
etc.

a écrit :
J'ai enfin compris pourquoi ta base est mal modélisée. En fait tu veux créer une base de données en arbre. Sur le net, tu trouveras pas mal d'exemples (structure arbre mysql/sql - taxonomy sql/mysql - etc.). Là, je viens de faire une petite recherche et je suis tombé sur cet article :
http://stephanelegrand.wordpress.com/2009/01/03/gestion-dune-structure-darbre-sous-mysql/

+1 pour la représentation intervalaire pour les structures arborescentes. Le seul vrai inconvénient de cette technique (pas indiquée dans l'article) est que les insertions, déplacements et suppressions sont beaucoup plus coûteuses que pour la méthode récursive.
Voir cet article beaucoup plus complet sur le sujet: http://sqlpro.developpez.com/cours/arborescence/
Ou celui-là qui est plus simple à comprendre: http://fr.openclassrooms.com/informatique/cours/la-representation-intervallaire

a écrit :
Pour un système de tag, c'est différent. Il y a trois tables.

Oui; c'était clair pour moi, mais peut-être que ce n'était pas si clair dans mon message, je l'ai plus ou moins sous entendu.

Merci a tous,

C'est tout simplement ce que je cherchais, je vais m'y mettre de suite. Ce qui résultera changer toute ma DB, mais le résultat vaudra le coup.

Je passe le sujet comme résolu!

Merci aussi a Alsacréation, pour regrouper un forum aussi complet, avec des membres aussi interessant.