Guillaume Rivière 2012 – 2017

Le logo de la CCI Bayonne Pays Basque

Informatisation du Système d'Information

TP5 : Extraction d'informations d'une base de données

BUTS PÉDAGOGIQUES

  • Connexion au SGBD MySQL depuis un script PHP
  • Envoyer des requêtes à une base de données MySQL depuis un script PHP
  • Afficher dans une page web les résultats d'une requête sur une base de données MySQL

Beaucoup des progiciels utilisés dans les systèmes d'information stockent leurs informations dans des bases de données. Il est souvent intéressant de pouvoir en extraire de l'information pour pouvoir la transférer vers un autre progiciel.

Ce TP propose de voir comment interroger une base de données avec un script PHP.

Grâce à certaines classes de PHP, il est possible de se connecter au SGBD MySQL (entre autres SGBD) et de faire des requêtes SQL :

Figure 0.1 : Contexte de l'environnement WAMP avec MySQL.

Pour ce faire, il existe deux possibilités dans PHP : la classe mysqli et la classe PDO. L'avantage de la classe PDO est de pouvoir (de la même manière que JDBC) gérer différents SGBD. La classe mysqli ne permet que de se connecter au SGBD MySQL, mais elle est légèrement plus simple à utiliser que PDO. Nous proposons de travailler avec la classe mysqli dans la suite de ce TP.

Exercice 1 • Mettre en place la base de données existante (10 min)

Nous étudions, dans ce TP, le cas d'une entreprise créée en 1990 par M. Iban Ilbarritz. Aujourd'hui, l'entreprise compte 22 employés, qui exercent des métiers divers, travaillant chacun dans un ou plusieurs services. Le système d'information de l'entreprise comprend une base de données décrivant les employés, leurs métiers et les services dans lesquels ils sont affectés.

Le schéma relationnel de cette base de données est le suivant :

Figure 1.1 : Le schéma relationnel.

Notation : les clés primaires sont soulignées, les clés étrangères sont précédées d'un croisillon « # ».

Figure 1.2 : Étapes pour importer la base de donnée à partir du fichier SQL.

Exercice 2 • Afficher des informations sur les employés (40 min)

La base de données stocke le nom, le prénom, la date de naissance et la date d'arrivée dans l'entreprise des employés.
Vous devez écrire le script liste_employes.php qui affichera dans un tableau HTML la liste de tous les employés avec :
• leur prénom ;
• leur nom ;
• leur âge ;
• leur ancienneté ;
• leur année d'arrivée ;
• et leur âge à l'arrivée dans l'entreprise.

Figure 2.1 : La page qui présentera la liste des employés.

Pour ce faire, vous utiliserez la requête SQL suivante :

SELECT
   nom,
   prenom,
   (YEAR(CURDATE()) - YEAR(date_naissance)) - (RIGHT(CURDATE(),5) < RIGHT(date_naissance,5)) AS age,
   (YEAR(CURDATE()) - YEAR(date_arrivee)) - (RIGHT(CURDATE(),5) < RIGHT(date_arrivee,5)) AS anciennete,
   YEAR(date_arrivee) AS arrivee,
   (YEAR(date_arrivee) - YEAR(date_naissance)) - (RIGHT(date_arrivee,5) < RIGHT(date_naissance,5)) AS age_arrivee
FROM
   employe
ORDER BY
   age DESC ;

Documentation complète et détaillée sur www.php.net :
La classe mysqli et ses méthodes :
mysqli::__construct()
mysqli::query()
mysqli::close()

La classe mysqli_result, son attribut mysqli_result::$num_rows et ses méthodes :
mysqli_result::fetch_assoc()
mysqli_result::free()

