SELECT avec 2 Tables

**FannyMae** · 25/05/2011, 23h48

Bonsoir,

je souhaite une petite aide ou du moins une piste pour mon problème.
je n'arrive plus à réfléchir correctement, je bloque.

J'ai 2 tables

table 1 : une table de mots clés - mkeyoa

id nom
1 autofoncage
2 autoripage
3 berlinoise
4 grutage
5 micropieu
6 hydrocampe
7 ripage
...

table 2 : une table de sites - siteoa dans laquelle des ouvrages sont listés et pour lesquels au maximum 5 mots-clés permettent de les caractériser dans leur méthode de construction, on peut donc avoir parfois juste 3 mots-clés.

id nom key1 key2 key3 key4 key5

1 Pont Cheran 2 3 0 0 0
2 Viaduc Hautille 5 2 1 7 0
3 Viaduc Biollay 5 6 4 0 0
4 Viaduc Fonds 5 3 0 0 0
5 Viaduc Plantes 5 7 1 0 0
6 Viaduc Enfer 1 6 3 2 4
7 Tunnel Chava 2 3 4 0 0
8 Viaduc Chava 5 7 1 4 0

dans cette table 2, les mots clés (key1, ..., key5) sont remplacés
par les ID de la table 1, qui pour l'instant comportent 7 mots clés mais il y en aura à terme entre 40 et 50. Donc cela me semblait plus pratique d'avoir les ID comme repère...

mon problème :

je veux connaître les ouvrages faits avec "ripage" - (ID 7)
là, c'est bon, mon SELECT est simple, j'en trouve 3
je les affiche.
je transforme ensuite les résultats (ici les noms des sites) en url.
je voudrais qu'en cliquant sur un des sites s'affichent tous les mots-clés qui le caractérisent, en plus du "ripage".
pour l'instant, j'arrive à afficher la liste des ID (de key1 à key5), même les nulles.

Code php :

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
$rq =  SELECT id,nom,key1,key2,key3,key4,key5 FROM siteoa WHERE id = '".$_GET['id']."' ";
 
$result = mysql_query ($rq) or die ("Requete impossible.");
 
echo "<ul>";
 
while ($ligne = mysql_fetch_array($result))
 
	{
 
	echo $ligne[1]."<br /><br />";
 
	echo "<li type=disc>Mots : ".$ligne[2]." ".$ligne[3]." ".$ligne[4]." ".$ligne[5]." ".$ligne[6]."</li>";
 
	} 
 
echo "</ul><br />";

je n'ai pas trouvé comment faire afficher les 5 mot-clés, pas leur ID et donc à faire référence aux infos stockés dans la table 1... :-(

je n'arrive pas à imaginer ma requête...

est-ce que les JOINTURES sont la solution ?

http://mysql.developpez.com/faq/?page=SYNTAXE_JOINTURES

Merci d'avance.

**ced** · 26/05/2011, 09h51

Bonjour,

Il faut faire une jointure sur chaque clé, et des jointures externes pour traiter les id à 0 :

Code :

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

1
2
3
4
5
6
7
8
SELECT s.id, s.nom, k1.nom AS key1, k2.nom AS key2, k3.nom AS key3, k4.nom AS key4, k5.nom AS key5
FROM siteoa s
LEFT JOIN mkeyoa k1 ON s.key1 = k1.id
LEFT JOIN mkeyoa k2 ON s.key2 = k2.id
LEFT JOIN mkeyoa k3 ON s.key3 = k3.id
LEFT JOIN mkeyoa k4 ON s.key4 = k4.id
LEFT JOIN mkeyoa k5 ON s.key5 = k5.id
WHERE s.id = ...

ced

**CinePhil** · 26/05/2011, 11h04

Il faudrait surtout modifier la structure de la BDD qui a été mal conçue !

