Concevez votre site web avec PHP et MySQL

MySQL permet de travailler avec plusieurs tables à la fois. Un des principaux intérêts d'une base de données est de pouvoir créer des relations entre les tables, de pouvoir les lier entre elles.

Pour le moment, nous n'avons travaillé que sur une seule table à la fois. Dans la pratique, vous aurez certainement plusieurs tables dans votre base, dont la plupart seront interconnectées. Cela vous permettra de mieux découper vos informations, d'éviter des répétitions et de rendre ainsi vos données plus faciles à gérer.

Tenez, par exemple, dans notre tablejeux_video, on répète à chaque fois le nom du possesseur du jeu. Le mot « Patrick » est écrit de nombreuses fois dans la table. Imaginez que l'on souhaite stocker aussi son nom de famille, son adresse, son numéro de téléphone… On ne va quand même pas recopier ces informations pour chaque jeu qu'il possède ! Il est temps de créer une autre table et de la lier.

Modélisation d'une relation

Si je voulais stocker les nom, prénom et numéro de téléphone de chaque propriétaire de jeux vidéo dans notre tablejeux_video, il n'y aurait pas d'autre solution que de dupliquer ces informations sur chaque entrée… Cependant ce serait bien trop répétitif ; regardez ce que ça donnerait sur le tableau suivant.

Comme vous le voyez, le nom, le prénom et le numéro de téléphone de Florent apparaissent autant de fois qu'il possède de jeux vidéo, et il en irait de même pour Patrick et Michel. Il faut à tout prix éviter ces répétitions.

Ce que je vous propose, c'est de créer une autre table, que l'on nommera par exempleproprietaires, qui centralisera les informations des propriétaires des jeux (tableau suivant).

Cette table liste tous les propriétaires de jeux connus et attribue à chacun un ID. Les propriétaires n'apparaissant qu'une seule fois, il n'y a pas de doublon.

Maintenant, il faut modifier la structure de la tablejeux_videopour faire référence aux propriétaires. Pour cela, le mieux est de créer un champID_proprietairequi indique le numéro du propriétaire dans l'autre table (tableau suivant).

Le nouveau champID_proprietaireest de typeINT. Il permet de faire référence à une entrée précise de la tableproprietaires.

On peut maintenant considérer que les tables sont reliées à travers ces ID de propriétaires, comme le suggère la figure suivante.

Non, il ne le sait pas. Il ne voit que des nombres et il ne fait pas la relation entre les deux tables. Il va falloir lui expliquer cette relation dans une requête SQL : on va faire ce qu'on appelle une jointure entre les deux tables.

Qu'est-ce qu'une jointure ?

Nous avons donc maintenant deux tables :

• jeux_video ;

• proprietaires.

Les informations sont séparées dans des tables différentes et c'est bien. Cela évite de dupliquer des informations sur le disque.

Cependant, lorsqu'on récupère la liste des jeux, si on souhaite obtenir le nom du propriétaire, il va falloir adapter la requête pour récupérer aussi les informations issues de la tableproprietaires. Pour cela, on doit faire ce qu'on appelle une jointure.

Il existe plusieurs types de jointures, qui nous permettent de choisir exactement les données que l'on veut récupérer. Je vous propose d'en découvrir deux, les plus importantes :

• les jointures internes : elles ne sélectionnent que les données qui ont une correspondance entre les deux tables ;

• les jointures externes : elles sélectionnent toutes les données, même si certaines n'ont pas de correspondance dans l'autre table.

Il est important de bien comprendre la différence entre une jointure interne et une jointure externe.

Pour cela, imaginons que nous ayons une 4e personne dans la table des propriétaires, un certain Romain Vipelli, qui ne possède aucun jeu (tableau suivant).

Romain Vipelli est référencé dans la tableproprietairesmais il n'apparaît nulle part dans la tablejeux_videocar il ne possède aucun jeu.

Si vous récupérez les données des deux tables à l'aide :

• d'une jointure interne : Romain Vipelli n'apparaîtra pas dans les résultats de la requête. La jointure interne force les données d'une table à avoir une correspondance dans l'autre ;

• d'une jointure externe : vous aurez toutes les données de la table des propriétaires, même s'il n'y a pas de correspondance dans l'autre table des jeux vidéo ; donc Romain Vipelli, qui pourtant ne possède aucun jeu vidéo, apparaîtra.

La jointure externe est donc plus complète car elle est capable de récupérer plus d'informations, tandis que la jointure interne est plus stricte car elle ne récupère que les données qui ont une équivalence dans l'autre table.

Voici par exemple les données que l'on récupèrerait avec une jointure interne (tableau suivant) :

On obtient les jeux et leurs propriétaires, mais Romain qui ne possède pas de jeu n'apparaît pas du tout. En revanche, avec une jointure externe (tableau suivant) :

Romain apparaît maintenant. Comme il ne possède pas de jeu, il n'y a aucun nom de jeu indiqué (NULL).

