Discussion :

[moimp]

Bonjour,
A partir de la liste officielle des codes NAF, je souhaite à partir d'un formulaire PHP sélectionner en 4 étapes une activité (Le PHP n'est pas le problème ici)
Chaque requête alimente les options d'une liste de choix, j'ai donc 4 listes et 4 requêtes.
Je joins un exemple de la table (ne pas tenir compte de l'erreur d'encodage).
Je n'arrive pas à établir les requêtes 2 à 4.
J'espère être suffisamment clair. Si ce n'est pas le cas, merci de me le signaler.

Principe:
Etape 1, niveau 1:
L'utilisateur sélectionne l'id d'une section (142 dans l'exemple ci-dessous) dans la liste des sections comme ceci:

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
		SELECT id, code, fr_text
		FROM naf
		WHERE code LIKE 'SECTION%'
		ORDER BY id

Etape 2: Sélection d'un groupe de niveau 2 de la section choisie par l'utilisateur
Il faut donc trouver tous les groupes de la section choisie (par exemple ici les codes 10, 11, 12, 13) jusqu'à la section suivante (le dernier id à sélectionner dans la requête est celui qui précède immédiatement la section suivante soit ici SECTION D)
Etape 3: Sélection d'un groupe de niveau 3
Dans l'exemple, il faut trouver toutes les lignes qui contiennent les codes 10.0, 10.1, 10.2, etc. qui précède le code 11.
Etape 4: Sélection d'un id de la liste précédente.

Extrait de la table:

id	ligne	code	fr_text
137	169	09.10	Activités de soutien Ã* l'extraction d'hydrocarbures
138	170	09.10Z	Activités de soutien Ã* l'extraction d'hydrocarbures
139	172	09.9	Activités de soutien aux autres industries extractives
140	173	09.90	Activités de soutien aux autres industries extractives 
141	174	09.90Z	Activités de soutien aux autres industries extractives 
142	176	SECTION C	INDUSTRIE MANUFACTURIÈRE
143	178	10	Industries alimentaires
144	180	10.1	Transformation et conservation de la viande et préparation de produits Ã* base de viande
145	181	10.11	Transformation et conservation de la viande de boucherie
146	182	10.11Z	Transformation et conservation de la viande de boucherie
147	183	10.12	Transformation et conservation de la viande de volaille
148	184	10.12Z	Transformation et conservation de la viande de volaille
149	185	10.13	Préparation de produits Ã* base de viande
150	186	10.13A	Préparation industrielle de produits Ã* base de viande
151	187	10.13B	Charcuterie
152	189	10.2	Transformation et conservation de poisson, de crustacés et de mollusques

[SQLpro]

L'utilisation de l'opérateur LIKE sans utiliser de joker n'a aucun intérêt. Pire, il ralentit les performances !

Commencez par apprendre les bases du SQL :
https://sqlpro.developpez.com/cours/sqlaz/select/#L3.4

A +

[moimp]

J'accepte la critique quand elle est justifiée: En regardant bien, j'ai le joker % à la fin dans "SECTION%".

[tatayo]

Bonjour,
Peux-tu nous donner la structure des tables, et un jeu de test de chaque table ainsi que le résultat attendu ?
J'ai du mal à voir le lien entre les différentes tables.

Tu commences par sélectionner une section (dans ton exemple 142).
Ensuite tu vas chercher tous les groupes de cette section. Là dans un exemple 142 n'apparaît qu'une seul fois. Tu parles aussi d'une section D, qui n'est pas dans ton exemple.

