Comment utiliser l'extension PHP PDO pour exécuter MySQL en PHP sur Ubuntu 18.04

L’auteur a choisi Open Sourcing Mental Illness pour recevoir un don dans le cadre du programme Write for DOnations.

Introduction

Une transaction MySQL est un groupe de commandes SQL logiquement liées qui sont exécutées dans la base de données comme une seule unité. Les transactions sont utilisées pour faire respecter la conformité ACID ( Atomicité, Cohérence, Isolation et Durabilité) dans une application. Il s’agit d’un ensemble de normes qui régissent la fiabilité des opérations de traitement dans une base de données.

L’atomicité garantit le succès des opérations connexes ou un échec complet si une erreur se produit. La cohérence garantit la validité des données soumises à la base de données selon une logique commerciale définie. L’isolation est l’exécution correcte de transactions simultanées garantissant que les effets des différents clients se connectant à une base de données n’affectent pas les autres. La durabilité garantit que les transactions logiquement liées restent en permanence dans la base de données.

Les instructions SQL émises via une transaction doivent soit réussir, soit échouer complètement. Si l’une des requêtes échoue, MySQL annule les modifications et celles-ci ne sont jamais enregistrées dans la base de données.

Un bon exemple pour comprendre comment fonctionnent les transactions MySQL est un site web de commerce électronique. Lorsqu’un client passe une commande, l’application insère des enregistrements dans plusieurs tableaux, tels que : orders et orders_products, en fonction de la logique commerciale.  Les enregistrements multi-tableaux liés à une seule commande doivent être envoyés de manière atomique à la base de données en tant qu’unité logique unique.

Un autre cas d’utilisation est celui d’une application bancaire. Lorsqu’un client transfère de l’argent, deux transactions sont envoyées à la base de données. Le compte de l’expéditeur est débité et le compte du destinataire est crédité. Les deux transactions doivent être effectuées simultanément. Si l’une d’elles échoue, la base de données revient à son état initial et aucune modification ne doit être enregistrée sur le disque.

Dans ce tutoriel, vous utiliserez l’extension PHP PDO, qui fournit une interface permettant de travailler avec des bases de données en PHP, pour effectuer des transactions MySQL sur un serveur Ubuntu 18.04.

Conditions préalables

Avant de commencer, vous aurez besoin des éléments suivants :

• Un serveur Ubuntu 18.04 configuré en suivant la Configuration initiale du serveur avec Ubuntu 18.04, y compris un utilisateur sudo non root.
• Apache, MySQL et PHP installés sur votre système. Vous pouvez suivre le guide sur la façon d’installer les piles Linux, Apache, MySQL, PHP (LAMP) sur Ubuntu 18.04. Vous pouvez sauter l’étape 4 (configuration des hôtes virtuels) et travailler directement avec les paramètres par défaut d’Apache.

Étape 1 — Création d’un exemple de base de données et de tableaux

Vous allez d’abord créer une base de données type et ajouter quelques tableaux avant de commencer à travailler avec les transactions MySQL. Tout d’abord, connectez-vous à votre serveur MySQL en tant que root :

sudo mysql -u root -p

Lorsque vous y êtes invité, entrez votre mot de passe MySQL en tant que root et appuyez sur ENTER (ENTRÉE) pour continuer. Ensuite, créez une base de données. Aux fins de ce tutoriel, nous appellerons la base de données sample_store:

CREATE DATABASE sample_store;

Vous verrez le résultat suivant :

Output
Query OK, 1 row affected (0.00 sec)

Créez un utilisateur appelé sample_user pour votre base de données. N’oubliez pas de remplacer PASSWORD par une valeur forte :

CREATE USER 'sample_user'@'localhost' IDENTIFIED BY 'PASSWORD';

Donnez à votre utilisateur tous les privilèges pour la base de données sample_store :

GRANT ALL PRIVILEGES ON sample_store.* TO 'sample_user'@'localhost';

Enfin, rechargez les privilèges MySQL :

FLUSH PRIVILEGES;

Vous verrez la sortie suivante une fois que vous aurez créé votre utilisateur :

Output
Query OK, 0 rows affected (0.01 sec)
. . .

Avec la base de données et l’utilisateur en place, vous pouvez maintenant créer plusieurs tableaux pour montrer comment fonctionnent les transactions MySQL.

Déconnectez-vous du serveur MySQL :

QUIT;

Une fois que le système vous a déconnecté, vous verrez le résultat suivant :

