Programmation et Base de données - Cours E.Thirion

Programmation et Base de Données - Exercice Php-MySql - Page 1
Programmation et Base de données
Exercices sur la Partie Php - MySql
Auteur : E. Thirion - Dernière mise à jour : 01/08/2015
Extrait du site : http://cours.thirion.free.fr
Liens utiles:
●
Voir le cours associé à ces exercices
●
Installation des dossiers et logiciels nécessaires.
●
Conditions d'accès au téléchargement.
●
Voir tous les cours du même auteur.
Corrigés
Les corrigés des exercices suivant se trouvent dans le dossier Corrige-ProgBD accessible par
téléchargement.
Exercice 1 : Sélection d'horaires
Connaissance SQL nécessaires: requête select de base. Voir cours I sur les bases de données.
Objectif
On veut pouvoir sélectionner les horaires dont la ville de départ est fixée et dont l'heure de départ ne
dépasse pas une certaine heure maximale donnée.
Les données sont saisie à l'aide d'un formulaire que vous pouvez visualiser en cliquant sur le lien Votre
Solution, en-dessous de l'intitulé Sélection d'horaire, dans la partie Tests Php du menu.
On souhaiterait que lorsque l'on clique sur le bouton Générer Requete, la requête de sélection s'affiche
dans la zone de texte, mais ne s'exécute pas.
Lorsque l'on clique sur le bouton Exécuter Requete, la requête devra s'exécuter et son résultat devra
s'afficher sous le formulaire.
Le code php du formulaire est contenu dans le fichier Etudiant/FormulaireHoraires.php. Mais pour faire
cet exercice vous n'avez besoin d'aucune connaissance sur le traitement des formulaires en Php. Vous
pouvez donc totalement ignorer le code du formulaire. Par contre, il vous faudra écrire deux fonctions :
•
GenererRequeteA($v,$h) : retourne la requête sous la forme d'une chaine de caractères. $v
représente la ville de départ et $h l'heure de départ maximale.
•
ExecuterEtAfficher ($Requete) : exécute la requête et affiche la table résultat.
Les entêtes de ces deux fonctions se trouvent dans le fichier Etudiant/Includes-Formulaire.php. Ecrivez votre
code à l'endroit indiqué dans ce fichier. Ne modifiez pas le code source du formulaire !
Page 1
Programmation et Base de Données - Exercice Php-MySql - Page 2
Exercice 2 - Gestion des Passagers
Connaissance SQL nécessaires: requêtes de modification d'une base de donnée. Voir cours II.
Objectif
On voudrait ajouter une table de passagers à la base BDVols en utilisant le formulaire que vous pouvez
visualiser en cliquant sur le lien Votre Solution dans la partie TestsPHP du menu.
Un passager est décrit par son numéro, son nom et son prénom. Ces attributs seront respectivement
représentés par :
•
•
•
un champ NumeroP de type INT en numérotation automatique.
un champ Nom de type VARCHAR(20).
un champ Prenom de type VARCHAR(20).
A chaque bouton du formulaire correspond une fonction particulière dans le fichier Etudiant/IncludesSQL.php dont vous devrez écrire le code.
L'affichage de la table des passager est déjà réalisé. Pour que l'affichage fonctionne correctement votre
table doit impérativement s'appeler Passagers.
D'autre part, pour vous faciliter les tests, vous pouvez utiliser le bouton Remplir qui permet de remplir la
table des passagers à partir d'un fichier de nom et prénoms existant (le fichier Etudiant/Passagers.txt).
1) Le bouton CreerT : création de la table
Un clic sur ce bouton provoque l'appel de la fonction CreerTablePassager. Complètez le code de cette
fonction de manière à ce que ce clic provoque la création de la table Passagers.
2) Le bouton SupprT : suppression de la table
Un clic sur ce bouton provoque l'appel de la fonction SupprimerTablePassager. Complètez le code de cette
fonction de manière à ce que ce clic provoque la suppression de la table Passagers.
3) Le bouton Ajouter : adjonction d'un passager
Un clic sur ce bouton provoque l'appel de la fonction AjouterPassager ($n,$p) avec $n et $p contenant
respectivement le nom et le prenom saisis par l'utilisateur dans les zones de texte prévues à cet effet
(Nouveau nom et Nouveau prénom).
Complètez le code de cette fonction de manière à ce que ce clic provoque l'adjonction d'un nouveau
passager de nom $n et de prénom $p à la table Passagers.
Attention : pour que ce bouton fonctionne le nom et le prénom doivent être saisies dans les champs de la
colonne nouveau.
4) Le bouton Suppr : suppression d'un passager
Un clic sur ce bouton provoque l'appel de la fonction SupprimerPassager($n,$p) avec $n et $p contenant
respectivement le nom et le prenom saisis par l'utilisateur. Complètez le code de cette fonction de manière à
ce que ce clic provoque la suppression du passager de nom $n et de prénom $p de la table Passagers.
Attention : pour que ce bouton fonctionne le nom et le prénom doivent être saisies dans les champs de la
colonne ancien.
Page 2
Programmation et Base de Données - Exercice Php-MySql - Page 3
5) Le bouton Vider : suppressions de tous les passagers
Un clic sur ce bouton provoque l'appel de la fonction ViderTablePassager. Complètez le code de cette
fonction de manière à ce que ce clic provoque la suppression de tous les enregistrements de la table
Passagers.
6) Le bouton ModifN : modification de nom
Un clic sur ce bouton provoque l'appel de la fonction ModifierNom($NomA,$PreA,$NomN) avec $NomA et
$PreA contenant respectivement l'ancien nom et l'ancien prenom et $NomN, le nouveau nom. Complètez le
code de cette fonction de manière à ce que ce clic provoque le remplacement du nom du passager de nom
$NomA et de prénom $PreA par le nom $NomN.
7) Le bouton ModifP : modification de prénom
Un clic sur ce bouton provoque l'appel de la fonction ModifierPrenom($NomA,$PreA,$PreN) avec $NomA
et $PreA contenant respectivement l'ancien nom et l'ancien prenom et $PreN, le nouveau prénom.
Complètez le code de cette fonction de manière à ce que ce clic provoque le remplacement du prénom du
passager de nom $NomA et de prénom $PreA par le prénom $PreN.
8) Le bouton ModifNum : modification du numéro
Un clic sur ce bouton provoque l'appel de la fonction ModifierNumero($NumA,$NumN) avec $NumA et
$NumN contenant respectivement l'ancien numéro et le nouveau numéro. Complètez le code de cette
fonction de manière à ce que ce clic provoque le remplacement du numéro du passager de numéro $NumA
par le numéro $NumN.
Exercice 3 - Gestion des réservations
Connaissance SQL nécessaires: requêtes de modification d'une base de donnée, vues et contraintes
d'intégrité. Voir cours II sur les bases de données.
Objectif
On voudrait à présent pouvoir réserver un vol pour un passager donné en utilisant le formulaire que vous
pouvez visualiser en cliquant sur le lien Votre Solution de la partie Tests Php du menu.
Une réservation est représentée par le numéro du passager qui a reservé et le numéro du vol qu'il a réservé.
Ces attributs seront respectivement représentés par :
•
•
un champ Passager de type INT.
un champ Vol de type INT.
Chaque bouton du formulaire correspond à une fonction particulière dans le fichier Etudiant/IncludesSQL.php dont vous devrez écrire le code.
Attention: pour pouvoir faire fonctionner ce formulaire, la table des passagers doit exister et contenir des
données.
1) Le bouton Creer Table
Un clic sur ce bouton provoque l'appel de la fonction CreerTableReservation. Complètez le code de cette
fonction de manière à ce que ce clic provoque la création de la table Reservations en format InnoDB.
Vous constaterez que l'affichage de la table Réservation figure déjà dans la fonction Reserver. Il vous
suffira donc d'écrire la requête permettant la création de la table Reservations.
Si cette requête est écrite correctement, la table suivante devrait s'afficher sous le formulaire:
Page 3
Programmation et Base de Données - Exercice Php-MySql - Page 4
Passager
Vol
2) Le bouton Suppr Table
Un clic sur ce bouton provoque l'appel de la fonction SupprimerTableReservation. Complètez le code de
cette fonction de manière à ce que ce clic provoque la suppression de la table Reservations.
3) Le bouton Reserver
Un clic sur ce bouton doit provoquer (via la fonction Reserver ($p,$v)) une réservation du vol $v pour le
passager $p, en supposant que $v et $p sont les numéros indiqués dans les champs de texte du formulaire.
Vous constaterez que l'affichage de la table Réservation figure déjà dans la fonction Reserver. Il vous
suffira donc d'écrire la requête ajoutant une entrée dans la table Réservation.
4) Le bouton Annuler
L'effet de ce bouton dépend des deux cases à cocher dont l'état coché/non coché est représenté par deux
booléen $arv (dans colonne Vol du formulaire )et $arp (colonne Passager).
Dans tous les cas de figure la fonction appelée est AnnulerReservations($p,$v,$arp,$arv).
Premier cas : annulation d'une réservation particulière
Pour cela les deux case à cocher doivent être cochées. Dans ce cas $arv et $arp seront vrais. Le bouton
Annuler provoque uniquement l'annulation de la réservation du vol $v pour le passager $p.
Deuxième cas : annulation de toutes les réservations d'un passager
Pour annuler toutes les réservations d'un passager, seule la case à cocher se trouvant dans la colonne
Passager doit être cochée.
Dans ce cas $arv sera faux et $arp sera vrai. Le bouton Annuler provoque l'annulation de toutes les
réservations du passager $p.
Troisième cas : annulation de toutes les réservations d'un vol
Pour annuler toutes les réservations d'un vol seule la case à cocher se trouvant dans la colonne Vol doit être
cochée.
Dans ce cas $arp sera faux et $arv sera vrai. Le bouton Annuler provoque l'annulation de toutes les
réservations du vol $v.
Page 4
Programmation et Base de Données - Exercice Php-MySql - Page 5
Quatrième cas : annulation de toutes les réservations
C'est lorsqu'aucune des deux cases à cocher n'est cochée.
5) Affichage des réservations
On souhaiterait afficher les réservation de la manière suivante:
NP
Nom
Prenom
NV
Depart
Arrivee
Jour
HD
HA
1
Katie
Holmes
11
Paris
Strasbourg
2001-09-04
10
11
1
Katie
Holmes
34
Strasbourg
Paris
2001-09-05
8
9
6
Courtney
Cox
11
Paris
Strasbourg
2001-09-04
10
11
7
Jennifer
Lopez
34
Strasbourg
Paris
2001-09-05
8
9
11
Marilyn
Monroe
11
Paris
Strasbourg
2001-09-04
10
11
Pour cela, on utilisera une vue nommée VueReservations possédant exactement les colonnes suivantes:
•
•
•
•
•
•
•
•
•
NP: Numéro du passager.
Nom: Nom du passager.
Prenom: Prénom du passager.
NV: Numéro du vol.
Départ: Ville de départ du vol.
Arrivée: Ville d'arrivée du vol.
Jour: Date du vol.
HD: Heure de départ du vol.
HA: Heure de d'arrivée du vol.
5-A) Gestion des boutons
Cinq boutons du formulaire sont associés à la visualisation des réservations:
•
Creer Vue: créé et affiche la vue VueReservations.
Fonction associée à complèter: CreerVueReservation.
•
Suppr Vue: supprime la vue VueReservations.
Fonction associée à complèter: SupprimerVueReservation.
•
Reservations: affiche toutes les réservations. Le code associé à ce bouton est déjà écrit. Il ne fait
qu'afficher le contenu de la vue VueReservations.
•
Vols: affiche toute les réservations du passager dont le numéro figure dans le formulaire.
Fonction associée à complèter: AfficherVolDuPassager($p).
•
Passagers: affiche tous les passagers ayant réservé une réservation pour le vol dont le numéro
figure dans le formulaire.
Fonction associée à complèter: AfficherPassagersDuVol($v).
5-B) Test: propagation des mises à jours
Vérifiez que toutes les modifications apportées au réservations, aux vols ou aux passagers sont
automatiquement propagées à la vues. Par exemple:
•
•
•
Ajoutez une réservation et vérifier qu'elle apparait dans la vue.
Supprimez une réservation et vérifier qu'elle apparait dans la vue.
Modifiez le nom ou le prénom d'un passager et vérifiez qu'il est également modifié dans la vue.
Page 5
Programmation et Base de Données - Exercice Php-MySql - Page 6
6) Tests d'intégrité
Pour l'instant, la table Reservations ne contient aucune contrainte d'intégrité. On le vérifiera de la manière
suivante:
•
•
Il est possible d'effectuer deux fois la même réservation !
Il est possible d'effectuer une réservation pour un vol qui n'existe pas ou pour une passager qui
n'existe pas ! Vous vérifierez qu'elle apparaitra dans la table Reservations, mais pas dans la vue
VueReservations.
7) Adjonction d'une contrainte de clé primaire
Un moyen d'éviter les réservations doubles est de définir une clé primaire sur deux colonnes (Passager et
Vol) dans la table Réservation.
C'est le rôle du bouton +CléPrim. Ce bouton fait appel à la fonction AjouterClefPrimaire.
Complètez le code de cette fonction afin qu'un clic sur le bouton +CléPrim ajoute une contrainte de clé
primaire sur les colonnes Passager et Vol dela table Réservation.
Pour vérifier que la contrainte a bien été ajouté à la table, essayez de faire deux fois la même reservation.
Vous devriez en principe obtenir une message d'erreur de ce type:
Réservation impossible !
Erreur SQL Duplicate entry '1-1' for key 'PRIMARY'
8) Adjonction de contraintes d'intégrité référentielle
Attention ! pour cette question les tables Vols et Passagers doivent être en format InnoDB. Utilisez
pour cela le bouton InnoDB du formulaire.
8-A) Intégrité référentielle sur le passager
Afin de d'éviter une réservation pour un passager inexistant, nous allons définir une contrainte d'intégrité
référentielle entre la colonne Passager de la table Reservation et la colonne NumeroP de la table
Passagers.
C'est le rôle du bouton +IR-Passager. Ce bouton fait appel à la fonction AjouterIntegriteRefSurPassager
que l'on vous demande de complèter.
Pour vérifier que la contrainte a bien été ajouté à la table, essayez de faire une réservation pour un passager
inexistant. Vous devriez en principe obtenir une message d'erreur de ce type:
Réservation impossible !
Erreur SQL Cannot add or update a child row: a foreign
key constraint fails ('bdvols'.'reservation',
CONSTRAINT 'reservation_ibfk_3 FOREIGN KEY
('Passager") REFRENCES 'Passagers'('NumeroP'))
8-B) Adjonction de l'option CASCADE
Le bouton CASCADE a le même effet que le bouton +IR-Passager avec l'option ON DELETE CASCADE et
ON UPFDATE CASCADE. e bouton fait appel à la fonction AjouterOptionCascade que l'on vous demande
de complèter.
Pour vérifier que l'option ON DELETE CASCADE fonctionne, supprimez un passager puis vérifiez que
toutes ses reservations on disparues dans la table Reservations (dans la vue VueReservation c'est
automatique!).
Pour vérifier que l'option ON UPDATE CASCADE fonctionne, modifiez le numéro d'un passager puis vérifiez
que ce numéro à également été modifié dans toutes les réservations de ce passager de la table
Page 6
Programmation et Base de Données - Exercice Php-MySql - Page 7
Reservations (dans la vue VueReservation c'est automatique!).
Exercice 4 : Sélection d'horaires - Version 2
Connaissance SQL nécessaires: requête select de base. Voir cours I sur les bases de données.
Objectif
Il s'agit de réaliser une nouvelle version du formulaire de sélection d'horaires de l'exercice 1 dans lequel la
liste de sélection des villes de départ est générée automatiquement et l'affichage des horaires se fera sans
utiliser la fonction AfficherTableSQL.
Le formulaire que vous pouvez visualiser à partir du menu a été un peu simplifié:
●
il n'y a plus de zone de texte pour afficher la requête générée.
●
il n'y a plus qu'un seul bouton pour afficher les horaires sélectionnés.
Ce formulaire utilise deux fonctions importées depuis le fichier Etudiant/Includes-Formulaire2.php :
●
ListeDesVillesDeDepart : cette fonction retourne la liste des villes de départ possibles, c'est à dire
toutes celles qui sont contenues dans la colonne VilleDepart de la table Horaires. Elle est utilisée
pour générer automatiquement la liste de sélection des villes de départ.
●
AfficherLesHoraires ( $v, $h) : affiche les horaires pour la ville de départ $v avec une heure de
départ inférieur ou égale à $h. Voici par exemple la table affichée avec $v= 'Paris' et $h =9 :
Votre travail : complèter ces deux fonctions afin d'obtenir le résultat souhaité.
Page 7
Programmation et Base de Données - Exercice Php-MySql - Page 8
Exercice 5 : Affichage d'un vol
Connaissance SQL nécessaires: requête select avec jointure. Voir cours I sur les bases de données.
Objectif
Il s'agit d'afficher les propriétés d'un vol de numéro donné dans des zones de texte. Le formulaire est déjà
fait. Le voici:
Il s'agit donc d'afficher le jour (colonne JourSem), l'heure (colonne HeureDepart) , la date (colonne Jour) et
la ville de départ (colonne VilleDepart) ainsi que la ville d'arrivée (colonne VilleArrivee) du vol de numéro
donné (colonne NumeroV).
Pour cela, il vous suffira de complèter le fichier Etudiant/AfficherUnVol.php à l'endroit indiquer. Vous
pouvez supposer que le numero du vol se trouve dans la variable $NumV. Pour que cela fonctionne
correctement, il vous suffira d'affecter les bonnes valeurs aux variables $Jour, $JourSem, $VilleDepart,
$VilleArrivee, $HeureDepart sachant que l'affichage de ces variables dans les zones de texte est déjà
codé.
Dernier détail: si le vol de numéro donné n'existe pas, n'affectez aucune valeur à ces variables de manière à
ce que le script affiche des '?' dans les zones de texte.
Page 8