Nous allons maintenant voir comment réaliser ces deux types de jointures en pratique.

Les jointures internes

Une jointure interne peut être effectuée de deux façons différentes :

• à l'aide du mot-cléWHERE : c'est l'ancienne syntaxe, toujours utilisée aujourd'hui, qu'il faut donc connaître mais que vous devriez éviter d'utiliser si vous avez le choix ;

• à l'aide du mot-cléJOIN : c'est la nouvelle syntaxe qu'il est recommandé d'utiliser. Elle est plus efficace et plus lisible.

Ces deux techniques produisent exactement le même résultat, mais il faut les connaître toutes les deux. ;-)

Jointure interne avecWHERE(ancienne syntaxe)

Construction d'une jointure interne pas à pas

Pour réaliser ce type de jointure, on va sélectionner des champs des deux tables et indiquer le nom de ces deux tables dans la clauseFROM :

SELECT nom, prenom FROM proprietaires, jeux_video

Cependant ça ne fonctionnera pas car ce n'est pas suffisant. En effet, le champnomapparaît dans les deux tables : une fois pour le nom du propriétaire, et une autre fois pour le nom du jeu vidéo. C'est ce qu'on appelle une colonne ambiguë car MySQL ne sait pas s'il doit récupérer un nom de personne (comme Dugommier) ou un nom de jeu (comme Super Mario Bros). Bref, il est un peu perdu.

L'astuce consiste à marquer le nom de la table devant le nom du champ, comme ceci :

SELECT jeux_video.nom, proprietaires.prenom FROM proprietaires, jeux_video

Ainsi, on demande clairement de récupérer le nom du jeu et le prénom du propriétaire avec cette requête.

Il reste encore à lier les deux tables entre elles. En effet, les jeux et leurs propriétaires ont une correspondance via le champID_proprietaire(de la tablejeux_video) et le champID(de la tableproprietaires). On va indiquer cette liaison dans unWHERE, comme ceci :

SELECT jeux_video.nom, proprietaires.prenom
FROM proprietaires, jeux_video
WHERE jeux_video.ID_proprietaire = proprietaires.ID

On indique bien que le champID_proprietairede la tablejeux_videocorrespond au champIDde la tableproprietaires. Cela établit la correspondance entre les deux tables telle qu'on l'avait définie dans le schéma suivant au début du chapitre.

Notre requête est enfin complète, vous pouvez l'essayer.

Vous devriez récupérer les données suivantes :

Utilisez les alias !

Nous avons appris à utiliser les alias lorsque nous avons découvert les fonctions SQL. Cela nous permettait de créer ce que j'appelais des « champs virtuels » pour représenter le résultat des fonctions.