Output
Bye.

Ensuite, connectez-vous avec les informations d’identification de l'utilisateur que vous venez de créer :

sudo mysql -u sample_user -p

Entrez le mot de passe de sample_user et appuyez sur ENTER (ENTRÉE) pour continuer.

Passez à la base de données sample_store pour qu’elle devienne la base de données actuellement sélectionnée :

USE sample_store;

Vous verrez la sortie suivante une fois qu’elle sera sélectionnée :

Output
Database Changed.

Ensuite, créez un tableau products :

CREATE TABLE products (product_id BIGINT PRIMARY KEY AUTO_INCREMENT, product_name VARCHAR(50), price DOUBLE) ENGINE = InnoDB;

Cette commande crée un tableau products avec un champ nommé product_id. Vous utilisez un type de données BIGINT qui peut accueillir une valeur importante allant jusqu’à 2^63-1. Vous utilisez ce même champ comme CLÉ PRIMAIRE pour identifier les produits de manière unique. Le mot-clé AUTO_INCREMENT indique à MySQL de générer la valeur numérique suivante lorsque de nouveaux produits sont insérés.

Le champ product_name est du type VARCHAR qui peut contenir jusqu’à 50 lettres ou chiffres maximum. Pour le prix du produit, vous utilisez un type de données DOUBLE pour tenir compte des formats à virgule flottante dans les prix avec des nombres décimaux.

Enfin, vous utilisez l’InnoDB comme MOTEUR car il prend confortablement en charge les transactions MySQL contrairement à d’autres moteurs de stockage tels que MyISAM.

Une fois que vous aurez créé votre tableau products, vous obtiendrez le résultat suivant :

Output
Query OK, 0 rows affected (0.02 sec)

Ensuite, ajoutez des éléments au tableau products en exécutant les commandes suivantes :

INSERT INTO products(product_name, price) VALUES ('WINTER COAT','25.50');
INSERT INTO products(product_name, price) VALUES ('EMBROIDERED SHIRT','13.90');
INSERT INTO products(product_name, price) VALUES ('FASHION SHOES','45.30');
INSERT INTO products(product_name, price) VALUES ('PROXIMA TROUSER','39.95');

Vous verrez une sortie semblable à celle qui suit après chaque opération INSERT :

Output
Query OK, 1 row affected (0.02 sec)
. . .

Ensuite, vérifiez que les données ont été ajoutées au tableau products :

SELECT * FROM products;

Vous verrez une liste des quatre produits que vous avez insérés :

Output
+------------+-------------------+-------+
| product_id | product_name      | price |
+------------+-------------------+-------+
|          1 | WINTER COAT       |  25.5 |
|          2 | EMBROIDERED SHIRT |  13.9 |
|          3 | FASHION SHOES     |  45.3 |
|          4 | PROXIMA TROUSER   | 39.95 |
+------------+-------------------+-------+
4 rows in set (0.01 sec)

Ensuite, vous allez créer un tableau customersqui contiendra des informations de base sur les clients :

CREATE TABLE customers (customer_id BIGINT PRIMARY KEY AUTO_INCREMENT, customer_name VARCHAR(50) ) ENGINE = InnoDB;

Comme dans le tableau products, vous utilisez le type de données BIGINT pour le customer_id et cela garantira que le tableau peut supporter un grand nombre de clients, jusqu’à 2^63-1 enregistrements. Le mot-clé AUTO_INCREMENT incrémente la valeur des colonnes une fois que vous avez inséré un nouveau client.

Comme la colonne customer_name accepte les valeurs alphanumériques, vous utilisez du type de données VARCHAR avec une limite de 50 caractères. Là encore, vous utilisez le MOTEUR de stockage InnoDB pour prendre en charge les transactions.

Après avoir exécuté la commande précédente pour créer le tableau customers, vous verrez le résultat suivant :

Output
Query OK, 0 rows affected (0.02 sec)

Vous ajouterez trois exemples de clients à la table. Exécutez les commandes suivantes :

INSERT INTO customers(customer_name) VALUES ('JOHN DOE');
INSERT INTO customers(customer_name) VALUES ('ROE MARY');
INSERT INTO customers(customer_name) VALUES ('DOE JANE');

Une fois que les clients ont été ajoutés, vous verrez un résultat semblable à ce qui suit :

Output
Query OK, 1 row affected (0.02 sec)
. . .

Ensuite, vérifiez les données dans le tableau customers :

