Discussion :

[chile]

Bonjour,

Je suis coincé sur la mise en forme d'une requete.
J'ai une liste de programmes touristiques, et je souhaite mettre en place un filtre par villes visitées dans le programme.

J'ai commencé par la solution: 1 colonne dans laquelle se trouve les villes. Mon probleme était que je n'ai pas trouvé comment rentrer plusieurs villes choisies dans une mÊme cellule, avec un systeme de checkbox

J'ai donc opté pour une autre solution: un 2e tableau lié par le code du circuit, et une colonne par ville, avec 1 si ville visitée et 0 si non. Mais la je coince pour l'affichage du résultat, car si je coche par exemple Paris, j'ai comme résultat seulement les programme qui inclus seulement la visite de Paris et pas les programme qui inclus au moins la visite de Paris.

Voici le select de ma 2e solution.

Code :

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

1
2
3
4
5
6
7
8
9
10
mysqli_select_db($connect, $database_connect);
$query_circuit = "SELECT devis_itineraires_versions.code_circuit, devis_itineraires_versions.version, devis_itineraires_lieux.code_circuit, devis_itineraires_lieux.version, devis_itineraires_lieux.paris, devis_itineraires_lieux.marseille, devis_itineraires_lieux.lyon, devis_itineraires_lieuxbordeaux
FROM devis_itineraires_versions
INNER JOIN devis_itineraires_lieux 
ON devis_itineraires_versions.code_circuit = devis_itineraires_lieux.code_circuit AND devis_itineraires_versions.version = devis_itineraires_lieux.version
 WHERE paris LIKE '$paris' AND marseille LIKE '$marseille' AND lyon LIKE '$lyon' AND bordeaux LIKE '$bordeaux' 
  ORDER BY date DESC";
$circuit = mysqli_query($connect, $query_circuit) or die(mysqli_error());
//$row_base_versions = mysqli_fetch_assoc($base_versions);
$totalRows_circuit = mysqli_num_rows($circuit);

Je ne sais pas sur quelle base partir.

Pourriez vous m'aider svp.

[mathieu]

je pense qu'il faudrait revoir la structure de la base de données.
ce qui se fait d'habitude dans ce cas, ce sont les tables suivantes :

itineraire :

• id_itineraire : clé primaire
• code_itineraire
• etc.

ville

• id_ville : clé primaire
• nom_ville
• etc.

et ensuite une table qui va faire le lien entre ces 2. la clé primaire est composée des 2 colonnes :

itineraire_ville

• id_itineraire
• id_ville

[chile]

Bonjour,

Merci pour cette réponse.

Mais je retrouve le même soucis.

J'ai un tableau composé des circuits.
id_itineraire : clé primaire
code_itineraire : exemple martin2023
etc.

J'ai ajouter une table composé de colonne correspondant aux circuits et villes incluses,
id_villes : clé primaire
code_itineraire : exemple martin2023
bordeaux: 1 ou 0
marseille: 1 ou 0
paris : 1 ou 0
etc.

Lorsque j'execute ma requete, avec un systeme de case à cocher, pour filtrer les circuits qui inclus par exemple Marseille, avec un marseille(égal à 1) LIKE '$marseille'(case à cocher qui donne 1 a la variable marseille), cela fonctionne seulement si ce circuit n'inclus pas bordeaux et paris. Car si ce circuit passe aussi par Bordeaux, et que la case Bordeaux n'est pas cochée, le circuit ne s'affiche pas. Je voudrais que tous les circuits passant AU MOINS par Marseille s'affiche.

En plus clair, j'ai un circuit qui passe par Marseille et Bordeaux. Je voudrais que lorsque je coche Marseille et pas Bordeaux, le circuit s'affiche. Je n'ai pas trouvé la solution avec les opérateur.

Idéalement je voudrais inclure dans ma requete WHERE seulement les variables cochées=1, car avec un AND les autres requetes=0 me faussent le résultat.

Merci pour votre aide.

[CosmoKnacki]

