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 ?§

client-serveur-mysql
  1. Le serveur interprète le script PHP
  2. PHP interroge (requête) la base de données par MariaDB/MySQL
  3. MariaDB/MySQL renvoie les données en réponse à la requête
  4. 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.

Astuce

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.

On peut voir une table comme un tableau de la forme :

Identifiant Champ1 Champ2 ...
1 Valeur11 Valeur21 ...
2 Valeur12 Valeur22 ...
3 Valeur13 Valeur23 ...
... ... ... ...

Astuce

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.

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).
phpmyadmin

Exercice§

L’objectif de cet exercice est de se familiariser avec l’interface phpMyAdmin.

Instructions :

  1. 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).
  2. 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.
  3. Remplissez la table en important d’autres données.

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§

<?php
 function connectDb(){
      $host = 'localhost'; // ou sql.hebergeur.com
      $user = 'root';      // ou login
      $pwd = '';      // ou xxxxxx
      $db = 'nom_bdd';
  try {
       $bdd = new PDO('mysql:host='.$host.';dbname='.$db.
                      ';charset=utf8', $user, $pwd,
                  array(PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION));
       return $bdd;
      } catch (Exception $e) {
       exit('Erreur : '.$e->getMessage());
  }
 }
?>

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 :

  1. 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.
  2. 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 :

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

Astuce

Le sélecteur * permet de sélectionner tous les champs d’une table : SELECT *.

  • 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 :

INSERT INTO ma_table(champ1, champ2, champ3)
VALUES(valeur1, valeur2, valeur3)

Ou :

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é.

Avertissement

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.

Exemple de modification :

UPDATE ma_table SET champ2 = valeur2, champ3 = valeur3
WHERE champ1 = valeur1

Avertissement

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.

Exemple de suppression :

DELETE FROM ma_table WHERE champ1 = valeur1

Avertissement

Les suppressions ne sont pas annulables.

Attention : sans la condition WHERE tous les enregistrements de la table seront supprimés !

Exercice§

Depuis phpMyAdmin, il est possible de taper directement des requêtes SQL et d’afficher le résultat retourné.

  1. Accédez à votre base de données de l’exercice précédent
  2. Depuis le formulaire de requêtes de phpMyAdmin, écrire une requête pour récupérer le nom de tous les films
  3. Ecrire une requête permettant de récupérer au plus 5 films parmi les plus récents (<= 2010)
  4. Récupérez le nom et la note de tous les films et triez le résultat par note (croissante)
  5. 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.

Lire les données d’une base de données§

La lecture de données depuis une BDD s’exécute suivant ce protocole :

  1. Connexion à la BDD,
  2. Préparation de la requête,
  3. Interrogation de la BDD via une requête SQL,
  4. Récupération de la réponse complète,
  5. Lecture enregistrement par enregistrement,
  6. Fin de la lecture et libération de la ressource.

Exemple générique§

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
<?php
 $bdd = connectDb(); //connexion à la BDD
 $query = $bdd->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();
?>

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.

Avertissement

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§

1
2
3
4
5
6
<?php
 ...
 $query = $bdd->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§

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
<?php
 ...
 $query = $bdd->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§

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
<?php
 ...
 $query = $bdd->prepare('SELECT champ1, champ2
                         FROM table
                         WHERE champ1 = :valeur1
                         AND champ3 <= :valeur2
                         ORDER BY champ2');
 $query->execute(array('valeur1' => $valeur1,
                       'valeur2' => $valeur2));
 ...
?>

Exercice§

  1. Créer une page contenant la fonction de connexion à la BDD films
  2. Récuperez la liste des films avec la requête adéquate
  3. Afficher le résultat

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 :

  1. Connexion à la BDD,
  2. Préparation de la requête,
  3. 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 :

1
2
3
4
5
<?php
 $bdd = connectDb(); //connexion à la BDD
 $query = $bdd->prepare('...'); // requête SQL
 $query->execute(...); // paramètres et exécution
?>

Raccourci (sans paramètres uniquement) :

1
2
3
4
<?php
 $bdd = connectDb(); //connexion à la BDD
 $query = $bdd->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);

Exercice§

  1. Créez une page qui affiche un formulaire simple permettant l’ajout d’un film
  2. Les données envoyées doivent être insérées dans la table films de votre BDD.
  3. Ajoutez les tests nécessaires au traitement des données entrées
  4. 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).

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 ...
... ... ... ...

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.

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 :

SELECT *
FROM ma_table AS alias

Ou

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.

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 :

SELECT *
FROM table1
INNER JOIN table2
    ON table1.id_champ1 = table2.id_champ2

Astuce

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).

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
interface-projet