SELECT * FROM customers;

Vous verrez une liste des trois clients :

Output
+-------------+---------------+
| customer_id | customer_name |
+-------------+---------------+
|           1 | JOHN DOE      |
|           2 | ROE MARY      |
|           3 | DOE JANE      |
+-------------+---------------+
3 rows in set (0.00 sec)

Ensuite, vous allez créer un tableau orders pour enregistrer les commandes placées par différents clients. Pour créer le tableau orders, exécutez la commande suivante :

CREATE TABLE orders (order_id BIGINT AUTO_INCREMENT PRIMARY KEY, order_date DATETIME, customer_id BIGINT, order_total DOUBLE) ENGINE = InnoDB;

Vous utilisez la colonne order_id comme CLE PRIMAIRE. Le type de données BIGINT vous permet d’enregistrer jusqu’à 2^63-1 commandes et s’incrémentera automatiquement après chaque insertion de commande. Le champ order_date contiendra la date et l’heure réelles de la commande et, par conséquent, vous utilisez le type de données DATETIME.  Le customer_id concerne le tableau customers que vous avez précédemment créé.

Vous verrez le résultat suivant :

Output
Query OK, 0 rows affected (0.02 sec)

Comme la commande d’un seul client peut contenir plusieurs articles, vous devez créer un tableau orders_products qui contiendra ces informations.

Pour créer le tableau orders_products, exécutez la commande suivante :

CREATE TABLE orders_products (ref_id BIGINT PRIMARY KEY AUTO_INCREMENT, order_id BIGINT, product_id BIGINT, price DOUBLE, quantity BIGINT) ENGINE = InnoDB;

Vous utilisez le ref_id comme CLÉ PRIMAIRE et celui-ci s’incrémentera automatiquement après chaque insertion d’enregistrement. order_id et product_id correspondent respectivement aux tableaux orders et products. La colonne price est du type de données DOUBLE afin de tenir compte des valeurs flottantes.

Le moteur de stockage InnoDB doit correspondre aux autres tableaux créés précédemment, car la commande d’un seul client affectera plusieurs tableaux simultanément en utilisant des transactions.

Votre sortie confirmera la création du tableau :

Output
Query OK, 0 rows affected (0.02 sec)

Vous n’ajouterez aucune donnée aux tableaux orders et orders_products pour l’instant, mais vous le ferez plus tard en utilisant un script PHP qui implémente des transactions MySQL.

Déconnectez-vous du serveur MySQL :

QUIT;

Votre schéma de base de données est maintenant complet et vous l’avez rempli avec quelques enregistrements. Vous allez maintenant créer une classe PHP pour gérer les connexions à la base de données et les transactions MySQL.

Étape 2 — Conception d’une classe PHP pour traiter les transactions MySQL

Dans cette étape, vous allez créer une classe PHP qui utilisera PDO (PHP Data Objects) pour gérer les transactions MySQL. La classe se connectera à votre base de données MySQL et insèrera des données atomiquement à la base de données.

Enregistrez le fichier de la classe dans le répertoire racine de votre serveur web Apache. Pour ce faire, créez un fichier DBTransaction.php à l’aide de votre éditeur de texte :

sudo nano /var/www/html/DBTransaction.php

Ensuite, ajoutez le code suivant au fichier. Remplacez PASSWORD par la valeur que vous avez créée à l’étape 1 :

<?php

class DBTransaction
{
    protected $pdo;
    public $last_insert_id;

    public function __construct()
    {
        define('DB_NAME', 'sample_store');
        define('DB_USER', 'sample_user');
        define('DB_PASSWORD', 'PASSWORD');
        define('DB_HOST', 'localhost');

        $this->pdo = new PDO("mysql:host=" . DB_HOST . ";dbname=" . DB_NAME, DB_USER, DB_PASSWORD);
        $this->pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
        $this->pdo->setAttribute(PDO::ATTR_EMULATE_PREPARES, false);
    }

Vers le début de la classe DBTransaction, le PDO utilisera les constantes (DB_HOST, DB_NAME, DB_USER, et DB_PASSWORD) pour initialiser et se connecter à la base de données que vous avez créée à l’étape 1.

Ensuite, vous définissez deux attributs pour la classe PDO :

• ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION : cet attribut indique à PDO de lancer une exception si une erreur est rencontrée. De telles erreurs peuvent être enregistrées pour le débogage.
• ATTR_EMULATE_PREPARES, false : cette option désactive l’émulation des déclarations préparées et permet au moteur de la base de données MySQL de préparer les déclarations lui-même.

Ajoutez maintenant le code suivant à votre fichier pour créer les méthodes pour votre classe :

. . .
    public function startTransaction()
    {
        $this->pdo->beginTransaction();
    }

