:tocdepth: 2 ============================= Gestion des bases de données ============================= Introduction aux bases de données ================================= Qu'est ce qu'une base de données ? ++++++++++++++++++++++++++++++++++ Un **base de données** (BDD) sert à enregistrer des données * Format organisé et hiérarchisé * Requêtable * Durable Durée de vie d'une variable PHP = temps d'éxécution du script Possibilité de stocker des données dans des fichiers externes => pas viable (grande quantité de données) Pour manipuler une BDD, on utilise un **SGBD**. Un SGBD ? +++++++++ C'est un **Système de Gestion de Base de Données**. Comme son nom l'indique, un SGBD permet : - d'enregistrer des données - d'organiser ces données - d'accèder aux données La plupart des SGBD utilisent le **langage SQL** (Structured Query Language). Les données brutes sont stockées dans des fichiers sur le serveur. Le SGBD a pour mission de permettre leur accès en optimisant les opération de lecture/écriture. Quel SGBD utiliser ? ++++++++++++++++++++ Il existe plusieurs SGBD; parmi les plus utilisés on retrouve : - MariaDB/MySQL - Oracle - Microsoft SQL Server - PostgreSQL Dans ce cours, nous travaillerons avec MariaDB/MySQL. Comment s'interfacent PHP et MySQL ? ++++++++++++++++++++++++++++++++++++ .. figure:: _static/bdd/client-serveur_MySQL.png :alt: client-serveur-mysql #. Le serveur interprète le script PHP #. PHP interroge (requête) la base de données par MariaDB/MySQL #. MariaDB/MySQL renvoie les données en réponse à la requête #. PHP traite les données reçues côté serveur .. note:: Dans ce schéma, vous pourriez remplacer PHP par n'importe quel langage dédié à la gestion de pages web dynamiques. De même, MariaDB/MySQL pourrait très bien être remplacé par un autre SGBD, tant que le langage qui est utilisé de PHP au SGBD reste le SQL. Structure d'une base de données +++++++++++++++++++++++++++++++ Une base de données est composée de **tables**. Chaque table est composée de **champs**. Chaque champ contient plusieurs **valeurs**. Chaque **entrée** ou **enregistrement** correspond à ensemble comprenant une valeur pour chaque champ. .. tip:: Généralement, on crée une base de donnée spécifique à chaque site web. Mais il est aussi possible de partager une même base entre plusieurs sites. .. nextslide:: On peut voir une table comme un tableau de la forme : ============ =========== =========== =========== Identifiant Champ1 Champ2 ... ============ =========== =========== =========== 1 Valeur11 Valeur21 ... 2 Valeur12 Valeur22 ... 3 Valeur13 Valeur23 ... ... ... ... ... ============ =========== =========== =========== .. tip:: Le champ identifiant n'est pas obligatoire, mais il permet d'affecter une valeur unique à chaque enregistrement. L'utilisation d'identifiants permet aussi de lier les données de plusieurs tables entre elles. .. _gestion_bdd: Créer et gérer une base de données ================================== L'interface phpMyAdmin ++++++++++++++++++++++ Pour faciliter les opérations de gestion des bases de données, il existe un outil nommé **phpMyAdmin**, qui propose une interface de gestion Web des BDD sous la forme de pages PHP permettant (entre autres) : - la création/suppression de bases de données; - la création/modification/suppresion de tables; - la création/modification/suppresion de champs; - la création/modification/suppresion d'enregistrements; - la visualisation des données enregistrées; - l'importation/exportation de bases de données (complètes ou partielles). .. figure:: _static/bdd/phpmyadmin.png :alt: phpmyadmin .. _exo_phpmyadmin: Exercice ++++++++ L'objectif de cet exercice est de se familiariser avec l'interface phpMyAdmin. Instructions : #. Créez une table nommée "film" avec les champs "id" (entier, clé primaire), "nom" (chaîne de caractères), "annee" (entier) , "score" (nombre flottant) et "nbVotants" (nombre entier). #. Créez un film dans la table : Aliens (1986) aura l'identifiant 13, un score de 8.3 et un nombre de votants de 5811. #. Remplissez la table en important `d'autres données <_static/bdd/films.sql>`_. Interroger une base de données ============================== Se connecter à une base de donnnées +++++++++++++++++++++++++++++++++++ Première étape nécessaire : connexion Connexion : processus d'authentification qui permet de s'assurer que seuls les utilisateurs autorisés peuvent accéder aux données et/ou les modifier Les SGBD utilisent un vocabulaire spécifique relatif au processus de connexion : * **l'hôte** est l'adresse du serveur qui héberge la base de données * **la base** est le nom de la base de données à laquelle on souhaite se connecter * **user** est l'identifiant de l'utilisateur * **password** est le mot de passe de cet utilisateur (connexion sécurisée). Type de connexion ----------------- PHP propose plusieurs fonctionnalités intégrées pour se connecter à une base de données via un SGBD. Les évolutions successives de PHP explique l'existence de 3 extensions : * ``mysql_`` : API MySQL originelle => Dépréciée depuis PHP 5.5, supprimée en PHP 7 * ``mysqli_`` : API MySQL améliorée (**i** pour improved) * ``PDO`` : PHP Data Objects - API bdd abstraite ``PDO`` constitue la concrétisation d'un effort d'unification entre les différents SGBD : * Générique (différents SGBD => même code) * Optimisée pour l'orienté objet Se connecter en PHP ------------------- .. code-block:: php PDO::ERRMODE_EXCEPTION)); return $bdd; } catch (Exception $e) { exit('Erreur : '.$e->getMessage()); } } ?> .. _requete_bdd: Faire une requête sur une base de données +++++++++++++++++++++++++++++++++++++++++ Après s'être connecté à une base de données, il est possible d'accéder à son contenu, en suivant le protocole suivant : #. On **interroge** une base de données grâce à une **requête** qui spécifie quelle(s) donnée(s) de quelle(s) tables on souhaite récupérer. #. Le SGBD se charge de **filtrer** et **trier** les données correspondantes à la requête et les **collecte** dans une structure de données exploitable en PHP (c'est-à-dire un tableau). Ecrire une requête ------------------ Les requêtes sont interprétées par le SGBD, elles sont dont formulées dans le langage qu'il manipule, c'est à dire le SQL. Le langage SQL est dédié à l'écriture de requêtes. Sa syntaxe, sous forme de chaîne de caractères, permet de créer des requêtes complexes à partir de quelques mots clés. Les instructions SQL -------------------- Le langage SQL est articulé autour de mots-clés facilement interprétables, exemple : * ``SELECT`` : sélection des champs * ``FROM`` : choix de la table * ``WHERE`` : condition (peut être composée avec ``AND``/``OR``) * ``ORDER BY`` : règle de tri (par champ) * ``LIMIT`` : limite du nombre d'enregistrements * ``INSERT INTO`` : insertion d'un enregistrement * ``VALUES`` : précise les valeurs à enregistrer * ``UPDATE`` : mise à jour d'un enregistrement * ``DELETE`` : suppression d'un enregistrement .. note:: Cette liste n'est pas exhaustive : il est possible de tout faire avec des requêtes SQL (y compris création/suppresion de table et même de BDD). Requête de lecture ------------------ L'ordre des mots-clés est figé, mais il n'est pas obligatoire de tous les utiliser. Un exemple d'une requête de lecture complète pourrait être : .. code-block:: sql SELECT champ1, champ2, champ3 FROM ma_table WHERE champ1='valeur' AND champ2 < 20 OR champ 3 > 0 ORDER BY champ2 DESC, champ3 ASC LIMIT 0, 5 .. tip:: Le sélecteur ``*`` permet de sélectionner tous les champs d'une table : ``SELECT *``. .. nextslide:: * Il est possible de ne sélectionner qu'une partie des champs d'une table. * Il est possible de sélectionner les champs de plusieurs tables. Dans ce cas, il faut écrire ``table.champ`` après le ``SELECT`` (pas obligatoire si les noms des champs diffèrent). * ``WHERE`` indique le début des conditions qu'il est possible de combiner avec les opérateurs ``AND`` et ``OR`` en plus des parenthèses. * Le tri peut se faire sur plusieurs champs, par ordre d'apparition après ``ORDER BY``. C'est l'ordre alphabétique qui s'applique sur un champs texte. * La limite du nombre d'enregistrement s'écrit : ``LIMIT start, nb`` ; il y aura donc ``nb`` enregistrements sélectionnés à partir de ``start``. Si l'on omet ``start``, la requête retournera ``nb`` enregistrements à partir du premier (**dans l'ordre défini par le tri**). Requête d'écriture ------------------ D'autres mots-clés permettent d'ajouter/modifier/supprimer un enregistrement dans une table. Exemple d'**insertion** : .. code-block:: sql INSERT INTO ma_table(champ1, champ2, champ3) VALUES(valeur1, valeur2, valeur3) Ou : .. code-block:: sql INSERT INTO ma_table VALUES(valeur1, valeur2, valeur3) -- Dans ce cas, toutes les colonnes doivent être -- spécifiées et l'ordre doit être respecté. .. nextslide:: .. warning:: Les SGBD sont très sécurisés au niveau des requêtes d'insertion. Aussi, la requête se traduira systématiquement par un échec dans le cas d'oubli d'un des champs ou de types de paramètres incompatibles. .. note:: Si un champ de la table à été déclaré comme une clé primaire (identifiant) avec la propriété ``auto_increment``, il n'est pas nécessaire de faire apparaître ce champ ni sa valeur dans une requête d'insertion. .. nextslide:: Exemple de **modification** : .. code-block:: sql UPDATE ma_table SET champ2 = valeur2, champ3 = valeur3 WHERE champ1 = valeur1 .. warning:: Les requêtes de modifications utilisent aussi une partie sélection. La requête n'aboutira pas si la condition du ``WHERE`` n'est pas satisfaite. .. note:: Il est possible de modifier plusieurs enregistrements en une seule requête : c'est la condition de sélection qui fait la différence. .. nextslide:: Exemple de **suppression** : .. code-block:: sql DELETE FROM ma_table WHERE champ1 = valeur1 .. warning:: Les suppressions ne sont **pas annulables**. Attention : sans la condition ``WHERE`` tous les enregistrements de la table seront supprimés ! .. _exo_sql: Exercice -------- Depuis phpMyAdmin, il est possible de taper directement des requêtes SQL et d'afficher le résultat retourné. #. Accédez à votre base de données de l'`exercice précédent`:ref: #. Depuis le formulaire de requêtes de phpMyAdmin, écrire une requête pour récupérer le nom de tous les films #. Ecrire une requête permettant de récupérer au plus 5 films parmi les plus récents (<= 2010) #. Récupérez le nom et la note de tous les films et triez le résultat par note (croissante) #. Ajouter un nouveau film nommé "Alien", de 1979, qui aura l'identifiant 29 et sera noté 8.5 pour un nombre de votants de 4828. .. _lecture_bdd: Lire les données d'une base de données --------------------------------------- La lecture de données depuis une BDD s'exécute suivant ce protocole : #. Connexion à la BDD, #. Préparation de la requête, #. Interrogation de la BDD via une requête SQL, #. Récupération de la réponse complète, #. Lecture enregistrement par enregistrement, #. Fin de la lecture et libération de la ressource. Exemple générique ----------------- .. code-block:: php :linenos: prepare('...'); // requête SQL $query->execute(...); // paramètres et exécution while ($data = $query->fetch()) // lecture par ligne { ... // traitement de l'enregistrement } // fin des données $query->closeCursor(); ?> .. nextslide:: Quelques remarques : * Dans la requête, si on veut injecter des paramètres, il faut le spécifier par le caractère anonyme ``?`` ou un identifiant précédé par ``:``. * La fonction ``execute()`` exécute la requête avec les paramètres fournis sous la forme d'un tableau simple (paramètres anonymes) ou associatif (paramètres identifés). Il n'est pas nécessaire de préciser de paramètres si la requête SQL n'en comporte pas. * La fonction ``fetch()`` retourne un tableau associatif dont les clés correspondent aux champs sélectionnés par la requête. * Lorsqu'il n'y a plus d'entrée, l'affectation dans le ``while`` retourne faux : on sort de la boucle. * La fonction ``closeCursor()`` permet de libérer la ressource lorqu'on a fini les traitements sur les données retournées par le SGBD. .. nextslide:: .. warning:: Une faille connue nommée "injection SQL" peut être exploitée lorsque l'on utilise des données entrées par l'utilisateur dans des requêtes SQL. Pour s'en prémunir, il **ne faut pas** injecter les paramètres à la main avec des concaténations. À la place on les passera **TOUJOURS** via les fonctions ``prepare()`` et ``execute()``. Requête sans paramètres ----------------------- .. code-block:: php :linenos: prepare('SELECT * FROM ma_table'); $query->execute(); ... ?> .. note:: Pour gagner du temps, il est aussi possible d'utiliser la fonction ``exec()`` qui prend en paramètre une requête, et s'applique sur l'objet BDD : ``$query = $bdd->exec('...');``. Attention : n'utilisez la fonction ``exec()`` que si la requête ne comporte pas de paramètre (pas de variable PHP) pour éviter la faille d'injection SQL. Requête avec paramètres anonymes -------------------------------- .. code-block:: php :linenos: prepare('SELECT champ1, champ2 FROM ma_table WHERE champ1 = ? AND champ3 <= ? ORDER BY champ2'); $query->execute(array($valeur1, $valeur2)); ... ?> Requête avec paramètres nommés ------------------------------ .. code-block:: php :linenos: prepare('SELECT champ1, champ2 FROM table WHERE champ1 = :valeur1 AND champ3 <= :valeur2 ORDER BY champ2'); $query->execute(array('valeur1' => $valeur1, 'valeur2' => $valeur2)); ... ?> .. _exo_requete: Exercice -------- #. Créer une page contenant la fonction de connexion à la BDD films #. Récuperez la liste des films avec la requête adéquate #. Afficher le résultat .. _ecriture_bdd: Ecrire des données dans une base de donnnées -------------------------------------------- L'écriture de données dans une BDD se fait en suivant les étapes suivantes : #. Connexion à la BDD, #. Préparation de la requête, #. Exécution de la requête. Trois actions sont possibles pour l'écriture : insertion, modification ou suppression d'un enregistrement. Exemple générique ----------------- Avec ou sans paramètres : .. code-block:: php :linenos: prepare('...'); // requête SQL $query->execute(...); // paramètres et exécution ?> .. nextslide:: Raccourci (sans paramètres uniquement) : .. code-block:: php :linenos: exec('...'); // requête SQL ?> .. note:: Pour effectuer chacune des opérations (ajout, modification, suppression), il suffit de choisir la bonne requête (``INSERT INTO, UPDATE SET, DELETE FROM``); .. _exo_ecriture: Exercice --------- #. Créez une page qui affiche un formulaire simple permettant l'ajout d'un film #. Les données envoyées doivent être insérées dans la table films de votre BDD. #. Ajoutez les tests nécessaires au traitement des données entrées #. Si l'utilisateur entre un nom de film déja existant dans la table, appliquer une requête de modification avec les nouvelles données (empêchez la création de doublons). .. _jointure_bdd: Les requêtes de jointure ------------------------ Un des intérêts majeurs des BDD est de pouvoir lier des données entre-elles afin de leur donner une sémantique plus forte. L'utilisation d'identifiants uniques (**clés primaires**) pour chaque enregistrement, permet leur réutilisation dans d'autres tables. On les appelle alors des **clés étrangères**. Exemple, table "films": ============ =========== =========== =========== id titre annee ... ============ =========== =========== =========== 1 Titanic 1997 ... 2 Star Wars 1977 ... 3 Braveheart 1995 ... ... ... ... ... ============ =========== =========== =========== .. nextslide:: Exemple, table "acteurs": ============ =========== =============== =========== id nom prenom ... ============ =========== =============== =========== 1 Di Caprio Leonardo ... 2 Winslet Kate ... 3 Gibson Mel ... ... ... ... ... ============ =========== =============== =========== Les champs ``film.id`` et ``acteur.id`` sont les clés primaires de leurs tables respectives. .. nextslide:: Exemple de table de jointure, table "Casting" : ============ =========== film_id acteur_id ============ =========== 1 1 1 2 2 3 ... ... ============ =========== Ici, les champs ``film_id`` et ``acteur_id`` deviennent clés étrangères et permettent de lier les tables "acteurs" et "films". Pour pouvoir accéder aux données présentes dans des tables jointes de la sorte, il faut utiliser les **requêtes de jointure**. Aller plus loin avec les requêtes SQL ------------------------------------- **Les alias :** Pour écrire une requête qui intervient sur plusieurs tables jointes, il est possible de simplifier l'écriture en définissant des **alias**. Deux écritures sont possibles après la clause ``FORM`` : .. code-block:: sql SELECT * FROM ma_table AS alias Ou .. code-block:: sql SELECT * FROM ma_table t .. note:: La première version reste la plus lisible. Notez que par convention, les noms des tables s'écrivent en minuscules. Une bonne pratique est de conserver ce format mais de réduire leur taille en utilisant des alias. .. nextslide:: **Les requêtes de jointure** s'écrivent en utilisant le mot clé ``JOIN``. La clause ``ON`` permet de définir la condition de la jointure (c'est à dire la correspondance entre les clés primaires/étrangères). Exemple : .. code-block:: sql SELECT * FROM table1 INNER JOIN table2 ON table1.id_champ1 = table2.id_champ2 .. tip:: On peut imbriquer plusieurs jointures lorsque plus de deux tables sont liées. Pour cela, on précise les conditions de jointures les unes après les autres (``INNER JOIN ... ON ... INNER JOIN ... ON``). .. _exo_jointure: Projet : site de films ====================== v1.0 ++++ * Interface se rapprochant de la présentation ci-dessous (vous pouvez utiliser d'autres couleurs et formatages !) * Visualisation des films, acteurs, formulaire ajout de film * Uniquement du PHP (**indenté !!**), HTML et CSS * pas de framework, ni de moteur de templates * passer le valideur HTML5 et CSS3 sans erreur * placer les fichiers dans le répertoire public_html/PHP/projet1 du login hébergeant le projet .. nextslide:: .. figure:: _static/projet/interface.png :alt: interface-projet