Un site pouvant avoir plusieurs mots clés et un mot clé pouvant s'appliquer à plusieurs sites, il faudrait une table associative :
mkeyoa (id, nom)
siteoa (id, nom
site_mkey (id_site, id_mkey)

**FannyMae** · 26/05/2011, 17h37

très intéressant.
grand merci.

je n'ai plus qu'à travailler ou à revoir intégralement ma base.

j'ai essayé d'abord la solution de ced :

je ne comprends pas la syntaxe de la requête que tu m'as fournie comme piste :

SELECT s.id, s.nom, k1.nom AS key1, k2.nom AS key2, k3.nom AS key3, k4.nom AS key4, k5.nom AS key5
FROM siteoa s
...

je n'ai pas vu dans les tutoriaux ce moyen de "renommer" une table dans une requête.
FROM siteoa s ==> tu renommes siteoa en s ???
ce qui te permettrait de sélectionner s.id s.nom etc...

est-ce que c'est çà ?

autre point : comment tu peux faire k1.nom ?
je n'ai pas de table nom...
et je ne pense pas qu'il s'agit de la table mkeyoa...

je n'ai rien compris en fait.
je crois que je suis bonne pour un aspro 1000

j'ai essayé plusieurs requêtes en essayant de comprendre ta proposition mais j'arrive à un résultat que je n'avais pas eu jusqu'ici :

http://5090.net/c et entre par exemple ripage ou hydrocampe ...

merci de tes lumières si possible.

**ericd69** · 27/05/2011, 09h15

oui c'est le surnommage qui permet d'avoir un alias...

par contre comme dit cinephil ta bd est très mal conçue

là il te faudrait une table de liaison:
relation(id,idpont,idcle)

qui relit alors tes tables:
ponts(id,nom,cheran) et cles(id,nom)

et là tu peux mettre un nombre variable de clés pour chaque pont et rechercher avec une pauvre jointure externe gauche tous les ponts avec telle ou telle clé ou l'inverse

en gros tout ce qui est fixe dans tes tables reste dedans, tout ce qui a un nombre variable d'apparitions est remplacer par la table de liaison...

de manière plus formelle c'est le principe de normalisation des tables qui fait en sorte de scinder une grosse table en une table de référence et des tables dépendantes (données n'apparaissant qu'une seul fois comme l'age d'une personne) ou en passant par une table de liaison (données pouvant être multiple, comme l'adresse d'une personne qui peut en avoir plusieurs par exemple)

refais déjà tes tables correctement et ça te simplifiera la vie énormément

**FannyMae** · 01/06/2011, 13h43

Bonjour à tous.
J'ai donc suivi vos conseils, j'ai refondu les tables.
Par contre, ne possédant pas (de très loin) toutes les subtilités de MySQL,
je n'ai pas cette vision sur "ce qui serait possible" de faire en "mieux".
Ce qui explique pourquoi mes tables sont *très* mal conçues...

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
 
-- --------------------------------------------------------
-- Table LIGNE
--
-- id     : le numero UNIQUE qui va servir pour identifier la ligne sur laquelle se trouvent des sites particuliers. 
-- num    : la numerotation specifique (normalisee) de ces lignes - elle se fait sur 6 caracteres avec 1 espace de lisibilite.
-- region : ALP - La table comprend les lignes de la region ALP que l'on fixe comme valeur par defaut.
-- siege  : CH  - Le siege est a CHAMBERY - CH que l'on fixe comme valeur par defaut.
-- desc   : la ligne est decrite "en clair" - plus comprehensible que la numerotation normalisee.
--
 
CREATE TABLE `ligne` (
`id` tinyint(4) NOT NULL auto_increment,
`num` varchar(9) NOT NULL,
`region` varchar(50) NOT NULL,
`siege` varchar(10) NOT NULL,
`desc` varchar(255) NOT NULL,
PRIMARY KEY (`id`)
);
 
 
INSERT INTO `ligne` VALUES
 
(1,'895 000','ALP','CH','La Roche sur Foron - St Gervais les Bains Le Fayet'),
(2,'897 000','ALP','CH','Aix les Bains Le Revard - Annemasse'),
(3,'903 000','ALP','CH','Saint Andre le Gaz - Chambery'),
(4,'900 000','ALP','CH','Culoz - Modane'),
(5,'909 000','ALP','CH','Grenoble - Montmelian');
 
 
 
-- ---------------------------------------------------------------------------------
-- Table SITE
--
-- id        : Le numero UNIQUE qui va servir pour identifier chaque site particulier. 
-- nom       : Le nom complet du site, pas de raccourci.
-- idLigne   : La ligne sur laquelle se trouve le site - reprise dans la table 'ligne'.
-- pk        : Le point kilometrique du site sous la forme xxx+yyy (normalisee).
-- dimoa     : Si le site est un ouvrage, les dimensions en metre de l'ouvrage.
--
 
CREATE TABLE 'site' (
'id' tinyint(4) NOT NULL AUTO_INCREMENT,
'nom' varchar(255) NOT NULL,
'idLigne' tinyint(4) DEFAULT NULL,
'pk' varchar(7) NOT NULL,
'dimoa' varchar(7) DEFAULT NULL,
PRIMARY KEY ('id')
);
 
 
INSERT INTO `site` VALUES
 
(1,  'Aix les Bains Le Revard',                 2, '123+622', ''),
(2,  'Section de separation 1500V - 25kV',      2,   '0+603', ''),
(3,  'Gresy sur Aix',                           2,   '4+265', ''),
(4,  'Albens',                                  2,  '12+130', ''),
(5,  'Bloye',                                   2,  '16+554', ''),
(6,  'Rumilly',                                 2,  '20+941', ''),
(7,  'Pont du Cheran',                          2,  '21+634', '69 m'),
(8,  'Marcellaz Hauteville',                    2,  '27+176', ''),
(9,  'Viaduc d Hauteville (Fier)',              2,  '27+927', '78 m'),
(10, 'Viaduc du Biollay (Fier)',                2,  '28+996', '69 m'),
(11, 'Viaduc de Fonds (Fier)',                  2,  '29+388', '87 m'),
(12, 'Viaduc des Plantes (Fier)',               2,  '29+704', '87 m'),
(13, 'Viaduc de l Affreux Enfer',               2,  '31+053', '123 m'),
(14, 'Tunnel de Chavaroche',                    2,  '31+712', '159 m'),
(15, 'Viaduc de Chavaroche (Fier)',             2,  '31+750', '87 m'),
(16, 'Pont sur le Fier',                        2,  '31+736', '8 m'),
(17, 'Tunnel de Pont Verre',                    2,  '32+256', '96 m'),
(18, 'Pont de Montrottier (Fier)',              2,  '32+752', '8 m'),
(19, 'Lovagny Gorges du Fier',                  2,  '33+221', ''),
(20, 'Tunnel de Brassilly',                     2,  '35+293', '1163 m');
 
 
 
-- --------------------------------------------------------
-- Table MOT CLE OA (mkeyoa)
--
-- id   : Le numero UNIQUE qui va servir pour identifier le mot cle donne par OA.
-- nom  : Le nom en clair - qui va caracterise un ou plusieurs sites dans la table site.
-- desc : Description du mot cle (facultatif), utile si le nom est un acronyme.
--
 
CREATE TABLE `mkeyoa` (
  `id` tinyint(4) NOT NULL AUTO_INCREMENT,
  `nom` varchar(50) NOT NULL,
  `desc` varchar(255) NOT NULL,
  PRIMARY KEY (`id`)
);
 
 
INSERT INTO `mkeyoa` VALUES
 
(1,  'autofoncage', 'methode de realisation d un ouvrage souterrain de faible longueur'),
(2,  'autorippage', ''),
(3,  'berlinoise',  ''),
(4,  'grutage',     ''),
(5,  'hydrocampe',  'appareil ferroviaire servant au transport d ouvrages de franchissements prefabriques'),
(6,  'injection',   ''),
(7,  'lancage',     ''),
(8,  'micropieu',   'dispose en groupes pour fonder ou consolider un ouvrage'),
(9,  'palplanche',  'permet de constituer un mur de soutenement ou un ecran impermeable'),
(10, 'poitrail',    ''),
(11, 'poussage',    ''),
(12, 'rippage',     '');
 
 
 
-- --------------------------------------------------------
-- Table MOT CLE TL (mkeytl)
--
-- id   : Le numero UNIQUE qui va servir pour identifier le mot cle donne par OA.
-- nom  : Le nom en clair - qui va caracterise un ou plusieurs sites dans la table site.
-- desc : Description du mot cle (facultatif), utile si le nom est un acronyme.
--
 
CREATE TABLE `mkeytl` (
  `id` tinyint(4) NOT NULL AUTO_INCREMENT,
  `nom` varchar(50) NOT NULL,
  `desc` varchar(255) NOT NULL,
  PRIMARY KEY (`id`)
);
 
 
INSERT INTO `mkeytl` VALUES
 
(1,  'CEMGC',   ''),
(2,  'CEMPC',   ''),
(3,  'CTFC',    ''),
(4,  'CTFU',    ''),
(5,  'DRPS',    'dispositif de renvoi des postes secondaires'),
(6,  'IOE',     'interface opto electronique'),
(7,  'PTE',     ''),
(8,  'TA',      ''),
(9,  'TFC',     ''),
(10, 'SELTA',   ''),
(11, 'SIEMA',   ''),
(12, 'SLITEC',  '');

J'ai toujours les mêmes problèmes :

1. Comment lier la table SITE aux tables MOT CLE (mkeyoa, mkeytl, ...) ?
2. Comment afficher les mots clés qui caractérisent le mieux un site ?

Avec la contrainte suivante, qu'avait soulevé CinePhil :

Un site pouvant avoir plusieurs mots clés et un mot clé pouvant s'appliquer à plusieurs sites, il faudrait une table associative

Et du coup, je précise une chose supplémentaire.
J'ai plusieurs tables de mots-clés car plusieurs domaines de compétence existent : Ouvrage d' Art, Télécoms, etc.
Je ne sais pas si je fais une table de mots clés uniques ou si je les segmente (ce que j'ai fait).

Merci de votre aide.

**ericd69** · 01/06/2011, 14h58

par définition, des table avec la même structure et sémantique = 1 seule table

si tu veux séparé les valeurs des différentes tables rajoute une colonne qui contiendra un index avec une valeur par ancienne table

-> moins de fichiers ouverts pour analysé les tables (3 fichiers minimum par table pour info)
-> ne pas confondre recherche par un pauvre humain dans une table et ce que sait faire un ordi (un humain adore les chaine de caractère plus que des recherche sur indexes numériques, l'ordi c'est totalement l'inverse)
-> quand tu as des valeurs en chaine de caractères répétitive, faut penser à les remplacer par un index vers une table contenant ces valeurs -> plus compact en terme de taille fichier et souvent une jointure très rapide à faire
-> 1 caractère utf8 = 1 à 5 octets pour mémoire, si tu as un type varchar c'est la taille voulue qui est allouée, si c'est du type char c'est le max de caractère x 5)

pour lier de manière ouverte des entrées de 2 tables, comme je te l'ai déjà dit, on utilise une table de liaison:

id identifiant de la relation (clé primaire)
idtablea clé étrangère vers la clé primaire de la table a
idtableb clé étrangère vers la clé primaire de la table b

du coup tu peux lier autan de fois que tu veux une entrée de la table a à l'une de celle de la table b

**CinePhil** · 01/06/2011, 15h59

Pour apprendre à modéliser correctement une BDD, vois le cours de SQLPro et les livres de Michel Diviné.

1) Table "ligne"
a) Tes lignes vont d'une ville à une autre. Tu devrais donc avoir une table des villes et l'associer deux fois à la table des lignes :
MCD :
ligne -1,1----partir----0,n- ville
|--------1,1----arriver----0,n---|

Tables :
ville_vil (vil_id, vil_nom, vil_abreviation...)
ligne_lgn (lgn_id, lgn_id_ville_depart, lgn_id_ville_arrivee...)

Dans l'absolu, ont pourrait même faire une identification relative !
MCD :
ligne -(1,1)----partir----0,n- ville
|--------(1,1)----arriver----0,n---|

Tables :
ville_vil (vil_id, vil_nom, vil_abreviation...)
ligne_lgn (lgn_id_ville_depart, lgn_id_ville_arrivee...)

Mais restons simples pour la suite, sans oublier toutefois de placer une contrainte d'unicité (index de type UNIQUE chez MySQL) sur le couple {lgn_id_ville_depart, lgn_id_ville_arrivee} de manière à ne pas créer deux fois la même ligne.

b) Les colonnes "region" et "siege" devraient être externalisées dans des tables de référence.