Il est fortement conseillé d'utiliser des alias lorsqu'on fait des jointures. On peut utiliser des alias sur les noms de champs (comme on l'avait fait) :

SELECT jeux_video.nom AS nom_jeu, proprietaires.prenom AS prenom_proprietaire
FROM proprietaires, jeux_video
WHERE jeux_video.ID_proprietaire = proprietaires.ID

On récupèrera donc deux champs :nom_jeuetprenom_proprietaire. Ces alias permettent de donner un nom plus clair aux champs que l'on récupère.

Il est également possible de donner un alias aux noms des tables, ce qui est fortement recommandé pour leur donner un nom plus court et plus facile à écrire. En général, on crée des alias de tables d'une lettre ou deux correspondant à leurs initiales, comme ceci :

SELECT j.nom AS nom_jeu, p.prenom AS prenom_proprietaire
FROM proprietaires AS p, jeux_video AS j
WHERE j.ID_proprietaire = p.ID

Comme vous le voyez, la tablejeux_videoa pour alias la lettrejetproprietairesla lettrep. On réutilise ces alias dans toute la requête, ce qui la rend plus courte à écrire (et plus lisible aussi au final).

Notez que le mot-cléASest en fait facultatif, les développeurs ont tendance à l'omettre. Vous pouvez donc tout simplement le retirer de la requête :

SELECT j.nom nom_jeu, p.prenom prenom_proprietaire
FROM proprietaires p, jeux_video j
WHERE j.ID_proprietaire = p.ID

Jointure interne avecJOIN(nouvelle syntaxe)

Bien qu'il soit possible de faire une jointure interne avec unWHEREcomme on vient de le voir, c'est une ancienne syntaxe et aujourd'hui on recommande plutôt d'utiliserJOIN. Il faut dire que nous étions habitués à utiliser leWHEREpour filtrer les données, alors que nous l'utilisons ici pour associer des tables et récupérer plus de données.

Pour éviter de confondre leWHERE« traditionnel » qui filtre les données et leWHEREde jointure que l'on vient de découvrir, on va utiliser la syntaxeJOIN.

Pour rappel, voici la requête qu'on utilisait avec unWHERE :

SELECT j.nom nom_jeu, p.prenom prenom_proprietaire
FROM proprietaires p, jeux_video j
WHERE j.ID_proprietaire = p.ID

Avec unJOIN, on écrirait cette même requête de la façon suivante :

SELECT j.nom nom_jeu, p.prenom prenom_proprietaire
FROM proprietaires p
INNER JOIN jeux_video j
ON j.ID_proprietaire = p.ID

Cette fois, on récupère les données depuis une table principale (ici,proprietaires) et on fait une jointure interne (INNER JOIN) avec une autre table (jeux_video). La liaison entre les champs est faite dans la clauseONun peu plus loin.

Le fonctionnement reste le même : on récupère les mêmes données que tout à l'heure avec la syntaxeWHERE.

Si vous voulez filtrer (WHERE), ordonner (ORDER BY) ou limiter les résultats (LIMIT), vous devez le faire à la fin de la requête, après le « ON j.ID_proprietaire = p.ID ».

Par exemple :

SELECT j.nom nom_jeu, p.prenom prenom_proprietaire
FROM proprietaires p
INNER JOIN jeux_video j
ON j.ID_proprietaire = p.ID
WHERE j.console = 'PC'
ORDER BY prix DESC
LIMIT 0, 10

Traduction (inspirez un grand coup avant de lire) : « Récupère le nom du jeu et le prénom du propriétaire dans les tablesproprietairesetjeux_video, la liaison entre les tables se fait entre les champsID_proprietaireetID, prends uniquement les jeux qui tournent sur PC, trie-les par prix décroissants et ne prends que les 10 premiers. »

Il faut s'accrocher avec des requêtes de cette taille-là ! ;-)

Les jointures externes

Les jointures externes permettent de récupérer toutes les données, même celles qui n'ont pas de correspondance. On pourra ainsi obtenir Romain Vipelli dans la liste même s'il ne possède pas de jeu vidéo.

Cette fois, la seule syntaxe disponible est à base deJOIN. Il y a deux écritures à connaître :LEFT JOINetRIGHT JOIN. Cela revient pratiquement au même, avec une subtile différence que nous allons voir.

LEFT JOIN : récupérer toute la table de gauche

Reprenons la jointure à base deINNER JOINet remplaçons tout simplementINNERparLEFT :

SELECT j.nom nom_jeu, p.prenom prenom_proprietaire
FROM proprietaires p
LEFT JOIN jeux_video j
ON j.ID_proprietaire = p.ID

proprietairesest appelée la « table de gauche » etjeux_videola « table de droite ». LeLEFT JOINdemande à récupérer tout le contenu de la table de gauche, donc tous les propriétaires, même si ces derniers n'ont pas d'équivalence dans la tablejeux_video.

Romain apparaît désormais dans les résultats de la requête grâce à la jointure externe. Comme il ne possède aucun jeu, la colonne du nom du jeu est vide.

RIGHT JOIN : récupérer toute la table de droite

LeRIGHT JOINdemande à récupérer toutes les données de la table dite « de droite », même si celle-ci n'a pas d'équivalent dans l'autre table. Prenons la requête suivante :

SELECT j.nom nom_jeu, p.prenom prenom_proprietaire
FROM proprietaires p
RIGHT JOIN jeux_video j
ON j.ID_proprietaire = p.ID

La table de droite est « jeux_video ». On récupèrerait donc tous les jeux, même ceux qui n'ont pas de propriétaire associé.

Il y a deux cas possibles :

• soit le champID_proprietairecontient une valeur qui n'a pas d'équivalent dans la table des propriétaires, par exemple « 56 » ;

• soit le champID_proprietairevautNULL, c'est-à-dire que personne ne possède ce jeu. C'est le cas notamment du jeu Bomberman dans la table que vous avez téléchargée (voir tableau suivant).

Dans ce cas, Bomberman n'appartient à personne. Avec la requêteRIGHT JOINque l'on vient de voir, on obtiendra toutes les lignes de la table de droite (jeux_video) même si elles n'ont aucun lien avec la tableproprietaires, comme c'est le cas ici pour Bomberman.

On obtiendra donc les données exposées dans le tableau suivant.

En résumé

• Les bases de données permettent d'associer plusieurs tables entre elles.

• Une table peut contenir les id d'une autre table ce qui permet de faire la liaison entre les deux. Par exemple, la table des jeux vidéo contient pour chaque jeu l'id de son propriétaire. Le nom et les coordonnées du propriétaire sont alors stockés dans une table à part.

• Pour rassembler les informations au moment de la requête, on effectue des jointures.

• On peut faire des jointures avec le mot-cléWHERE, mais il est recommandé d'utiliserJOINqui offre plus de possibilités et qui est plus adapté.

• On distingue les jointures internes, qui retournent des données uniquement s'il y a une correspondance entre les deux tables, et les jointures externes qui retournent toutes les données même s'il n'y a pas de correspondance.