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

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

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.

Structure d'une base de données§

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

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

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

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

Requête de lecture§

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

Requête d'écriture§

Requête d'écriture§

Exemple de modification :

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

Requête d'écriture§

Exemple de suppression :

DELETE FROM ma_table WHERE champ1 = valeur1

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();
?>

Exemple générique§

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.

Exemple générique§

Requête sans paramètres§

1
2
3
4
5
6

<?php
 ...
 $query = $bdd->prepare('SELECT * FROM ma_table');
 $query->execute();
 ...
?>

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

Exemple générique§

Raccourci (sans paramètres uniquement) :

1
2
3
4

<?php
 $bdd = connectDb(); //connexion à la BDD
 $query = $bdd->exec('...'); // requête SQL
?>

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

Les requêtes de jointure§

Exemple, table "acteurs":

Les champs film.id et acteur.id sont les clés primaires de leurs tables respectives.

Les requêtes de jointure§

Exemple de table de jointure, table "Casting" :

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

Aller plus loin avec les requêtes SQL§

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

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

v1.0§