Utilisation : (voir aussi les deux exemples complets ci-après)

  1. $mysqli = new mysqli($host, $user, $password, $db_name)
    • Cet appel crée un nouvel objet mysqli qui permet d'établir une connexion avec le SGBD MySQL et de sélectionner la base de données avec laquelle vous allez travailler.
    • Utilisez les identifiants suivants :
      • hôte : "localhost"
      • utilisateur : "root"
      • mot de passe : "" (c'est-à-dire chaîne vide)
      • base de données : "db_entreprise"
    • En cas d'erreur lors de la connexion (mauvaise adresse, mauvais login ou mot de passe, problème de réseau…), l'attribut $connect_errno sera positionné à TRUE
  2. $result = $mysqli->query($query)
    • Cet appel permet de faire une requête sur la base de donnée (qui a précédemment été sélectionnée lors de la création de l'objet $mysqli).
    • Le paramètre est une chaîne de caractère décrivant la requête.
    • En cas d'erreur lors de la requête (p. ex. erreur de syntaxe dans la requête SQL…), la valeur retournée sera FALSE. En cas de succès, la valeur retournée décrira le résultat de la requête (les données dans le cas d'un SELECT, et TRUE dans le cas d'un INSERT, UPDATE, DELETE ou DROP) et sera un objet de la classe mysqli_result.
  3. $result->num_rows
    La valeur de cet attribut sera le nombre de réponses du résultat lors d'une requête SELECT.
  4. $row = $mysqli_result->fetch_assoc()
    • Cet appel permet de décomposer dans un tableau la première ligne du résultat d'une requête SELECT.
    • Par exemple, $row['field'] permettra d'accéder au champ field de la première ligne de résultat.
    • En renouvelant cet appel, c'est ensuite la valeur de la deuxième ligne qui sera retournée, et ainsi de suite.
    • La valeur retournée sera FALSE lorsqu'il n'y aura plus de nouvelle ligne de résultat à traiter.
  5. $mysqli_result->free()
    Cet appel permet, lorsqu'on a fini de traiter le résultat, de libérer la mémoire allouée par $mysqli->query() dans le cas d'une requête SELECT.
  6. $mysqli->close()
    Quand la connexion au SGBD n'est plus utile (ou avant la fin du programme), cet appel permet de rompre la connexion qui avait été établie au préalable.

Exemple en comptant le nombre d'itérations :

Code 2.1 : Appels PHP pour se connecter au SGDB, envoyer une requête, et afficher le résultat en HTML avec une boucle for.

Exemple en bouclant tant que possible :

Code 2.2 : Appels PHP pour se connecter au SGDB, envoyer une requête, et afficher le résultat en HTML avec une boucle while.

Exercice 3 • Afficher les métiers et les services d'affectation (40 min)

La base de données décrit dans quel service tel employé exerce tel métier et selon quel régime de temps.

1) Vous devez écrire le script liste_metiers.php qui affichera dans un premier tableau HTML la liste de tous les employés avec :
• leur prénom ;
• leur nom ;
• leur métier (un ou plusieurs) ;
• le service d'affectation pour ce métier (un ou plusieurs) ;
• et le temps imparti.

2) Dans un deuxième tableau HTML, le script affichera ensuite la liste des employés qui travaillent dans plus de 1 service (en affichant à chaque fois le prénom, le nom et le service).

Figure 3.1 : La page qui présentera la liste des employés avec leur métier et la liste de ceux travaillant dans plus de 1 service.

Pour ce faire, vous utiliserez les deux requêtes SQL suivantes :

SELECT
   employe.nom,
   employe.prenom,
   metier.nom AS metier,
   service.nom AS service,
   exerce.temps
FROM
   employe
     INNER JOIN exerce ON employe.id = exerce.id_employe
     INNER JOIN metier ON metier.id = exerce.id_metier
     INNER JOIN service ON service.id = exerce.id_service ;

SELECT
   employe.nom,
   employe.prenom,
   service.nom AS service
FROM
   employe
     INNER JOIN exerce ON employe.id = exerce.id_employe
     INNER JOIN service ON service.id = exerce.id_service
WHERE
   employe.id IN (
     SELECT
       id_employe
     FROM
       exerce
     GROUP BY
       id_employe
     HAVING
       COUNT(DISTINCT id_service) > 1
     ) ;

Exercice 4 • Afficher les employés d'un service (20 min)

Écrire le script index.php qui comporte tout d'abord 2 liens vers les scripts liste_employes.php et liste_metiers.php écrits précédemment. Ensuite ce script doit proposer un formulaire HTML qui permet de sélectionner un service grâce à une liste à choix. La validation du formulaire appellera le script liste_service.php, que vous devez également écrire, et qui affichera dans un tableau HTML la liste des employés du service sélectionné (en affichant à chaque fois le prénom, le nom, le métier et le temps imparti).

NB : Les noms des services proposés dans la liste à choix devront provenir du résultat d'une requête sur la base de données.

Figure 4.1 : La page d'accueil avec des liens et un formulaire qui permet de choisir un service.
Figure 4.2 : La page avec la liste des employés du service qui aura été sélectionné depuis la page d'accueil.

Pour ce faire, vous utiliserez la requête SQL suivante, où $id est le id du service à afficher :

SELECT
   employe.nom,
   employe.prenom,
   metier.nom AS metier,
   exerce.temps
FROM
   employe
     INNER JOIN exerce ON employe.id = exerce.id_employe
     INNER JOIN metier ON metier.id = exerce.id_metier
WHERE
     exerce.id_service = $id ;

La balise <select> permet de créer une liste déroulante d'éléments (précisés par les balises <option>). En lui spécifiant un attribut name, la balise <select> peut être utilisée, dans un formulaire, au même titre qu'une balise <input>.
<select name="liste_a_choix" >
  <option value="1">Choix 1</option>
  <option value="2">Choix 2</option>
  <option value="3">Choix 3</option>
  <option value="4">Choix 4</option>
</select>