    public function insertTransaction($sql, $data)
    {
        $stmt = $this->pdo->prepare($sql);
        $stmt->execute($data);
        $this->last_insert_id = $this->pdo->lastInsertId();
    }

    public function submitTransaction()
    {
        try {
            $this->pdo->commit();
        } catch(PDOException $e) {
            $this->pdo->rollBack();
            return false;
        }

          return true;
    }
}

Enregistrez et fermez le fichier en appuyant sur CTRL + X, Y, puis sur ENTER.

Pour travailler avec les transactions MySQL, vous créez trois méthodes principales dans la classe DBTransaction : startTransaction, insertTransaction et submitTransaction.

• startTransaction : cette méthode demande à PDO de démarrer une transaction et désactive l’auto-validation jusqu’à ce qu’une commande de validation soit émise.

• insertTransaction : cette méthode prend deux arguments. La variable $sql contient l’instruction SQL à exécuter tandis que la variable $data est un tableau des données à lier à l’instruction SQL puisque vous utilisez des déclarations préparées. Les données sont transmises sous forme de tableau à la méthode insertTransaction.

• submitTransaction : Cette méthode valide les modifications apportées à la base de données de manière permanente en lançant une commande commit(). Cependant, s’il y a une erreur et que les transactions rencontrent un problème, la méthode appelle la méthode rollBack() pour remettre la base de données dans son état initial au cas où une exception PDO serait soulevée.

Votre classe DBTransaction initialise une transaction, prépare les différentes commandes SQL à exécuter, et enfin valide les modifications de la base de données de manière atomique s’il n’y a pas de problème, sinon, la transaction est annulée. De plus, la classe vous permet de récupérer l’enregistrement order_id que vous venez de créer en accédant à la propriété publique last_insert_id.

La classe DBTransaction est maintenant prête à être appelée et utilisée par n’importe quel code PHP, que vous allez créer ensuite.

Étape 3 — Création d’un script PHP pour utiliser la classe DBTransaction

Vous allez créer un script PHP qui implémentera la classe DBTransaction et enverra un groupe de commandes SQL à la base de données MySQL. Vous imiterez le déroulement de la commande d’un client dans un panier d’achat en ligne.

Ces requêtes SQL affecteront les tableaux orders et orders_products. Votre classe DBTransaction ne devrait permettre de modifier la base de données que si toutes les requêtes sont exécutées sans aucune erreur.  Dans le cas contraire, vous obtiendrez un retour d’erreur et toute tentative de modification sera annulée.

Vous créez une commande unique pour le client JOHN DOE identifié avec le customer_id 1.La commande du client comporte trois articles différents avec des quantités différentes du tableau products. Votre script PHP prend les données de la commande du client et les soumet dans la classe DBTransaction.

Créez le fichier orders.php :

sudo nano /var/www/html/orders.php

Ensuite, ajoutez le code suivant au fichier :

<?php

require("DBTransaction.php");

$db_host = "database_host";
$db_name = "database_name";
$db_user = "database_user";
$db_password = "PASSWORD";

$customer_id = 2;

$products[] = [
  'product_id' => 1,
  'price' => 25.50,
  'quantity' => 1
];

$products[] = [
  'product_id' => 2,
  'price' => 13.90,
  'quantity' => 3
];

$products[] = [
  'product_id' => 3,
  'price' => 45.30,
  'quantity' => 2
];

$transaction = new DBTransaction($db_host, $db_user, $db_password, $db_name);

Vous avez créé un script PHP qui initialise une instance de la classe DBTransaction que vous avez créée à l’étape 2.

Dans ce script, vous incluez le fichier DBTransaction.php et vous initialisez la classe DBTransaction. Ensuite, vous préparez un tableau multidimensionnel de tous les produits que le client commande dans le magasin. Vous invoquez également la méthode startTransaction() pour lancer une transaction.

Ensuite, ajoutez le code suivant pour terminer votre script orders.php :

. . .
$order_query = "insert into orders (order_id, customer_id, order_date, order_total) values(:order_id, :customer_id, :order_date, :order_total)";
$product_query = "insert into orders_products (order_id, product_id, price, quantity) values(:order_id, :product_id, :price, :quantity)";

