PHP Data Objects (PDO)

Dernière mise à jour le 3 novembre 2022

Création d'une base de données de test

Commençons par créer une base de données pour nos exemples.

Il n'y aura qu'une table contenant des données sur des pays. Ces données seront :

le nom du pays;
sa capitale;
sa population;
sa superficie (en km²).

Il y a de nombreuses façons (logiciels) de créer et de gérer une base de données MariaDB. Si vous êtes à l'aise avec le langage SQL, vous pouvez utiliser directement le client MySQL sur le serveur.

On le lance de la façon suivante (on suppose que vous êtes l'usager MySQL "patoche") :

$ mysql -u patoche -p
Enter password: mot_de_passe_de_patoche
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 133
Server version: 10.3.34-MariaDB-0ubuntu0.20.04.1 Ubuntu 20.04

Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

MariaDB [(none)]>

Vous pouvez alors entrer directement les commandes SQL pour gérer la base de données.

Les commandes SQL suivantes peuvent être copiées et collées dans l'interface du client MySQL :

DROP DATABASE monde;

CREATE DATABASE monde;

USE monde;

CREATE TABLE pays (
  nom VARCHAR(50) NOT NULL,
  capitale VARCHAR(50) NOT NULL,
  population INT NOT NULL,
  superficie INT NOT NULL,
  PRIMARY KEY (nom)
);

INSERT INTO pays (nom, capitale, population, superficie)
    VALUES ('Algérie', 'Alger', '44487616', '2381741');
INSERT INTO pays (nom, capitale, population, superficie)
    VALUES ('Cuba', 'La Havane', '11032343', '110860');
INSERT INTO pays (nom, capitale, population, superficie)
    VALUES ('France', 'Paris', '66014000', '672051');
INSERT INTO pays (nom, capitale, population, superficie)
    VALUES ('Gondor', 'Minas Tirith', '3198', '12173');
INSERT INTO pays (nom, capitale, population, superficie)
    VALUES ('Japon', 'Tokyo', '124780000', '377975');
INSERT INTO pays (nom, capitale, population, superficie)
    VALUES ('Québec', 'Québec', '8501833', '1542056');

Si tout a bien fonctionné on peut afficher le contenu de l'unique table :

MariaDB [monde]> select * from pays;
+----------+--------------+------------+-------------+
| nom      | capitale     | population | superficie |
+----------+--------------+------------+-------------+
| Algérie  | Alger        |   44487616 |     2381741 |
| Cuba     | La Havane    |   11032343 |      110860 |
| France   | Paris        |   66014000 |      672051 |
| Gondor   | Minas Tirith |       3198 |       12173 |
| Japon    | Tokyo        |  124780000 |      377975 |
| Québec   | Québec       |    8501833 |     1542056 |
+----------+--------------+------------+-------------+
6 rows in set (0.001 sec)

MariaDB [monde]>

Dans le cas (peu probable) où vous ne seriez pas à l'aise avec le langage SQL, il y a PhpMyAdmin que vous avez récemment installé.

Connexion à la base de données

PHP Data Objects (ou simplement PDO) est une extension qui définit l'interface pour accéder à une base de données à l'aide de PHP.

Il s'agit d'une bibliothèque orientée objet.

PDO constitue une couche d'abstraction qui agit entre l'application PHP et un système de gestion de base de données (SGDB) tel que MySQL, MariaDB, SQLite, PostgreSQL, Oracle, etc.

Toute application utilisant une bases de données commence par la connexion à celle-ci.

Exemple :

$host    = ''; // 127.0.0.1 si la BD et l'application sont sur le même serveur
$db      = ''; // nom de la base de données
$user    = '';
$pass    = '';
$charset = 'utf8';

$dsn = "mysql:host=$host;dbname=$db;charset=$charset";

$options = [
    PDO::ATTR_ERRMODE            => PDO::ERRMODE_EXCEPTION,
    PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC,
    PDO::ATTR_EMULATE_PREPARES   => false,
];

try {
    $pdo = new PDO($dsn, $user, $pass, $options);
    // echo "Connexion établie";
} catch (\PDOException $e) {
    throw new \PDOException($e->getMessage(), (int)$e->getCode());
}

Remarques :

Le "data source name" ($dsn) ne doit pas contenir d'espaces.
Le même objet PDO doit être utilisé le plus souvent possible pour limiter le nombre de connexions à la base de données. Notez toutefois qu'il est détruit automatiquement à la fin de l'exécution d'un script.

Exécuter une requête

Il existe deux façons d'exécuter une requête avec PDO : la méthode PDO:query() et les requêtes préparées.

PDO::query()

Si aucune variable n'est utilisée dans la requête, on utilise généralement la méthode PDO:query().

Cette méthode exécute la requête et retourne un objet de la classe PDOStatement duquel on peut extraire le résultat.

La méthode fecth() retourne un enregistrement sous forme de tableau associatif.

Exemple :

$stmt = $pdo->query('SELECT nom FROM pays');

// tant qu'il y a des enregistrements (lignes)
while ($row = $stmt->fetch()) {
  echo $row['nom'] . "<br>";
}

Sortie :

Algérie
Cuba
...

Autres exemple :

$stmt = $pdo->query('SELECT * FROM pays');

while ($row = $stmt->fetch()) {
  echo $row['capitale'] . " est la capitale d'un pays nommé " . $row['nom'] . "<br>";
}

Sortie :

Alger est la capitale d'un pays nommé Algérie
La Havane est la capitale d'un pays nommé Cuba
...

Requêtes préparées

La principale raison pour laquelle on utilise les requêtes préparées est de se protéger des injections SQL. Ce sujet sera abordé dans un autre document.

Les requêtes préparées constituent en fait la seule bonne stratégie si une variable doit être incluse dans la requête.

Il s'agit tout simplement de remplacer celle-ci par un espace réservé (placeholder en anglais) et de fournir séparément à PDO la requête et la variable.

On utilise pour cela les méthodes prepare() et execute().

Imaginons que les variables $population et $superficie sont des données de forumulaire. Le code suivant :

$sql = 'SELECT * FROM pays WHERE population > $population AND superficie > $superficie';

peut être remplacé par :

// paramètres positionnels

$sql = 'SELECT * FROM pays WHERE population > ?  AND superficie > ?';

ou encore par :

// paramètres nommés

$sql = 'SELECT * FROM pays WHERE population > :population AND superficie > :superficie';

Une requête incorporant des espaces réservés doit d'abord être préparée avec la méthode prepare(). Cette fonction retourne aussi un objet de la classe PDOStatement, mais sans aucune donnée.

Exemple :

$stmt = $pdo->prepare($sql);

Ensuite, la requête est exécutée avec la méthode execute() en lui passant les variables sous forme de tableau. Les données résultantes (si applicable) pourront ensuite être obtenues de l'objet PDOStatement.

Exemple :

// paramètres positionnels

$pop = 1000000; // population de plus d'un million d'habitants
$sup = 1500000; // superficie de plus d'un million et demie de km carrés

$sql = 'SELECT nom FROM pays WHERE population > ?  AND superficie > ?';

$stmt = $pdo->prepare($sql);

// tableau régulier
$stmt->execute([$pop, $sup]); // retourne un tableau à deux dimensions où chaque ligne est
                              // elle-même un tableau (voir plus loin la sortie dans le client)

while ($row = $stmt->fetch()) {
  echo $row['nom'] . "<br>";
}

Sortie :

Algérie
Québec

La même requête dans le client MySQL :

MariaDB [monde]> SELECT * FROM pays WHERE population > 1000000 AND superficie > 1500000;
+----------+----------+------------+------------+
| nom      | capitale | population | superficie |
+----------+----------+------------+------------+
| Algérie  | Alger    |   44487616 |    2381741 |
| Québec   | Québec   |    8501833 |    1542056 |
+----------+----------+------------+------------+
2 rows in set (0.002 sec)

MariaDB [monde]>

Ou encore :

// paramètres nommés

...
$sql = 'SELECT nom FROM pays WHERE population > :population  AND superficie > :superficie';

$stmt = $pdo->prepare($sql);

// tableau associatif (ordre sans importance)
$stmt->execute(['superficie' => $sup, 'population' => $pop]);
...

Obtenir les résultats avec `foreach`

Puisque la classe PDOStatement implémente l'interface Traversable, la façon la plus simle d'obtenir toutes les rangées est d'utiliser le foreach plutôt que la boucle while.

Exemple :

...

// chaque rangée est un tableau associatif
foreach ($stmt as $row) {
    echo $row['nom'] . "<br>";
}

...

Obtenir les résultats avec fetch()

La méthode fetch() permet d'obtenir les résultats une ligne à la fois. Cette méthode est très souple, car un paramètre permet d'obtenir les résultats sous différentes formes :

Paramètre	Type(s) retourné(s)
PDO::FETCH_NUM	tableau régulier
PDO::FETCH_ASSOC	tableau associatif
PDO::FETCH_BOTH	les deux types de tableaux
PDO::FETCH_OBJ	objet
PDO::FETCH_LAZY	tous les types précédents

Cette méthode permet d'éviter le foreach ou le while quand on sait que le résultat ne comporte qu'une ligne.

Exemple de récupération dans un tableau indexé numériquement (tableau ordinaire) :

$sql = "SELECT population, superficie FROM pays WHERE nom = ?";
$nom = "France";
$stmt = $pdo->prepare($sql);
$stmt->execute([$nom]);
$row = $stmt->fetch(PDO::FETCH_NUM);

echo "Il y a en " . $nom . " " . $row[0] . " habitants sur " . $row[1] . "km<sup>2</sup><br>";

Sortie :

Il y a en France 66014000 habitants sur 672051km²

Avec un tableau associatif :

$row = $stmt->fetch(PDO::FETCH_ASSOC);

echo "Il y a en " . $nom . " " . $row['population']
    . " habitants sur " . $row['superficie'] . "km<sup>2</sup><br>";

Avec les deux types de tableaux :

$row = $stmt->fetch(PDO::FETCH_BOTH);

echo "Il y a en " . $nom . " " . $row['population']
    . " habitants sur " . $row['1'] . "km<sup>2</sup><br>";

Dans l'exemple précédent, il devient possible d'accéder aux éléments du tableau par un indice numérique ou par une clé (chaîne de caractères).

La constante PDO::FETCH_OBJ permet de créer un objet automatiquement à partir des valeurs obtenues. Nous y reviendrons plus tard.

Exemples de requêtes avec PDO

Pour terminer voici quelques exemples qui pourraient vous aider.

SELECT

Si la requête ne contient aucune variable, la méthode query() est suffisante.

Dans l'exemple suivant, on suppose qu'il s'agit d'un employé permanent si ce dernier travaille dans l'entreprise depuis au moins 5 ans ou q'il a été engagé avant 2010 :

function getEmployesPermanents() {
  $pdo = getPdo();
  $sql = "SELECT * FROM employe WHERE nb_annees >= 5 OR date_engagement < 2010";
  $stmt = $pdo->query($sql);
  return $stmt;
}

$resultat = getEmployesPermanents();
foreach ($resultat as $rangee) {
    echo "$rangee['nom'], $rangee['prenom']<br>";
}

Si la requête contient une ou plusieurs variables, surtout en provenance d'un formulaire, une requête préparée devrait être utilisée pour se protéger des injections SQL.

// dangereux, on ne sait pas ce que contiennent vraiment les paramètres

$sql = "SELECT * FROM employe WHERE nb_annees >= '$nbAnnees' OR date_engagement < '$dateEngagement'";

Une requête préparée peut utiliser des paramètres positionnels ou nommés. Voyons d'abord un exemple avec des paramètres positionnels :

// avec paramètres positionnels

function getEmployesPermanents($nbAnnees, $dateEngagement) {
  $pdo = getPdo();
  $sql = "SELECT * FROM employe WHERE nb_annees >= ? OR date_engagement < ?";
  $stmt= $pdo->prepare($sql);
  $stmt->execute([$nbAnnees, $dateEngagement]);
  return $stmt;
}

Les requêtes préparées avec paramètres positionnels font un code un peu plus court, mais celles avec paramètres nommés ont l'avantage de ne pas accorder d'importance à l'ordre des paramètres :

// avec paramètres nommés

function getEmployesPermanents($nbAnnees, $dateEngagement) {
  $pdo = getPdo();
  $sql = "SELECT * FROM employe WHERE nb_annees >= :annees OR date_engagement < :date";
  $stmt= $pdo->prepare($sql);
  $stmt->execute(['date' => $dateEngagement, 'annees' => $nbAnnees]);
  return $stmt;
}

INSERT

function ajouter($nom, $prenom) {
  $pdo = getPdo();
  try {
    $sql = "INSERT INTO employe (nom, prenom) VALUES (?, ?)";
    $stmt= $pdo->prepare($sql);
    $stmt->execute([$nom, $prenom]);
  } catch (Exception $e) {
    echo "Erreur...";
    exit;
  }
}

UPDATE

function modifier($id, $nom, $prenom) {
  $pdo = getPdo();
  try {
    $sql = "UPDATE employe SET nom=?, prenom=? WHERE id=?";
    $stmt= $pdo->prepare($sql);
    $stmt->execute([$nom, $prenom, $id]);
  } catch (Exception $e) {
    echo "Erreur...";
    exit;
  }
}

DELETE

function supprimer($id) {
  $pdo = getPdo();
  $sql = "DELETE FROM employe WHERE id=?";
  $stmt= $pdo->prepare($sql);
  $stmt->execute([$id]);
}

Sauvegarder et restaurer une base de données

Sauvegarder et restaurer une base de données MySQL est très simple. Vous trouverez ici l'essentiel de ce qu'il faut savoir.

Sauvegarder

La syntaxe de la commande mysqldump est la suivante :

mysqldump -u usager -p base_de_données > fichier_de_sauvegarde

Dans l'exemple suivant, nous sauvegardons la totalité (structure et données) de la base de données "lotr". Il est possible de sauvegarder seulement les données, seulement une table, etc., mais en général nous voulons tout sauvegarder de façon à pouvoir ensuite réinstaller notre VPS à partir de zéro.

Notez bien que l'opération se fait au niveau du shell et qu'il n'est pas nécessaire "d'entrer" comme tel dans le client MySQL.

Exemple :

$ sudo mysqldump -u patoche -p lotr > lotr_2022-11-04.sql

Le suffixe .sql du fichier n'est pas obligatoire, mais nous renseigne sur son contenu.

N'oubliez pas ensuite de télécharger le fichier de sauvegared sur autre ordinateur!

Pour une seule table il faut spécifier le nom de la base de données et ensuite celui de la table à sauvegarder :

$ sudo mysqldump -u patoche -p lotr personnage > table_personnage.sql

Restaurer

Avant de restaurer une base de données, vous devez la détruire sur le serveur, puis la recréer :

MariaDB [(none)]> drop database lotr;
Query OK, 24 rows affected (0.35 sec)
MariaDB [(none)]> create database lotr;
Query OK, 1 row affected (0.01 sec)
MariaDB [(none)]> exit;
Bye
$

On procède ensuite directement avec la commande mysql. Exemple :

$ sudo mysql -u patoche -p lotr < lotr_2022-11-04.sql

Et voilà, ce n'est pas plus compliqué!

Questions et exercices

Question 1

Expliquez à quel moment il est préférable d'utiliser les méthodes query(), prepare() ou execute().

Question 2

Écrivez le code d'une méthode qui effectue un "select" avec des paramètres positionnels.

Question 3

Écrivez à nouveau la méthode de la question précédente, cette fois avec des paramètres nommés.

Question 4

Savez-vous comment vous connecter au client MySQL de votre VPS dans une console? Vous devriez!

Question 5

Donnez la différence entre les trois appels suivants à la méthode fetch() :

$stmt->fetch(PDO::FETCH_NUM);
$stmt->fetch(PDO::FETCH_ASSOC);
$stmt->fetch(PDO::FETCH_BOTH);

Question 6

Donnez le code que l'usager MySQL "bob" devra écrire dans la console pour sauvegarder sa base de données "ma_bd".

Question 7

Donnez le code que Bob devra écrire dans la console pour restaurer sa base de données.

Question 8

Expliquez en quelques mots ce qu'est une injection SQL.

PHP Data Objects (PDO)

Création d'une base de données de test

Connexion à la base de données

Exécuter une requête

PDO::query()

Requêtes préparées

Obtenir les résultats avec foreach

Obtenir les résultats avec fetch()

Exemples de requêtes avec PDO

SELECT

INSERT

UPDATE

DELETE

Sauvegarder et restaurer une base de données

Sauvegarder

Restaurer

Questions et exercices

Question 1

Question 2

Question 3

Question 4

Question 5

Question 6

Question 7

Question 8

Obtenir les résultats avec `foreach`