MCD :
ligne -1,1----dependre----0,n- region
|--------1,1----siéger----0,n- ville

Si une région n'a qu'un siège, le MCD devient celui-ci :
ligne -1,1----dependre----0,n- region -1,1----sieger----0,n- ville

Tables :
region_rgn (rgn_id, rgn_id_ville_siege, rgn_nom, rgn_abreviation)
ligne_lgn (lgn_idlgn_id_ville_depart, lgn_id_ville_arrivee, lgn_id_region...)

2) Table site
Puisque certains sites sont des ouvrages, avec un attribut supplémentaire, mais pas tous, tu devrais faire un héritage.

MCD :
ouvrage -(1,1)----Être----0,1- site

Tables :
site_ste (ste_id, ste_id_ligne, ste_nom, ste_point_kilometrique)
ouvrage_ovg (ovg_id, ovg_dimension)

3) Table mkeyoa
Puisque tu dis que la description est facultative, ne rends pas la colonne NOT NULL !

Au contraire, mets-là à DEFAULT NULL !

4) Tables mkey
Comme l'a dit Éric, inutile de faire plusieurs tables de mots-clés, une seule suffit.

5) Association entre les mots-clés et les sites
Comme dit Éric, il te faut une table associative, mais contrairement à lui, je ne lui donne pas d'identifiant propre. L'identifiant d'une table associative est composé des identifiants des tables entrant en jeu dans l'association.