Tu prends ensuite les lignes dont le code vaut 10.0 (qui n'est pas dans la liste), 10.1, 10.2... jusqu'à 11. Est-ce que 10.12Z doit aussi être retenu ?

Comment se fait le "passage" entre la section C et la section D ?

Comment se fait le lien entre la table section et la table groupe, vu que l'id de la table section n'est pas dans la table groupe ?

Tatayo.

[moimp]

Peux-tu nous donner la structure des tables, et un jeu de test de chaque table ainsi que le résultat attendu ?
J'ai du mal à voir le lien entre les différentes tables.

Je n'ai qu'une table nommée `naf` qui correspond à l'extrait donné dans mon post dont la structure est la suivante:

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
CREATE TABLE IF NOT EXISTS `naf` (
  `id` int(10) NOT NULL AUTO_INCREMENT,
  `ligne` varchar(10) NOT NULL,
  `code` varchar(10) NOT NULL,
  `fr_text` varchar(255) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='Révision 2';

La table correspond à un extrait du fichier de l'INSEE formaté (suppression des lignes vides et encodage)

Tu commences par sélectionner une section (dans ton exemple 142).
Ensuite tu vas chercher tous les groupes de cette section. Là dans un exemple 142 n'apparaît qu'une seule fois. Tu parles aussi d'une section D, qui n'est pas dans ton exemple.

Comme je l'ai dit, l'exemple est un extrait représentatif de la table complète. 142 n'apparaît qu'une seule fois parce que la colonne id est la clef primaire. La SECTION D se trouve effectivement plus loin dans la table.

Tu prends ensuite les lignes dont le code vaut 10.0 (qui n'est pas dans la liste), 10.1, 10.2... jusqu'à 11. Est-ce que 10.12Z doit aussi être retenu ?

Le code 10.12Z ne doit pas encore retenu. Il fait partie du niveau 4.

Comment se fait le "passage" entre la section C et la section D ?

Il n'y a pas de "passage" d'une section à l'autre.

Comment se fait le lien entre la table section et la table groupe, vu que l'id de la table section n'est pas dans la table groupe ?

Il n'y a qu'une table, par contre c'est peut être une bonne idée de scinder la table en plusieurs table. Sur le site de l'INSEE, je vois qu'il existe aussi des fichiers Excel partiels que je peux peut-être combiner. Ce serait peut-être plus judicieux.

J'ai commencé à regarder comment travailler avec des sous-requêtes mais ce n'est peut-être pas la bonne solution.

Le principe:
Je sélectionne un niveau 1 (SECTION) en PHP (ici l'id 142).
Parmi ce niveau 1 je sélectionne en PHP un niveau 2 qui appartient au niveau 1 sélectionné.
Parmi le niveau 2, je sélectionne en PHP un niveau 3 qui appartient au niveau 3 sélectionné.
etc.

[tatayo]

Donc si j'ai bien compris:
On prend la ligne dont l'id vaut ici 142
On cherche la ligne de la section suivante (comment ?)
On prend les lignes qui sont entre les deux lignes précédentes, donc celles dont l'id est entre 142 et (à vue de nez) 153, dont le code est dans la liste 10,11,12,13, mais sans '.'.
J'imagine que 11, 12, 13 ne sont pas visibles ici, mais que la ligne 153 à pour code 14.
Et enfin si on sélectionne 11, le code des lignes de niveau 3 commence par 11., ont ensuite un "sous-code" numérique mais sans le 0 avant, et n'ont pas de lettre à la fin.

J'ai bon ?

Tatayo.

[moimp]

C'est à peu près ça, mais comme je l'ai indiqué l'INSEE propose des tables découpées par niveau, je vais donc creuser cette piste et revenir vers toi. En attendant, je mets ma question en suspens.

[escartefigue]

Bonjour noimp

B
Etape 3: Sélection d'un groupe de niveau 3
Dans l'exemple, il faut trouver toutes les lignes qui contiennent les codes 10.0, 10.1, 10.2, etc. qui précède le code 11.

142 176 SECTION C INDUSTRIE MANUFACTURIÈRE
143 178 10 Industries alimentaires
144 180 10.1 Transformation et conservation de la viande et préparation de produits Ã* base de viande
145 181 10.11 Transformation et conservation de la viande de boucherie
146 182 10.11Z Transformation et conservation de la viande de boucherie
147 183 10.12 Transformation et conservation de la viande de volaille
148 184 10.12Z Transformation et conservation de la viande de volaille
149 185 10.13 Préparation de produits Ã* base de viande
150 186 10.13A Préparation industrielle de produits Ã* base de viande
151 187 10.13B Charcuterie
152 189 10.2 Transformation et conservation de poisson, de crustacés et de mollusques

Le code 10.12Z ne doit pas encore retenu. Il fait partie du niveau 4.

La notion de "niveau" n'apparaît nullement dans la table et c'est la que la bât blesse : comment déterminer cette notion sans laquelle on ne peut pas savoir quelles sont les lignes éligibles.
A priori, il manque une relation reflexive typique des relations hierarchiques pour établir les liens et donc les niveaux.

[moimp]

J'ai complètement repris l'affaire avec une structure bdd différente en 6 tables dont je donne les structures:
Niveau 1:

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
CREATE TABLE IF NOT EXISTS `naf1` (
  `id` int(10) UNSIGNED NOT NULL AUTO_INCREMENT,
  `code` varchar(1) DEFAULT NULL,
  `fr_text` varchar(146) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='Sections INSEE';

idem pour les niveaux 2 (table naf2) à 5 (table naf5). Seules la longueur des varchar et les commentaires changent.
Les colonnes 'code' ont des formats différents:
Niveau 1: Une lettre de A à U
Niveau 2: Deux chiffres de 01 à 99
Niveau 3: NN.N de 01.1 à 99.0
Niveau 4: NN.NN de 01.11 à 99.00
Niveau 5: NN.NNZ de à 99.00Z (c'est toujours la lettre Z) de 01.11Z à 99.00Z

J'ai ensuite une requête de liens dont je donne un extrait tronqué:

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
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
CREATE TABLE IF NOT EXISTS `naf_structure` (
  `id` int(10) UNSIGNED NOT NULL AUTO_INCREMENT,
  `niv5` varchar(6) DEFAULT NULL,
  `niv4` varchar(5) DEFAULT NULL,
  `niv3` varchar(4) DEFAULT NULL,
  `niv2` varchar(2) DEFAULT NULL,
  `niv1` varchar(1) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=733 DEFAULT CHARSET=utf8;
 
INSERT INTO `naf_structure` (`id`, `niv5`, `niv4`, `niv3`, `niv2`, `niv1`) VALUES
(1, '01.11Z', '01.11', '01.1', '01', 'A'),
(2, '01.12Z', '01.12', '01.1', '01', 'A'),
(3, '01.13Z', '01.13', '01.1', '01', 'A'),
(4, '01.14Z', '01.14', '01.1', '01', 'A'),
(5, '01.15Z', '01.15', '01.1', '01', 'A'),
(6, '01.16Z', '01.16', '01.1', '01', 'A'),
(7, '01.19Z', '01.19', '01.1', '01', 'A'),
(8, '01.21Z', '01.21', '01.2', '01', 'A'),
(9, '01.22Z', '01.22', '01.2', '01', 'A'),
(10, '01.23Z', '01.23', '01.2', '01', 'A'),
(11, '01.24Z', '01.24', '01.2', '01', 'A'),
(12, '01.25Z', '01.25', '01.2', '01', 'A'),
(13, '01.26Z', '01.26', '01.2', '01', 'A'),
(14, '01.27Z', '01.27', '01.2', '01', 'A'),
(15, '01.28Z', '01.28', '01.2', '01', 'A'),
(16, '01.29Z', '01.29', '01.2', '01', 'A'),
(17, '01.30Z', '01.30', '01.3', '01', 'A'),
(18, '01.41Z', '01.41', '01.4', '01', 'A'),
(19, '01.42Z', '01.42', '01.4', '01', 'A'),
(20, '01.43Z', '01.43', '01.4', '01', 'A'),
(21, '01.44Z', '01.44', '01.4', '01', 'A'),
(22, '01.45Z', '01.45', '01.4', '01', 'A'),
(23, '01.46Z', '01.46', '01.4', '01', 'A'),
(24, '01.47Z', '01.47', '01.4', '01', 'A'),
(25, '01.49Z', '01.49', '01.4', '01', 'A'),
(26, '01.50Z', '01.50', '01.5', '01', 'A'),
(27, '01.61Z', '01.61', '01.6', '01', 'A'),
(28, '01.62Z', '01.62', '01.6', '01', 'A'),
(29, '01.63Z', '01.63', '01.6', '01', 'A'),
(30, '01.64Z', '01.64', '01.6', '01', 'A'),
(31, '01.70Z', '01.70', '01.7', '01', 'A'),
(32, '02.10Z', '02.10', '02.1', '02', 'A'),
(33, '02.20Z', '02.20', '02.2', '02', 'A'),
(34, '02.30Z', '02.30', '02.3', '02', 'A'),
(35, '02.40Z', '02.40', '02.4', '02', 'A'),
(36, '03.11Z', '03.11', '03.1', '03', 'A'),
(37, '03.12Z', '03.12', '03.1', '03', 'A'),
(38, '03.21Z', '03.21', '03.2', '03', 'A'),
(39, '03.22Z', '03.22', '03.2', '03', 'A'),
(40, '05.10Z', '05.10', '05.1', '05', 'B'),
(41, '05.20Z', '05.20', '05.2', '05', 'B'),
(42, '06.10Z', '06.10', '06.1', '06', 'B'),
(43, '06.20Z', '06.20', '06.2', '06', 'B'),
(44, '07.10Z', '07.10', '07.1', '07', 'B'),
(45, '07.21Z', '07.21', '07.2', '07', 'B'),
(46, '07.29Z', '07.29', '07.2', '07', 'B'),
(47, '08.11Z', '08.11', '08.1', '08', 'B'),
(48, '08.12Z', '08.12', '08.1', '08', 'B'),
(49, '08.91Z', '08.91', '08.9', '08', 'B'),
(50, '08.92Z', '08.92', '08.9', '08', 'B'),
(51, '08.93Z', '08.93', '08.9', '08', 'B'),
(52, '08.99Z', '08.99', '08.9', '08', 'B'),
(53, '09.10Z', '09.10', '09.1', '09', 'B'),
(54, '09.90Z', '09.90', '09.9', '09', 'B'),
(55, '10.11Z', '10.11', '10.1', '10', 'C'),
(56, '10.12Z', '10.12', '10.1', '10', 'C'),
(57, '10.13A', '10.13', '10.1', '10', 'C'),
(58, '10.13B', '10.13', '10.1', '10', 'C'),
(59, '10.20Z', '10.20', '10.2', '10', 'C'),
(60, '10.31Z', '10.31', '10.3', '10', 'C'),
(61, '10.32Z', '10.32', '10.3', '10', 'C'),
(62, '10.39A', '10.39', '10.3', '10', 'C'),
(63, '10.39B', '10.39', '10.3', '10', 'C'),
(64, '10.41A', '10.41', '10.4', '10', 'C'),
(65, '10.41B', '10.41', '10.4', '10', 'C'),
(66, '10.42Z', '10.42', '10.4', '10', 'C'),
(67, '10.51A', '10.51', '10.5', '10', 'C'),
(68, '10.51B', '10.51', '10.5', '10', 'C'),
(69, '10.51C', '10.51', '10.5', '10', 'C'),
(70, '10.51D', '10.51', '10.5', '10', 'C'),
(71, '10.52Z', '10.52', '10.5', '10', 'C'),
(72, '10.61A', '10.61', '10.6', '10', 'C'),
(73, '10.61B', '10.61', '10.6', '10', 'C'),
(74, '10.62Z', '10.62', '10.6', '10', 'C'),
(75, '10.71A', '10.71', '10.7', '10', 'C'),
(76, '10.71B', '10.71', '10.7', '10', 'C'),
(77, '10.71C', '10.71', '10.7', '10', 'C'),
(78, '10.71D', '10.71', '10.7', '10', 'C'),
(79, '10.72Z', '10.72', '10.7', '10', 'C'),
(80, '10.73Z', '10.73', '10.7', '10', 'C'),
(81, '10.81Z', '10.81', '10.8', '10', 'C'),
(82, '10.82Z', '10.82', '10.8', '10', 'C'),
(83, '10.83Z', '10.83', '10.8', '10', 'C'),
(84, '10.84Z', '10.84', '10.8', '10', 'C'),
(85, '10.85Z', '10.85', '10.8', '10', 'C'),
(86, '10.86Z', '10.86', '10.8', '10', 'C'),
(87, '10.89Z', '10.89', '10.8', '10', 'C'),
(88, '10.91Z', '10.91', '10.9', '10', 'C'),
(89, '10.92Z', '10.92', '10.9', '10', 'C'),
(90, '11.01Z', '11.01', '11.0', '11', 'C'),
(91, '11.02A', '11.02', '11.0', '11', 'C'),
(92, '11.02B', '11.02', '11.0', '11', 'C'),
(93, '11.03Z', '11.03', '11.0', '11', 'C'),
(94, '11.04Z', '11.04', '11.0', '11', 'C'),
(95, '11.05Z', '11.05', '11.0', '11', 'C'),
(96, '11.06Z', '11.06', '11.0', '11', 'C'),
(97, '11.07A', '11.07', '11.0', '11', 'C'),
(98, '11.07B', '11.07', '11.0', '11', 'C'),
(99, '12.00Z', '12.00', '12.0', '12', 'C'),
(100, '13.10Z', '13.10', '13.1', '13', 'C')

Il me faut 5 requêtes pour alimenter les 5 listes de choix successives.
La première requête ne pose aucun problème (on prend toute la table naf1):

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
SELECT id, code, fr_text
FROM naf1
ORDER BY code

Pour les autres niveaux, j'ai écrit la requête PHP suivante qui ne fonctionne pas. Pourquoi?

Code PHP :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
SELECT n.id, n.code, n.fr_text
FROM naf$level n			// $level est le niveau (1 à 5)
	INNER JOIN naf_structure s
	ON n.code=s.niv$level		// $level est le niveau (1 à 5)
WHERE code LIKE '$filter'		// $filter est la valeur sélectionnée dans la liste d'options précédente (1 à 5)
ORDER BY code

[Artemus24]

Salut moimp.

Quand on n'arrive pas à résoudre un problème complexe, il faut alors le décomposer afin d'être en mesure de pouvoir résoudre chaque chaque étape.

D'après ce que j'ai pu comprendre, ton fichier "int_courts_naf_rev_2.xls" contient trois niveaux.
Le premier niveau est la "section".
Le deuxième niveau est ce que je nomme "titre". Dans la colonne "code", c'est le numéro qui se trouve avant le point.
Le troisième niveau est le plus bas, et se nomme article". Ce niveau correspond à ce qui se trouve après le point.

Je suis parti du fichier ".csv" que j'ai chargé dans une table de travail.
A partir de cette table de travail, j'ai dispatcher chaque ligne dans l'une de trois tables ci-dessus.
Afin de faire le lien, je me suis servi des numéros de lignes, en procédant par l'appartenance à l'interval.

Bien entendu, le but d'éclater le fichier excel en trois tables est d'avoir le modèle relationnel.
C'est-à-dire relier une table fille à la table mère par une clef étrangère. Et d'éviter des duplications de libellé.

Je ne donne pas l'exécutable car le vidage des tables est trop volumineux.

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
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
START TRANSACTION;
 
set session collation_connection = "latin1_general_ci";
 
-- ======================
-- Base de Données `base`
-- ======================
 
DROP DATABASE IF EXISTS `base`;
 
CREATE DATABASE `base`
	DEFAULT CHARACTER SET `latin1`
	DEFAULT COLLATE       `latin1_general_ci`;
 
USE `base`;
 
-- ============
-- Table `trav`
-- ============
 
CREATE TABLE `trav`
( `ligne`     integer unsigned NOT NULL primary key,
  `code`      varchar(255)     NOT NULL,
  `fr_text`   varchar(255)     NOT NULL
) ENGINE=InnoDB
  DEFAULT CHARSET=`latin1` COLLATE=`latin1_general_ci`
  ROW_FORMAT=COMPRESSED;
 
-- =================
-- Chargement `trav`
-- =================
 
LOAD DATA INFILE 'E:/30.MySql/02.Etudes/int_courts_naf_rev_2.csv'
     INTO TABLE `trav`
     CHARACTER SET latin1
     FIELDS TERMINATED            BY ';'
            OPTIONALLY ENCLOSED   BY '\"'
            ESCAPED               BY '\\'
     LINES  TERMINATED            BY '\r\n'
     IGNORE 1  LINES
     (@F1, @F2, @F3, @F4, @F5, @F6)
     set ligne = if(trim(@F1) = '', 0, trim(@F1)),
          code = trim(@F2),
       fr_text = replace(trim(@F3), '\n', ' ');
 
-- =========================
-- Vidage de la Table `trav`
-- =========================
 
select * from `trav`;
 
-- ===============
-- Table `section`
-- ===============
 
CREATE TABLE `section`
( `id`         integer unsigned NOT NULL auto_increment primary key,
  `ligne_min`  integer unsigned NOT NULL,
  `ligne_max`  integer unsigned     NULL,
  `code`       varchar(255)     NOT NULL,
  `fr_text`    varchar(255)     NOT NULL
) ENGINE=InnoDB
  DEFAULT CHARSET=`latin1` COLLATE=`latin1_general_ci`
  ROW_FORMAT=COMPRESSED;
 
-- ========================
-- Insertion dans `section`
-- ========================
 
insert into `section` (`code`,`fr_text`,`ligne_min`)
  select `code`,
         `fr_text`,
         `ligne`
    from `trav`
   where `code` like 'SECTION%';
 
-- =====================================
-- Mise à jour de la colonne `ligne_max`
-- =====================================
 
update          `section` as t1
left outer join `section` as t2
        on t2.`id` = t1.`id` + 1
       set t1.`ligne_max` = coalesce(t2.`ligne_min`, 4294967295);
 
-- ============================
-- Vidage de la Table `section`
-- ============================
 
select * from `section`;
 
-- =============
-- Table `titre`
-- =============
 
CREATE TABLE `titre`
( `id`         integer unsigned  NOT NULL auto_increment primary key,
  `ligne_min`  integer unsigned  NOT NULL,
  `ligne_max`  integer unsigned      NULL,
  `fk_clef`    integer unsigned      NULL,
  `code`       varchar(255)      NOT NULL,
  `fr_text`    varchar(255)      NOT NULL
) ENGINE=InnoDB
  DEFAULT CHARSET=`latin1` COLLATE=`latin1_general_ci`
  ROW_FORMAT=COMPRESSED;
 
-- ======================
-- Insertion dans `titre`
-- ======================
 
insert into `titre` (`code`,`fr_text`,`fk_clef`,`ligne_min`)
         select t1.`code`,
                t1.`fr_text`,
                t2.`id`,
                t1.`ligne`
 
           from `trav`    as t1
 
left outer join `section` as t2
             on t2.`ligne_min` <= t1.`ligne`
            and t2.`ligne_max` >= t1.`ligne`
 
          where t1.`code` not like 'SECTION%'
            and t1.`code` not like '%.%'
            and t1.`code` !=       ' ';
 
-- =====================================
-- Mise à jour de la colonne `ligne_max`
-- =====================================
 
update          `titre` as t1
left outer join `titre` as t2
             on t2.`id` = t1.`id` + 1
            set t1.`ligne_max` = coalesce(t2.`ligne_min`, 4294967295);
 
-- ==========================
-- Vidage de la Table `titre`
-- ==========================
 
select * from `titre`;
 
-- ===============
-- Table `article`
-- ===============
 
CREATE TABLE `article`
( `id`         integer unsigned  NOT NULL auto_increment primary key,
  `fk_clef`    integer unsigned      NULL,
  `code`       varchar(255)      NOT NULL
) ENGINE=InnoDB
  DEFAULT CHARSET=`latin1` COLLATE=`latin1_general_ci`
  ROW_FORMAT=COMPRESSED;
 
-- ========================
-- Insertion dans `article`
-- ========================
 
insert into `article` (`code`,`fk_clef`)
         select t1.`code`,
                t2.`id`
 
           from `trav`    as t1
 
left outer join `titre` as t2
             on t2.`ligne_min` <= t1.`ligne`
            and t2.`ligne_max` >= t1.`ligne`
 
          where t1.`code` not like 'SECTION%'
            and t1.`code`     like '%.%'
            and t1.`code` !=       ' ';
 
-- ============================
-- Vidage de la Table `article`
-- ============================
 
select * from `article`;
 
-- =========
-- Nettoyage
-- =========
 
drop table `trav`;
 
alter table `section` drop column `ligne_min`;
alter table `section` drop column `ligne_max`;
 
alter table `titre`   drop column `ligne_min`;
alter table `titre`   drop column `ligne_max`;
 
-- ========================
-- vidage de l'arborescence
-- ========================
 
select *
        from `article` as t1
 
  inner join `titre`   as t2
          on t2.`id` = t1.`fk_clef`
 
  inner join `section`   as t3
          on t3.`id` = t2.`fk_clef`;
 
-- ===
-- Fin
-- ===
 
COMMIT;
EXIT

A toi de réadapter ces trois tables à ta convenance.

Pour ce qui de tes questions de ton premier message, il n'y a plus aucune difficulter.
Etape 1 : tu selectionnes une ligne dans la table "section".
Etape 2 : à partie de l'id correspondant à la sélection de ta ligne dans la table "section", tu obtiens toutes les lignes de la table "titre", qui ont la même clef étrangère.
Etape 3 : à partie de l'id correspondant à la sélection de ta ligne dans la table "titre", tu obtiens toutes les lignes de la table "article", qui ont la même clef étrangère.
Etape 4 : tu as l'id qui correspond à la ligne qui a été sélectionner dans la table "article".

J'espère que tu sais faire une jointure entre deux tables.

@+

[escartefigue]

Bonjour Noimp

Plutôt que de s'intéresser aux structures tabulaires c'est à dire le "comment", il serait beaucoup plus efficace de décrire les règles de gestion et communiquer un exemple de résultat attendu en fonction du jeu de données c'est à dire le "quoi".
Un fois ce décor bien planté, on pourra en déduire les tables requises.

Quoi qu'il en soit, créer des colonnes varchar pour des tailles aussi petites que 6 ou moins est une erreur : le char fixe est dans ce cas plus performant.
En deçà de 20-25 caractères, préférez du char.

[moimp]

Bonjour et merci à tous.

J'ai provisoirement résolu mon problème en corrigeant la requête PHP donnée en #9 comme ceci:

Code php :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
$previousLevel = $level-1;
$sql = <<<SQL
	SELECT DISTINCT n.id, n.code, n.$col
	FROM naf$level n
		INNER JOIN naf_structure s
		ON n.code=s.niv$level
	WHERE s.niv$previousLevel LIKE '$filter'
	ORDER BY code

Cette solution a l'inconvénient d'être basée sur des tables non relationnelles avec des doublons. Elle présente l'avantage de correspondre aux 5 niveaux de l'INSEE. Je la garde provisoirement.

@escartefigue: Merci pour tes remarques.

Lorsque j'aurais avancé dans mon projet, je reviendrai sur la solution d'Artemus24 mais avec au moins 4 niveaux, les deux derniers étant très voisins.
Pour l'instant je marque le sujet résolu.

[Artemus24]

Salut à tous.

il serait beaucoup plus efficace de décrire les règles de gestion et communiquer un exemple de résultat attendu en fonction du jeu de données c'est à dire le "quoi".

Cela a été fait par Moimp dans son premier message.
Le problème de Moimp est d'obtenir les "id" des différents niveaux.
Sauf qu'il ne sait pas comment relier ces niveaux entre eux.
Ce qui implique de créer une table par niveau et de les relier par des clefs étrangères.

Cette solution a l'inconvénient d'être basée sur des tables non relationnelles avec des doublons.

La solution que je vous ai proposé permet de créer des tables relationnelles basées sur les clefs étrangères.
Avez-vous au moins jeté un coup d'œil à ce que j'ai fait ?

Si vous avez cinq niveaux, j'aimerai savoir comment faites vous pour les distinguer ?
Je rappelle que ma solution n'est qu'une ébauche en fonctionne de ce que j'ai compris de votre problème.

Il serait bien de préciser votre demande étape par étape, en indiquant les difficultés que vous avez rencontré dans vos tests.

@+

[moimp]

Bonjour à tous,

La solution que je vous ai proposé permet de créer des tables relationnelles basées sur les clefs étrangères.
Avez-vous au moins jeté un coup d'œil à ce que j'ai fait ?

Oui, j'ai jeté un coup d'œil et je n'ai pas l'habitude de faire travailler les gens pour rien. Mais comme actuellement, je suis concentré sur le PHP, et que j'ai une solution qui fonctionne, je reviendrais sur votre solution plus tard (voir #12). Promis!

[moimp]

Bonjour Artemus24,
Comme indiqué dans mon dernier message, je reviens pour tester ton code.
J'ai fait un copier/coller de ton code dans le menu SQL de phpMyAdmin, j'ai bien évidement modifié la ligne 33 de ton code pour adapter le chemin du fichier et j'ai également du supprimer le EXIT final.
A l'exécution du code, j'obtiens l'erreur suivante à cette même ligne 33:

#1290 - The MySQL server is running with the --secure-file-priv option so it cannot execute this statement

J'ai donc importé la table avec l'outil Importer de phpMyAdmin en changeant l'encodage en utf8_general_ci que j'utilise habituellement.
Je ne comprends pas ce que tu entends par "vidage" de la table ... alors que tu fais une sélection.
Je ne comprends pas non plus ce que tu entends par

Je ne donne pas l'exécutable car le vidage des tables est trop volumineux.

Par sécurité, je n'ai pas encore effectué le nettoyage final sinon jusque là tout va bien.

Je ne comprends pas cette partie de code:

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
update          `section` as t1
left outer join `section` as t2
        on t2.`id` = t1.`id` + 1
       set t1.`ligne_max` = coalesce(t2.`ligne_min`, 4294967295);

Pourquoi passer par deux alias?

Il ne me reste plus qu'à exploiter tout ça.

EDIT: Je ne comprends pas pourquoi tu n'as pas créé de clef étrangère (Je peux les créer). Y a t-il une raison?
EDIT2: Il manque la colonne fr_text sur la table article. J'ai essayé de l'ajouter avec le code suivant mais ça ne fonctionne pas.

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
UPDATE t1
SELECT *
FROM article AS t1
SET t1.fr_text=t2.fr_text
LEFT OUTER JOIN trav as t2
ON t2.code=t1.code

[Artemus24]

Salut moimp.

A l'exécution du code, j'obtiens l'erreur suivante à cette même ligne 33:

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

#1290 - The MySQL server is running with the --secure-file-priv option so it cannot execute this statement

Cette erreur signifie que tu n'as pas configuré ton serveur mysql pour utiliser l'outil "LOAD DATA INFILE".
J'ai cru comprendre que ton but était de charger le fichier "int_courts_naf_rev_2.xls" et de dispatcher son contenu dans différentes tables.

Comment as-tu fait pour charger ce fichier à partir de "LOAD DATA INFILE" si le serveur mysql n'a pas été configuré ?

Voici le paramétrage à placer dans le fichier my.ini :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
# ------------------------------ #
#     Load data local infile     #
# ------------------------------ #
 
local-infile = 1
 
secure-file-priv  = E:/30.MySql
 
loose-local-infile = on

J'ai donc importé la table avec l'outil Importer de phpMyAdmin

De quelle table parles-tu ?

Je ne comprends pas ce que tu entends par "vidage" de la table ...

En anglais, on parle de "dump", que j'ai traduit par "vidage".
En effet, ce n'est pas une remise à zéro de la table, mais bien la lecture de son contenu.

Je ne comprends pas non plus ce que tu entends par :

Je ne donne pas l'exécutable car le vidage des tables est trop volumineux.

A l'exécution de mon script mysql, chaque select produit un résultat qui est trop volumineux pour entrer dans un message de ce sujet.

Par sécurité, je n'ai pas encore effectué le nettoyage final sinon jusque là tout va bien.

Le nettoyage final pour faire quoi ?

Pourquoi passer par deux alias?

Cette question sous-entend que tu ne connais pas bien la syntaxe de l'update.
Dans cet update, la première table est celle qui sera mise à jour, tandis que la seconde table est une jointure.

EDIT: Je ne comprends pas pourquoi tu n'as pas créé de clef étrangère (Je peux les créer). Y a t-il une raison?

Mon script effectue la création des clef étrangères.

Dans la table "titre", ainsi que dans la table "article, la colonne :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

`fk_clef`    integer unsigned      NULL,

est une clef étrangère.

Oui, en effet, j'ai oublié d'ajouter les contraintes. C'est un oubli de ma part. Désolé.

EDIT2: Il manque la colonne fr_text sur la table article.

Ce n'est pas un oubli car le contenu de la colonne fr_text de la table "article" est identique au contenu de la colonne "fr_text" de la table "titre".
Puisque tu désires la présence de cette colonne, je l'ajoute dans le script suivant :

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
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
START TRANSACTION;
 
set session collation_connection = "latin1_general_ci";
 
-- ======================
-- Base de Données `base`
-- ======================
 
DROP DATABASE IF EXISTS `base`;
 
CREATE DATABASE `base`
	DEFAULT CHARACTER SET `latin1`
	DEFAULT COLLATE       `latin1_general_ci`;
 
USE `base`;
 
-- ============
-- Table `trav`
-- ============
 
CREATE TABLE `trav`
( `ligne`     integer unsigned NOT NULL primary key,
  `code`      varchar(255)     NOT NULL,
  `fr_text`   varchar(255)     NOT NULL
) ENGINE=InnoDB
  DEFAULT CHARSET=`latin1` COLLATE=`latin1_general_ci`
  ROW_FORMAT=COMPRESSED;
 
-- =================
-- Chargement `trav`
-- =================
 
LOAD DATA INFILE 'E:/30.MySql/02.Etudes/int_courts_naf_rev_2.csv'
     INTO TABLE `trav`
     CHARACTER SET latin1
     FIELDS TERMINATED            BY ';'
            OPTIONALLY ENCLOSED   BY '\"'
            ESCAPED               BY '\\'
     LINES  TERMINATED            BY '\r\n'
     IGNORE 1  LINES
     (@F1, @F2, @F3, @F4, @F5, @F6)
     set ligne = if(trim(@F1) = '', 0, trim(@F1)),
          code = trim(@F2),
       fr_text = replace(trim(@F3), '\n', ' ');
 
-- =========================
-- Vidage de la Table `trav`
-- =========================
 
select * from `trav`;
 
-- ===============
-- Table `section`
-- ===============
 
CREATE TABLE `section`
( `id`         integer unsigned NOT NULL auto_increment primary key,
  `ligne_min`  integer unsigned NOT NULL,
  `ligne_max`  integer unsigned     NULL,
  `code`       varchar(255)     NOT NULL,
  `fr_text`    varchar(255)     NOT NULL
) ENGINE=InnoDB
  DEFAULT CHARSET=`latin1` COLLATE=`latin1_general_ci`
  ROW_FORMAT=COMPRESSED;
 
-- ========================
-- Insertion dans `section`
-- ========================
 
insert into `section` (`code`,`fr_text`,`ligne_min`)
  select `code`,
         `fr_text`,
         `ligne`
    from `trav`
   where `code` like 'SECTION%';
 
-- =====================================
-- Mise à jour de la colonne `ligne_max`
-- =====================================
 
update          `section` as t1
left outer join `section` as t2
        on t2.`id` = t1.`id` + 1
       set t1.`ligne_max` = coalesce(t2.`ligne_min`, 4294967295);
 
-- ============================
-- Vidage de la Table `section`
-- ============================
 
select * from `section`;
 
-- =============
-- Table `titre`
-- =============
 
CREATE TABLE `titre`
( `id`         integer unsigned  NOT NULL auto_increment primary key,
  `ligne_min`  integer unsigned  NOT NULL,
  `ligne_max`  integer unsigned      NULL,
  `fk_clef`    integer unsigned      NULL,
  `code`       varchar(255)      NOT NULL,
  `fr_text`    varchar(255)      NOT NULL,
  CONSTRAINT `FK_01` FOREIGN KEY (`fk_clef`) REFERENCES `section` (`id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB
  DEFAULT CHARSET=`latin1` COLLATE=`latin1_general_ci`
  ROW_FORMAT=COMPRESSED;
 
-- ======================
-- Insertion dans `titre`
-- ======================
 
insert into `titre` (`code`,`fr_text`,`fk_clef`,`ligne_min`)
         select t1.`code`,
                t1.`fr_text`,
                t2.`id`,
                t1.`ligne`
 
           from `trav`    as t1
 
left outer join `section` as t2
             on t2.`ligne_min` <= t1.`ligne`
            and t2.`ligne_max` >= t1.`ligne`
 
          where t1.`code` not like 'SECTION%'
            and t1.`code` not like '%.%'
            and t1.`code` !=       ' ';
 
-- =====================================
-- Mise à jour de la colonne `ligne_max`
-- =====================================
 
update          `titre` as t1
left outer join `titre` as t2
             on t2.`id` = t1.`id` + 1
            set t1.`ligne_max` = coalesce(t2.`ligne_min`, 4294967295);
 
-- ==========================
-- Vidage de la Table `titre`
-- ==========================
 
select * from `titre`;
 
-- ===============
-- Table `article`
-- ===============
 
CREATE TABLE `article`
( `id`         integer unsigned  NOT NULL auto_increment primary key,
  `fk_clef`    integer unsigned      NULL,
  `code`       varchar(255)      NOT NULL,
  `fr_text`    varchar(255)      NOT NULL,
  CONSTRAINT `FK_02` FOREIGN KEY (`fk_clef`) REFERENCES `titre` (`id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB
  DEFAULT CHARSET=`latin1` COLLATE=`latin1_general_ci`
  ROW_FORMAT=COMPRESSED;
 
-- ========================
-- Insertion dans `article`
-- ========================
 
insert into `article` (`code`,`fr_text`,`fk_clef`)
         select t1.`code`,
                t1.`fr_text`,
                t2.`id`
 
           from `trav`    as t1
 
left outer join `titre` as t2
             on t2.`ligne_min` <= t1.`ligne`
            and t2.`ligne_max` >= t1.`ligne`
 
          where t1.`code` not like 'SECTION%'
            and t1.`code`     like '%.%'
            and t1.`code` !=       ' ';
 
-- ============================
-- Vidage de la Table `article`
-- ============================
 
select * from `article`;
 
-- =========
-- Nettoyage
-- =========
 
drop table `trav`;
 
alter table `section` drop column `ligne_min`;
alter table `section` drop column `ligne_max`;
 
alter table `titre`   drop column `ligne_min`;
alter table `titre`   drop column `ligne_max`;
 
describe `section`;
describe `titre`;
describe `article`;
 
-- ========================
-- vidage de l'arborescence
-- ========================
 
select *
        from `article` as t1
 
  inner join `titre`   as t2
          on t2.`id` = t1.`fk_clef`
 
  inner join `section`   as t3
          on t3.`id` = t2.`fk_clef`;
 
-- ===
-- Fin
-- ===
 
COMMIT;
EXIT

Je te donne aussi mon script batch qui permet de lancer le script mysql :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
@echo off
 
chcp 1252 > nul
 
set PATH=.;%WAMPSERVER%\bin\mysql\%MYSQL%\bin\;%PATH%
 
mysql < Base.sql  --verbose  --force
@echo.
pause
exit

A toi de le réadapter à ton environnement.

Le nombre de ligne est de :
--> section : 21 lignes
--> titre : 88 lignes
--> article : 1619 lignes

@+

[moimp]

Bonjour Artemus24,

IMPORTANT: Pour me simplifier la vie, j'ai utilisé différentes fonctionnalités de phpMyAdmin.

Cette erreur signifie que tu n'as pas configuré ton serveur mysql pour utiliser l'outil "LOAD DATA INFILE".

J'avais compris mais ne sachant pas comment configurer le serveur j'ai utilisé l'outil d'importation de phpMyAdmin.

J'ai cru comprendre que ton but était de charger le fichier "int_courts_naf_rev_2.xls" et de dispatcher son contenu dans différentes tables.

C'est parfaitement exact. Il manque encore un niveau avec un découpage de sous-article. Je devrais me débrouiller sur la base de tes exemples.

De quelle table parles-tu ?

Du fichier "int_courts_naf_rev_2.xls"

A l'exécution de mon script mysql, chaque select produit un résultat qui est trop volumineux pour entrer dans un message de ce sujet.

OK

Le nettoyage final pour faire quoi ?

Il s'agit de toutes les éléments devenus inutiles que tu as toi-même noté comme "nettoyage final" dans les commentaires de ton code.

Cette question sous-entend que tu ne connais pas bien la syntaxe de l'update.
Dans cet update, la première table est celle qui sera mise à jour, tandis que la seconde table est une jointure.

C'est sûr, j'ai du mal avec les jointures dans les mises à jour (update) et les insertions (insert) alors que je les utilise facilement en lecture (select).

Ce n'est pas un oubli car le contenu de la colonne fr_text de la table "article" est identique au contenu de la colonne "fr_text" de la table "titre".

Là, il me semble que tu fais une erreur car il manque un niveau sous-article.

Pour la suite, je me suis débrouillé, notamment en utilisant ton exemple pour les mises à jour de table avec update.
En tout cas merci, parce que tu m'as appris beaucoup.

[Artemus24]

Salut moimp.

J'avais compris mais ne sachant pas comment configurer le serveur j'ai utilisé l'outil d'importation de phpMyAdmin.

Le mieux est de configurer le serveur car même si tu peux importer un quelconque fichier, cela reste de la manipulation.
Le but est de faire un script que tu lances à la demande, sans trop te poser des questions.

Il manque encore un niveau avec un découpage de sous-article. Je devrais me débrouiller sur la base de tes exemples.

Comme je ne suis pas dans ton projet, il m'est difficile de savoir ce dont tu as réellement besoin.
En ce qui me concerne, je ne vois que trois niveaux, que j'ai nommé "section", "titre" et "article".
Le quatrième niveau, je ne vois pas trop à quoi il peux correspondre.

Il s'agit de toutes les éléments devenus inutiles que tu as toi-même noté comme "nettoyage final" dans les commentaires de ton code.

Dans mon script, j'ai fait en sorte d'utiliser le maximum d'informations pour créer les liens au travers des clefs étrangères.
Le but est de recréer un organisation de type relationnel, ce que j'ai fait.

A la fin du script, il est inutile de conserver la numérotation des lignes qui n'a servi que pour attendre le but que l'on sait donné.

C'est sûr, j'ai du mal avec les jointures dans les mises à jour (update) et les insertions (insert) alors que je les utilise facilement en lecture (select).

Dans le cas de l'update ou de l'insert, pour produire une jointure, il n'y a pas que cette syntaxe.
Je sais, tu n'es pas la seule personne à ne pas connaitre cette syntaxe.

Là, il me semble que tu fais une erreur car il manque un niveau sous-article.

Il est fort possible que j'ai fait des oublies.
Je me suis penché sur la faisabilité de l'organisation relationnel et non sur le respect des différents niveaux de ton fichier excel.

Pour la suite, je me suis débrouillé, notamment en utilisant ton exemple pour les mises à jour de table avec update.

Ton problème est résolu !

En tout cas merci, parce que tu m'as appris beaucoup.

Pas de quoi. Bonne continuation.

@+