Le problème c'est que tu n'as pas suivi et/ou mal compris la voie que te montre mathieu. Il te propose 3 tables: itineraire, ville et itineraire_ville qui te permet de faire le lien entre les deux. Toi tu reviens à la charge avec 2 tables dont l'une doit en plus se coltiner une nouvelle colonne par ville -> ça ne marchera pas (ou au mieux ça marchera mal), une base de données relationnelle ne s'utilise pas comme un tableur.

Repart sur l'architecture de mathieu et renseigne toi (-> tutoriels) sur les jointures et les clefs étrangères, sinon tu ne t'en sortiras pas.

Un exemple de requête avec cette architecture pour obtenir tous les itinéraires passant par Bordeaux serait:

Code SQL :

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

1
2
3
4
5
select iti.code_itineraire
from itineraire as iti
join itineraire_ville as itv on iti.id_itineraire = itv.id_itineraire
join ville as vil on itv.id_ville = vil.id_ville
where vil.nom_ville = "Bordeaux"

N'hésite pas à revenir si ça coince.

[chile]

Effectivement je n'ai pas suivi la voie de Mathieu car je ne suis pas sur d'avoir saisi.

Si je comprends, la 1ere table
itineraire:

• id primaire
• code circuit
• version

exemple
1 / martin2023 / 4


ville

• id_ville
• ville

exemple
1 / Bordeaux
2 / Paris
3 / Marseille


itineraire_ville

• id_itineraire
• id_ville1
• id_ville2
• id_ville3

exemple
1 / martin2023 / 1 / 3
2 / dupont2024 / 3 / 2


Est-ce que c'est l'architecture correcte ?

Mais je reste avec le doute qu'avec cette solution je puisse faire un filtre avec plusieurs villes, et que je puisse afficher de cette manière:
Je coche bordeaux, j'ai tous les circuits qui passent au moins par Bordeaux
Si je coche Bordeaux et Marseille, j'ai tous les circuits qui passent au moins par Bordeaux et Marseille (mais si ils passent pas Paris, et que je n'ai pas coché Paris, le circuit s'affiche quand même). C'est le soucis que j'ai actuellement.

[CosmoKnacki]

Est-ce que c'est l'architecture correcte ?

Non, ce n'est pas correct pour la dernière table, celle qui fait la liaison entre les deux premières, juste deux colonnes sont nécessaires:

itineraire_ville:

• id_itineraire: clef étrangère (foreign key) provenant de la table itineraire
• id_ville: clef étrangère (foreign key) provenant de la table ville

Ce qui donne comme enregistrements:

• 1 / 25
• 1 / 14
• 1 / 28
• 2 / 14
• 2 / 13
• 2 / 46

Avec, disons que 25 est l'id de Paris, 14 celle de Bordeaux, 28 celle de Toulouse, 13 celle de Perpignan et 46 celle de Collioure (informations présentes dans la table ville). On répète dans cette table l'id de l'itinéraire (1 et 2 dans cet exemple) autant de fois qu'il y a de villes pour un même itinéraire, elle est faite pour ça, ce n'est pas un problème. Par contre, on ne met pas le code martin2023 parce qu'il est déjà présent dans la table itinéraire et qu'on peut le retrouver par une simple jointure via l'id de l'itinéraire. Le but du jeu est de ne jamais dupliquer l'information, les ids quant à eux ne doivent contenir aucune information, c'est pour cela qu'on opte pour des raisons de performance pour des clefs numériques (voir tutoriel sur les formes normales). Une colonne pourrait être ajoutée à cette table si par exemple on voulait se soucier de l'ordre dans lequel ces villes seraient visitées.

(Tout ce qui est souligné fait partie du jargon des systèmes de gestion de base de données relationnelles: sgbdr).

[escartefigue]

Bonjour

je pense qu'il faudrait revoir la structure de la base de données.
ce qui se fait d'habitude dans ce cas, ce sont les tables suivantes :

itineraire :

• id_itineraire : clé primaire
• code_itineraire
• etc.

ville

• id_ville : clé primaire
• nom_ville
• etc.

et ensuite une table qui va faire le lien entre ces 2. la clé primaire est composée des 2 colonnes :

itineraire_ville

• id_itineraire
• id_ville

Le modèle de données ci-dessus n'est valide que si pour un itinéraire, on ne peut passer qu'une seule fois par une même ville, ce qui est très restrictif !

