Relier une table avec un système de recherche d'adresse

**cool-man** · 28/09/2010, 01h48

Bonjour,

Je souhaiterai faire une requête, qui m'affiche une liste de lycées ou d'universités avec leurs adresses.

Exemple d'affichage :
Lycée Pasteur
Neuilly-sur-Seine, France

pour ça j'ai du crée quatre tables

trois tables pour l'adresse:

location_country (pour les pays)
location_region (pour les regions)
location_location (pour les localités et autres ...)

La structure des trois tables

Code mySql :

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
 
-- `location_country`
 
CREATE TABLE `location_country` (
  `country_id` smallint(3) unsigned NOT NULL AUTO_INCREMENT,
  `country_en_name` varchar(100) CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL,
  `country_local_name` varchar(100) CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL,
  PRIMARY KEY (`country_id`),
  UNIQUE KEY `country_en_name` (`country_en_name`,`country_local_name`)
)
 
-- `location_region`
 
CREATE TABLE `location_region` (
  `region_id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `region_country_id` smallint(3) unsigned NOT NULL,
  `region_post_code` varchar(15) CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL,
  `region_common_name` varchar(100) CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL,
  `region_local_name` varchar(100) CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL,
  PRIMARY KEY (`region_id`),
  KEY `region_country_id` (`region_country_id`,`region_common_name`,`region_local_name`),
  KEY `region_post_code` (`region_post_code`)
)
 
-- `location_location`
 
CREATE TABLE `location_location` (
  `location_id` bigint(13) unsigned NOT NULL AUTO_INCREMENT,
  `location_country_id` smallint(3) unsigned NOT NULL,
  `location_region_id` int(10) unsigned NOT NULL,
  `location_post_code` varchar(15) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL,
  `location_en_name` varchar(100) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL,
  `location_local_name` varchar(100) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL,
  PRIMARY KEY (`location_id`),
  KEY `location_country_id` (`location_country_id`,`location_region_id`,`location_en_name`,`location_local_name`),
  KEY `location_post_code` (`location_post_code`)
)

Pour faire simple, chaque localisation et lier a une région qui est liera un pays.
'location_location' => 'location_region' => 'location_country'

grâce a ce forum et notamment au membre "sabotage" je suis sortie avec cette requête.

Code sql :

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

1
2
3
4
5
6
7
8
9
10
11
12
13
14
 
$sql = "SELECT * FROM
   location_country
   LEFT JOIN location_region ON region_country_id = country_id
   LEFT JOIN location_location location_region_id = region_id
WHERE 
   location_post_code LIKE '%$location%' 
|| location_common_name LIKE '%$location%'
|| location_local_name LIKE '%$location%'
|| region_post_code LIKE '%$location%'
|| region_common_name LIKE '%$location%'
|| region_local_name LIKE '%$location%'
|| country_en_name LIKE  '%$location%'
|| country_local_name LIKE '%$location%'";

Jusque la ça va je peut faire des recherche fulltext sur ces trois tables et avoir des résulta du genre:
location, region, country
paris, france
Nord-Pas de Calais, lille, france
Laval, Québec, Canada ...

Ce que je veux faire maintenant c'est lier ces trois table a une quatrième table qui contiens une liste de lycées

Code mySql :

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

1
2
3
4
5
6
7
8
9
10
11
12
 
-- `high_school`
 
CREATE TABLE `high_school` (
  `h_s_id` mediumint(8) unsigned NOT NULL AUTO_INCREMENT,
  `h_s_name` varchar(200) CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL,
  `h_s_location_type` tinyint(1) unsigned NOT NULL,
  `h_s_location_id` int(11) unsigned NOT NULL,
  PRIMARY KEY (`h_s_id`),
  KEY `h_s_location_type` (`h_s_location_type`,`h_s_location_id`),
  FULLTEXT KEY `h_s_name` (`h_s_name`)
)

cette table est relier avec les table de location, avec les champs 'h_s_location_type' et 'h_s_location_id'

'h_s_location_type' a trois valeur 1,2 et 3 ce qui définie si la localisation du lycée est enregistrer dans la table 'location_country' ou 'location_region' ou 'location_location', en gros si elle vaut 1 je chercher la valeur de 'h_s_location_id' dans la table 'location_country' si elle vaut 2 je chercher dans la table 'location_region' ...

de mon coté je n'est fait qu'ajouter 'high_school,' a la requête qui ma été donné précédemment avec quelque conditions coté "WHERE"

Code sql :

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
 
$sql = "SELECT * FROM high_school, location_country
LEFT JOIN location_region ON region_country_id = country_id
LEFT JOIN location_location ON location_region_id = region_id
 
	WHERE
   	h_s_name LIKE '%$h_s_name%'
	&& (
   		(
   		h_s_location_type = 1 
     		&&
     		country_id = h_s_location_id
   		)
   	||
   		(
   		h_s_location_type = 2
   		&&
   		region_id = h_s_location_id
   		)
   	||
		(
   		h_s_location_type = 3
   		&&
   		location_id = h_s_location_id
   		)
	)
	ORDER BY h_s_name && h_s_id
	LIMIT 0,8"

Cette requête marche très bien quand il s'agi du type 2 et 3 c'est-a-dire quand l'adresse du lycée ce trouve dans la table 'location_location' ou 'location_region', le problème est avec le type 1 (la table 'location_country')
la j'ai un résultat qui ce répète avec tous les pays que j'ai enregistrer dans la table 'location_country'

__exemple d'affichage avec h_s_location_type 2 :
nom du Lycée
region, country

__ exemple d'affichage avec h_s_location_type 3 :
nom du Lycée
location, region, country

__ exemple d'affichage avec h_s_location_type 4 :
nom du Lycée
country

nom du Lycée
country

nom du Lycée
country

nom du Lycée
country

..... ça répète avec tous les pays dans la table 'location_country

Je peut très bien faire deux requête la premier qui donne le type et l'id de la localisation, et la deuxième qui retourne la localisation

quelque chose du genre

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
 
$sql_type = array('','country_id','region_id','location_id');
$sql = "SELECT * FROM high_school WHERE	h_s_name LIKE '%$h_s_name%'";
 
$query = mysql_query($sql);
while($response = mysql_fetch_assoc($query))
	{
	$h_s_location_type = $response['h_s_location_type'];
	$h_s_location_id = $response['h_s_location_id'];
 
	$sql2 = "SELECT * FROM  
	location_country 
	LEFT JOIN location_region ON region_country_id = country_id
	LEFT JOIN location_location ON location_region_id = region_id
 
	WHERE
	$sql_type[$h_s_location_type] = $h_s_location_id
	";
 
        $query2 = mysql_query($sql2);
	....
	}

mais c'est vraiment lourd, pour 8 résultats il faut 9 requête SQL

le mieux serai encore une fois de résumé le tous en une seul requête, je suis presque sure qu'il manque un petit quelque chose, pour que ça marche.

Je revient sur ce forum car il ma été d'une grande utilité a mainte reprise, et la encore je me tourne vers vous pour demandé de l'aide merci.

**RunCodePhp** · 28/09/2010, 09h13

Salut

Il y a truc qui me parais vraiment bizarre, c'est justement cette info du champ "h_s_location_type".
D'après ce que je comprends (bien sûr), cette info peut être en contradiction par rapport à la conception de ta Bdd.

Au départ tu as ces 2 tables : location_country et location_region
Ensuite une autre table : location_location, donc chaque ligne est liée aux 2 autres tables ci dessus avec -> location_country_id et location_region_id

Pour finir, les Universités sont liées à la table "location_location" avec h_s_location_id
Ca veut dire que par réaction (en cascade), chaque ligne sera lié aux tables "location_country" et "location_region".
C'est d'ailleurs ceci qui permettra de faire les jointures.
Ca veut surtout dire, que chaque Université aura obligatoirement un Pays et une région, ça ne peut pas être autrement.

Donc là où je veux en venir, c'est que rajouter une info qui dit que tel ou tel Université est dans un Pays ou Région ou Localité, sous entendu dans un Pays mais pas les autres (ni région ni localité), et bien c'est contradictoire à la conception que tu as faite.

Ou alors, ça suppose qu'une ligne dans "location_location" n'aurait aucune valeur (serait NULL) pour une "location_country_id" ou "location_region_id", mais ce n'est pas le cas vu que les 2 sont NOT NULL, ce qui serait bizarre de toute manière.
Puis de toute manière, d'après ta Bdd, une Université et avant tout liée à une localité (h_s_location_id).

Je ne parviens vraiment pas à saisir le but de ce champ "h_s_location_type".
Peux tu donner 1 ou 2 exemple de noms que tu donne pour les localités (qu'est ce qu'une localité quoi) ?

**cool-man** · 28/09/2010, 14h44

Salut,

Je sais j'ai pas bien exposé mon problème, je vais essayé de bien résumé la chose.

Pour commencé je vous invite a voire ce topic que j'ai posté Concept d'un script pour chercher des villes ou des régions (A la Google Map).

En gros j'ai fait un script AJAX qui fait la même chose ou presque que qu'on vous chercher une région ou une ville sur google map, ça marche très bien.

Ce que je veux faire un autre script pour recherche des lycées ou des universités avec leurs adresse, et pour avoir les adresse j'ai relier l'ancienne requête qui affiche les adresse avec une simple requête de recherche fulltext dans le nom des lycée, ce qui a donné la requête suivante.

Code SQL :

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
 
$sql = "SELECT * FROM high_school, location_country
LEFT JOIN location_region ON region_country_id = country_id
LEFT JOIN location_location ON location_region_id = region_id
 
	WHERE
   	h_s_name LIKE '%$h_s_name%'
	&& (
   		(
   		h_s_location_type = 1 
     		&&
     		country_id = h_s_location_id
   		)
   	||
   		(
   		h_s_location_type = 2
   		&&
   		region_id = h_s_location_id
   		)
   	||
		(
   		h_s_location_type = 3
   		&&
   		location_id = h_s_location_id
   		)
	)
	ORDER BY h_s_name && h_s_id
	LIMIT 0,8"

la j'ai relier les trois table du système de recherche d'adresse, avec la table ou sont enregistrer les nom de lycées, avec les champs 'h_s_location_type' et 'h_s_location_id'.

pour comprendre 'h_s_location_type', il faut comprendre que l'adresse du lycée peut être une localité (location_location), une region (location_region) ou un pays(location_country), c'est plus au moins précis (ça dépend de l'utilisateur qui enregistre sont lycée).

en gros si l'adresse est une localité 'h_s_location_type' vaut '3' est 'h_s_location_id' est l'id de la localité (location_id = h_s_location_id)
(talbe=>location_location)

Si c'est une région 'h_s_location_type' vaut '2' et 'h_s_location_id' est l'id de la region (region_id = h_s_location_id)
(talbe=location_region)

si c'est un pays elle vaut 1 donc 'h_s_location_id' est l'id d'un pays (country_id = h_s_location_id)(talbe=location_country)

ce qui a donné la condition:

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
'(
   		h_s_location_type = 1 
     		&&
     		country_id = h_s_location_id
   		)
   	||
   		(
   		h_s_location_type = 2
   		&&
   		region_id = h_s_location_id
   		)
   	||
		(
   		h_s_location_type = 3
   		&&
   		location_id = h_s_location_id
   		)'

la requête que j'ai fais marche très bien avec les type 2 et 3, c'est a dire région et locatlité, mais avec le type 1 quand l'adresse est un pays, ça répète le nom du lycée avec tous les pays de la table 'location_country'.

j'été sure que le problème vient de la jointure que j'ai fait entre les 'high_school' et 'location_country' un simple virgule, mais j'ai aussi essayé avec JOINT LEFT ET RIGHT, toujours le même problème.

exemple d'affichage:
Nom du lycée
localité, région, pays (du lycée).

Ps:
location ou localité peut être arrondissement ou un quartier ou même les deux réuni.
region c'est pour les villes, ou états ou ...
et country c'est pour les pays

Merci.

**RunCodePhp** · 28/09/2010, 16h05

Je ne parviens pas du tout à me faire au principe que tu utilise, car ça ne reflèterait pas du tout la réalité.

Dans la réalité, une Universite (un bâtiment en dur) est obligatoirement liée à 1 Pays, et 1 seul, c'est physique.
De même qu'une Université (la même) est obligatoirement liée une région, et 1 seule.
Mais encore, une région est obligatoirement lié à 1 Pays, et 1 seul.
Ca ne peut pas être autrement à mon avis.

Le principe supposerait de créer une Université qui ne serait lié à aucun Pays, ou alors lié à un Pays mais aucune Région ???

pour comprendre 'h_s_location_type', il faut comprendre que l'adresse du lycée peut être une localité (location_location), une region (location_region) ou un pays(location_country), c'est plus au moins précis (ça dépend de l'utilisateur qui enregistre sont lycée).

Par rapport aux infos que tu donne, on dirait que pour une Université, la valeur stockée dans le champ "h_s_location_id" correspondrait tantôt un country_id, tantôt une region_id voir même un h_s_location_id.
Le champ "h_s_location_type" serait pour dire sur quelle table cette donnée est liée ???

Si c'est ça, pour ma part le principe me semble pas correcte, pas du tout même.

Franchement, pourquoi ne pas respecter la réalité, c'est à dire dès lors de l'enregistrement d'une Université, on la lie à une localité, point final, faut l'imposer.
Une localité étant par relation liée à une Région, et une Région étant par relation liée à un Pays, et bien les jointures feront qu'on pourra toujours obtenir la localité/Région/Pays de toutes les Universités.

En tout cas, je ne vois pas du tout comment résoudre ton problème, car à mon sens, même si tu remarque que pour certains types tu obtiens des résultats, j'ai plutôt tendance à penser que c'est le hasard qui veut ça, ou presque.

Relier une table avec un système de recherche d'adresse

PHP & Base de données

Discussions similaires

Partager

Partager