MCD :
site -0,n----Assoccier----0,n- mot_cle

Tables :
site_ste (ste_id, ste_id_ligne, ste_nom, ste_point_kilometrique)
mot_cle_mk (mk_id, mk_mot, mk_description)
ste_associer_mk_sam (sam_id_site, sam_id_mot_cle)

Bon courage pour la suite !

**ericd69** · 01/06/2011, 16h24

oui cinéphil a raison:

l'index supplémentaire dans la table des mot clé est uniquement à mettre dans un but de filtrage par thématique et/ou en cas de double de mots clé entre thématiques

mais c'est plus utile pour la génération de l'interface de saisie ...

en gros, dans ta bd tout n'est pas forcément utilisé à tout moment !

le gros du travail du sgbd doit être de brasser des indexes numériques pour la recherche des éléments correspondants à ce que tu veux (et le moins possible de chercher dans des champs char,varchar,text par exemple)...

les données autres sont rapatriées pour la restitution finale dans les ihm

si tu suis ces principes, tu auras toujours quelque chose d'efficace normalement

j'essaye de te le dire d'une manière moins formelle que cinephil mais on dit la même chose

**FannyMae** · 13/06/2011, 21h13

mon code n'est peut-être pas optimisé mais je suis arrivé à mes fins avec la toute première info :

mkeyoa (id, nom)
siteoa (id, nom
site_mkey (id_site, id_mkey)

j'ai cherché, j'ai trouvé.
merci à vous pour vos conseils, tutoriaux, etc.

SELECT avec 2 Tables

Requêtes MySQL

Discussions similaires

Partager

Partager