$transaction->insertQuery($order_query, [
  'customer_id' => $customer_id,
  'order_date' => "2020-01-11",
  'order_total' => 157.8
]);

$order_id = $transaction->last_insert_id;

foreach ($products as $product) {
  $transaction->insertQuery($product_query, [
    'order_id' => $order_id,
    'product_id' => $product['product_id'],
    'price' => $product['price'],
    'quantity' => $product['quantity']
  ]);
}

$result = $transaction->submit();

if ($result) {
    echo "Records successfully submitted";
} else {
    echo "There was an error.";
}

Enregistrez et fermez le fichier en appuyant sur CTRL + X, Y, puis ENTER.

Vous préparez la commande à insérer dans le tableau orders via la méthode insertTransaction. Après cela, vous récupérez la valeur de la propriété publique last_insert_id de la classe DBTransaction et l’utilisez-comme $order_id.

Une fois que vous avez un $order_id, vous utilisez l’ID unique pour insérer les éléments de la commande du client dans le tableau orders_products.

Enfin, vous appelez la méthode submitTransaction pour valider l’ensemble des détails de la commande du client dans la base de données s’il n’y a pas de problème. Dans le cas contraire, la méthode de la submitTransaction annulera les tentatives de modification.

Vous allez maintenant exécuter le script orders.php dans votre navigateur. Exécutez ce qui suit et remplacez your-server-IP par l’adresse IP publique de votre serveur :

http://your-server-IP/orders.php

Vous verrez la confirmation que les enregistrements ont été soumis avec succès :

Votre script PHP fonctionne comme prévu et la commande ainsi que les produits associés ont été soumis à la base de données de manière atomique.

Vous avez exécuté le fichier orders.php dans une fenêtre du navigateur. Le script a invoqué la classe DBTransaction qui a à son tour soumis les détails des orders (commandes) à la base de données. Dans l’étape suivante, vous allez vérifier si les enregistrements ont été sauvegardés dans les tableaux correspondants de la base de données.

Étape 4 — Confirmation des entrées dans votre base de données

Au cours de cette étape, vous vérifierez si la transaction initiée depuis la fenêtre du navigateur pour la commande du client a été enregistrée dans les tableaux de la base de données comme prévu.

Pour ce faire, connectez-vous à nouveau à votre base de données MySQL :

sudo mysql -u sample_user -p

Entrez le mot de passe du sample_user et appuyez sur ENTER (ENTRÉE) pour continuer.

Passez à la base de données sample_store :

USE sample_store;

Assurez-vous que la base de données est modifiée avant de poursuivre en confirmant la sortie suivante :

Output
Database Changed.

Ensuite, lancez la commande suivante pour récupérer les enregistrements du tableau orders :

SELECT * FROM orders;

Cela affichera la sortie suivante détaillant la commande du client :

Output
+----------+---------------------+-------------+-------------+
| order_id | order_date          | customer_id | order_total |
+----------+---------------------+-------------+-------------+
|  1       | 2020-01-11 00:00:00 |           2 |       157.8 |
+----------+---------------------+-------------+-------------+
1 row in set (0.00 sec)

Ensuite, récupérez les enregistrements du tableau orders_products :

SELECT * FROM orders_products;

Vous verrez une sortie semblable à celle qui suit avec une liste de produits de la commande du client :

Output
+--------+----------+------------+-------+----------+
| ref_id | order_id | product_id | price | quantity |
+--------+----------+------------+-------+----------+
|      1 |  1       |          1 |  25.5 |        1 |
|      2 |  1       |          2 |  13.9 |        3 |
|      3 |  1       |          3 |  45.3 |        2 |
+--------+----------+------------+-------+----------+
3 rows in set (0.00 sec)

Le résultat confirme que la transaction a été enregistrée dans la base de données et que la classe DBTransaction de votre assistant fonctionne comme prévu.

Conclusion

Dans ce guide, vous avez utilisé le PDO PHP pour travailler avec des transactions MySQL. Bien que cet article ne soit pas exhaustif sur la conception d’un logiciel de commerce électronique, il a fourni un exemple d’utilisation des transactions MySQL dans vos applications.

Pour en apprendre davantage sur le modèle ACID de MySQL, pensez à consulter le guide InnoDB et le modèle ACID sur le site officiel de MySQL. Consultez notre page de contenu MySQL pour d’autres tutoriels, articles et Questions & Réponses.