La bonne modélisation est la suivante, au niveau conceptuel :

[ITINERAIRE] 1, n --- (comporter) --- 1,1(R) [ETAPE] 1,1 --- (situer) --- 0,n [VILLE]

Ce qui donne les tables suivantes (PK soulignées, FK suffixées #) :

ITINERAIRE (IT_ident, IT_nom...)
VILLE (VI_ident, VI_insee, VI_nom...)
ETAPE(IT_ident#, ET_num, VI_ident#)

Ainsi, si un itinéraire fait un circuit dans une ville (fréquent pour les itinéraires touristiques dans les grandes villes), tout va bien

[CosmoKnacki]

Oui, effectivement si des itinéraires passent plusieurs fois par une même ville, dans ce cas il suffit d'ajouter une colonne avec le numéro de l'étape à la table itineraire_ville.

[escartefigue]

Non, car dans la structure proposée plus haut par mathieu, la PK de la table associative itineraire_ville était id_itineraire + id_ville

Donc si on rajoute la colonne étape

• en dehors de cette PK, alors le problème que j'ai mentionné subsiste : la PK étant par définition unique, on ne peut avoir qu'une étape dans une même ville d'un même itinéraire.
  
• dans la PK (qui devient du coup id_itineraire + id_ville + numéro_etape) alors la ville est inutile dans la PK puisque c'est bien le couple (itinéraire, ville) qui doit être unique.
  Or, je rappelle que par définition, une PK doit être irréductible. Donc le trio (id_itineraire, id_ville, numéro_etape) n'est pas une PK, mais une surclef.
  

Pour rappel, une PK doit avoir les caractéristiques suivantes :

• unique
• not null
• irreductible

Et, ce n'est pas obligatoire, mais très fortement recommandé, elle doit en outre être :

• stable (à cause de la propagation de sa valeur au travers des clefs étrangères)
• concise (pour favoriser les performances)
• insensible à la collation (pour éviter les erreurs de comparaison)

Ces 3 derniers points militent en faveur des colonnes techniques de type int (small, integer ou bigint) et dont la valeur est calculée par le SGBD (identity, serial, auto_increment... selon le SGBD)

[CosmoKnacki]

C'est vrai que ça change la clef primaire et que du coup le nom de table itineraire_ville n'est plus légitime et peut être remplacé par etape.

(Après on s'emballe peut-être un peu vite avec le terme "itinéraire" et qu'il s'agit d'un simple "tour" où seules les villes sont mentionnées sans plus d'informations sur l'ordre de visite ou le nombre de fois qu'on y passe. Dans ce cas la modélisation de mathieu convient très bien.)

[escartefigue]

Plus que le nom de la table qui n'est pas très important, ce genre d'erreurs est caractéristique d'une réflexion menée sur la structure des tables alors que la bonne démarche est de réaliser le modèle conceptuel des données en fonction des règles de gestion.

Si les règles de gestion stipulent qu'un itinéraire est composé de plusieurs étapes et qu'on sait que plusieurs étapes peuvent être dans le même lieu (cas typique, un itinéraire de type circuit dans lequel l'étape de début et de fin sont le même lieu), alors le MCD en tiendra compte, ce faisant, la script généré pour créer les tables sera correct et autorisera de réaliser plusieurs étapes dans une même ville. On voit bien trop souvent des modèles de donnés créés directement sans passer par l'étape conceptuelle et qui comportent ce genre d'imperfection.

Dans le pire des cas, l'erreur aurait pu n'être découverte que tardivement, après la mise en production, lorsqu'on aurait rencontré un premier itinéraire de type circuit, et là, les conséquence sont lourdes, revoir un modèle de données impacte non seulement les données, mais aussi les traitements.

La plupart des logiciels de modélisation, y compris les gratuits (dont l'excellent looping), comportent des contrôles de cohérence du modèle et génèrent automatiquement le script de création des tables et index en fonction du choix du SGBD.
C'est pourquoi j'ai du mal à comprendre pourquoi on trouve encore des personnes qui créent leurs tables à la main, avec pour conséquence des incohérences comme celle qu'on vient de voir ici, alors qu'on pourrait les éviter très simplement, tout en se simplifiant la vie pour la maintenance avec un logiciel de modélisation.

[chile]

Ce n'est pas un soucis si l'itinéraire repasse plusieurs fois par un même ville.
Je pense que mes explications rendent plus compliqué mon besoin qui est plutot simple.

Je vais essayer de reformuler plus clairement.

J'ai une base de circuits touristiques. Je souhaite faire un filtre pour trouver les circuits selon les lieux inclus dans chacun avec un systeme de cases à cocher liées et de villes.

Par exemple je souhaite lister les circuits qui comprennent Bordeaux, Nantes et Rennes.
Jusqu'a maintenant avec ma requête, j'arrive à lister les circuits qui comprennent ces trois villes. Mais si un circuit comprends ces trois villes + Brest, il ne s'affichera pas. Et justement je souhaiterai qu'il s'affiche aussi.

Voici mon architecture de 2 tableaux.

tab itineraire:
id primaire
code circuit


exemple
1 / martin2023

------------------
ce deuxième tableau regroupe tous les codes circuits suivi de colonnes des villes. Selon si le circuit comprend ou non la ville, il est noté 1 ou 0.

tab itineraire_ville
id_itineraire
code_circuit
bordeaux
brest
marseille
nantes
paris
toulouse


exemple
1 / martin2023 / 1 / 1 /0 / 0 / 0 / 1 (circuit passant par brest, marseille et toulouse)
2 / dupont2024 / 0 / 1 / 0 / 1 / 0 / 1

Dans ma requête j'ai fait un INNER JOIN des deux table avec code_circuit et un WHERE brest LIKE '$brest' AND marseille LIKE '$marseille' AND nantes LIKE '$nantes' AND paris LIKE '$paris' AND toulouse LIKE '$toulouse'

J'ai un systeme de case a cocher pour le filtre qui renvoi les valeur 1 ou 0 selon si la villes cochée ou non.

Merci pour votre aide

[escartefigue]

ce deuxième tableau regroupe tous les codes circuits suivi de colonnes des villes. Selon si le circuit comprend ou non la ville, il est noté 1 ou 0.

tab itineraire_ville
id_itineraire
code_circuit
bordeaux
brest
marseille
nantes
paris
toulouse

Ce type de modélisation est à proscrire : mettre en colonnes les différentes villes complique inutilement les évolutions et compromet les jointures.

• Le jour où de nouvelles destinations sont à gérer, il faut modifier la table et les objets associés (vues, triggers, procédures...)
• L'indexation sera hyper gourmande puisqu'il faut autant d'index que de colonnes destination
• Dans chaque circuit enregistré dans la table, la plupart des destinations sont inutiles, on encombre donc inutilement l'espace disque
• Le principe d'un circuit c'est non seulement de connaitre les étapes, mais aussi dans quel ordre elles sont et, souvent, à quelle date voire heure elles sont prévues

Bref, rien ne va, il faut utiliser la modélisation que j'ai communiquée plus haut, elle permettra de satisfaire votre besoin, sera indolore le jour où de nouvelles destinations seront à gérer, les performances seront accrues et les requêtes seront bien plus simples.

[chile]

Bonjour et merci pour ton retour.

C'est un peu technique pour moi, je ne comprends qu'a moitié. Surtout sur la structure des tables.
Peux tu corriger et/ou completer les exemples stp.

ITINERAIRE (IT_ident, IT_nom...)
1 / martin2023
2 / lola2023


VILLE (VI_ident, VI_insee, VI_nom...)
1 / ? /bordeaux / brest / marseille...

Quelle est la 2e colonne avec VI_insee?


ETAPE(IT_ident#, ET_num, VI_ident#)
1 / martin2023

[escartefigue]

Les communes françaises sont identifiées par l'INSEE selon un code alphanumérique (mais ne contenant que des chiffres) de longueur 5
J'avais donc mis cet attribut VI_insee à titre d'exemple d'attribut possible dans ma proposition de modèle de données, mais à ne retenir que si vous en avez besoin.

La liste des valeurs des codes INSEE des communes est disponible gratuitement sur la toile.
Voir ICI par exemple.

Dans la table des villes il n'y aura surtout pas ce que vous proposez

1 / ? /bordeaux / brest / marseille...

Il y aura une ligne (et donc un identifiant) par ville comme suit :

1 / 33063 / bordeaux
2 / 29019 / brest
3 / 13055 / marseille
etc


Encore une fois, il ne faut jamais stocker de liste de valeurs dans une même colonne !

Le jour où vous aurez un nouvel itinéraire passant par Besançon, vous ajouterez une nouvelle ligne :

4 / 25056 / besancon

Ainsi, nul besoin de modifier la structure ni les traitements en cas de nouvelle ville

[chile]

D'accord, merci pour cette précision pour la 2e table.

et pour la 3e table ?
j'imagine que IT_ident est le code circuit, mais ensuite comment se composent les 2 autres colonnes ?

ETAPE(IT_ident#, ET_num, VI_ident#)
1 / martin2023

[Séb.]

Les communes françaises sont identifiées par l'INSEE selon un code alphanumérique

Attention toutefois, il existe différents types de communes et le code INSEE peut être "partagé" entre une commune (type COM) et une commune déléguée (type COMD).
Dans ce fichier on a l'unicité sur { Code INSEE, Type commune } ou les lignes en « Type IN ('COM', 'COMA', 'ARM') » (on ne garde que les communes, les communes associées et les arrondissements de mairie).

Ex.

Type	Code	Nom
COM	01015	ARBOYS EN BUGEY
COMD	01015	ARBIGNIEU

[chile]

D'accord. Mais ça c'est la table des villes. Je bloque sur la 3e table.

La premiere table, on trouve les circuits.

La deuxième table on trouve les lieux (je ne vais utiliser Insee, car je vais retrouver dans cette table des villes étrangères aussi)

Qu'en est il de la 3e table ? je bloque sur celle ci.

[escartefigue]

Avec les préconisations précédentes


Pour ce qui concerne le modèle tabulaire :

ITINERAIRE (IT_ident, IT_nom...)
VILLE (VI_ident, VI_insee, VI_nom...)
ETAPE(IT_ident#, ET_num, VI_ident#)

Et pour ce qui concerne le contenu de la table des villes :

1 / 33063 / bordeaux
2 / 29019 / brest
3 / 13055 / marseille
4 / 25056 / besancon

Soit un itinéraire n°1 faisant une boucle Bordeaux ==> Brest ==> Bordeaux,
nous aurons dans la table ITINERAIRE
1 / Boucle bordeaux-brest-bordeaux par la côte atlantique
et dans la table ETAPE
1 / 1 / 1
1 / 2 / 2
1 / 3 / 1

Et pour l'itinéraire n°2 qui fait Brest ==> Marseille sans escale
nous aurons dans la table ITINERAIRE
2 / Bretagne - Provence
et dans la table ETAPE
2 / 1 / 2
2 / 2 / 3

Grâce à ce modèle, extraire tous les itinéraires passant par bordeaux est l'enfance de l'art :

Code SQL :

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

1
2
3
4
5
6
7
8
9
10
11
12
select ITI.IT_ident  as "n° itinéraire"
     , ITI.IT_nom    as "nom itinéraire"
     , VIL.VI_nom    as "ville"
     , ETP.ET_num    as "numéro d'étape"
from       VILLE       as VIL
inner join ETAPE       as ETP
   on ETP.VI_ident = VIL.VI_ident
inner join ITINERAIRE  as ITI
   on ITI.IT_ident = ETP.IT_ident
where  VIL.VI_nom = 'bordeaux'
order by ITI.IT_ident 
       , ETP.ET_num

[chile]

Bonjour,

Je relance ma demande avec un tableau pour être plus clair.

J'ai besoin de filtre sur les villes incluse dans un circuit.

Par exemple, je veux lister tous les circuits qui comprennent Londres et Paris

Ma liste en fonction de ce tableau est:
martin_2024 et norman_2024

code_circuit	londres	madrid	paris	rome
martin_2024	1	1	1	0
norman_2024	1	0	1	1
marion_2024	1	1	0	0

Je bloque sur la requête.

Merci pour votre aide.