One place for hosting & domains

      Extension

      Cómo usar la extensión PHP PDO para realizar transacciones de MySQL en PHP en Ubuntu 18.04


      El autor seleccionó Open Sourcing Mental Illness para recibir una donación como parte del programa Write for DOnations.

      Introducción

      Una transacción de MySQL es un conjunto de comandos SQL que guardan una relación lógica y se ejecutan en la base de datos como una sola unidad. Las transacciones se utilizan para garantizar el cumplimiento de ACID (atomicidad, consistencia, aislamiento y durabilidad, por su sigla en inglés) en las aplicaciones. Se trata de un conjunto de normas que determinan la confiabilidad de las operaciones de procesamiento de las bases de datos.

      La atomicidad garantiza que las transacciones relacionadas sean correctas o no surtan ningún efecto si se produce un error. La consistencia garantiza la validez de los datos enviados a la base de datos según la lógica de negocios definida. El aislamiento es la correcta ejecución de transacciones simultáneas que garantiza que los efectos de los distintos clientes que se conectan a una base de datos no se afecten entre sí. La durabilidad garantiza que las transacciones con relación lógica se conserven en la base de datos de forma permanente.

      Las instrucciones SQL que se emiten a través de una transacción deben ser correctas fracasar por completo. Si alguna consulta falla, MySQL revierte los cambios y no se confirman en la base de datos.

      Un buen ejemplo para comprender cómo funcionan las transacciones de MySQL es un sitio web de comercio electrónico. Cuando un cliente realiza un pedido, la aplicación inserta registros en varias tablas, por ejemplo, orders y orders_products, dependiendo de la lógica de negocio. Los registros de varias tablas relacionados con un único pedido se deben enviar de forma atómica a la base de datos como una sola unidad lógica.

      Otro caso de uso es una aplicación bancaria. Cuando un cliente transfiere dinero, se envían algunas transacciones a la base de datos. Se debita dinero de la cuenta del remitente y se acredita en la del receptor. Las dos transacciones se deben confirmar de forma simultánea. Si una de ellas falla, la base de datos volverá a su estado original y no se guardarán cambios en el disco.

      En el caso de este tutorial, usará la extensión PHP PDO, que ofrece una interfaz para trabajar con bases de datos en PHP, para realizar transacciones de MySQL en un servidor de Ubuntu 18.04.

      Requisitos previos

      Para comenzar, necesitará lo siguiente:

      Paso 1: Crear una base de datos y tablas de ejemplo

      Primero, creará una base de datos de ejemplo y agregará algunas tablas para comenzar a trabajar con transacciones de MySQL. Primero, inicie sesión en su servidor MySQL como root:

      Cuando se le solicite, introduzca su contraseña root de MySQL y presione INTRO para continuar. Luego, cree una base de datos. En este tutorial, la denominaremos sample_store:

      • CREATE DATABASE sample_store;

      Verá lo siguiente:

      Output

      Query OK, 1 row affected (0.00 sec)

      Cree un usuario llamado sample_user para su base de datos. Recuerde sustituir PASSWORD por un valor seguro:

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

      Conceda privilegios completos a su usuario para la base de datos sample_store:

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

      Por último, vuelva a cargar los privilegios de MySQL:

      Una vez que cree su usuario, verá el siguiente resultado:

      Output

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

      Ahora, con la base de datos y el usuario establecidos, puede crear varias tablas para ver cómo funcionan las transacciones de MySQL.

      Cierre sesión en el servidor de MySQL:

      Una vez que el sistema cierre su sesión, verá el siguiente resultado:

      Output

      Bye.

      Luego, inicie sesión con las credenciales de sample_user que acaba de crear:

      • sudo mysql -u sample_user -p

      Ingrese la contraseña de sample_user y presione INTRO para continuar.

      Pase a sample_store para que sea la base de datos seleccionada:

      Una vez que la seleccione, verá el siguiente resultado:

      Output

      Database Changed.

      A continuación, cree una tabla products:

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

      Este comando crea una tabla products con un campo denominado product_id. Se utiliza el tipo de datos BIGINT, que puede admitir un valor elevado de hasta 2^63-1. Se utiliza este mismo campo como PRIMARY KEY para identificar productos de forma única. La palabra clave AUTO_INCREMENT indica a MySQL que genere el siguiente valor numérico cuando se insertan nuevos productos.

      El campo product_name es de tipo VARCHAR y puede contener hasta 50 letras o números. En price del producto, se utiliza el tipo de datos DOUBLE para admitir formatos de punto flotante en los precios con números decimales.

      Por último, se utiliza InnoDB como ENGINE porque admite fácilmente las transacciones de MySQL a diferencia de otros motores de almacenamiento, como MyISAM.

      Cuando haya creado su tabla products, verá el siguiente resultado:

      Output

      Query OK, 0 rows affected (0.02 sec)

      A continuación, añada algunos elementos a la tabla products al ejecutar los siguientes comandos:

      • 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');

      Verá un resultado similar al siguiente después de cada operación INSERT:

      Output

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

      Luego, compruebe que se hayan agregado los datos a la tabla products:

      Verá una lista con los cuatro productos que insertó:

      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)

      A continuación, creará una tabla customers para almacenar información básica sobre los clientes:

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

      Al igual que en la tabla products, se utiliza el tipo de datos BIGINT para customer_id para garantizar que la tabla pueda admitir muchos clientes, hasta 2^63-1 registros. La palabra clave AUTO_INCREMENT aumenta el valor de las columnas cuando se inserta un nuevo cliente.

      Como la columna customer_name acepta valores alfanuméricos, se utiliza el tipo de datos VARCHAR, que tiene un límite de 50 caracteres. Nuevamente, se utiliza InnoDB como ENGINE de almacenamiento para admitir las transacciones.

      Después de ejecutar el comando anterior para crear la tabla customers, verá el siguiente resultado:

      Output

      Query OK, 0 rows affected (0.02 sec)

      Agregará tres clientes de ejemplo a la tabla. Ejecute el siguiente comando:

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

      Una vez que haya agregado los clientes, verá un resultado similar al siguiente:

      Output

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

      Luego, verifique los datos de la tabla customers:

      Verá una lista con los tres clientes:

      Output

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

      A continuación, creará una tabla orders para registrar los pedidos de los distintos clientes. Para crear la tabla orders, ejecute el siguiente comando:

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

      Se utiliza la columna order_id como PRIMARY KEY. El tipo de datos BIGINT le permite ingresar hasta 2^63-1 pedidos que se incrementan de forma automática cada vez que se introduce uno nuevo. El campo order_date contiene la fecha y la hora reales en que se realiza un pedido y, por lo tanto, se utiliza el tipo de datos DATETIME. El campo customer_id se relaciona con la tabla customers que creó anteriormente.

      Verá lo siguiente:

      Output

      Query OK, 0 rows affected (0.02 sec)

      Como el pedido de un cliente puede contener varios elementos, debe crear una tabla orders_products para almacenar esta información.

      Para crear la tabla orders_products, ejecute el siguiente comando:

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

      Se utiliza ref_id como PRIMARY KEY, que se incrementará automáticamente con cada registro que se ingrese. Los campos order_id y product_id se relacionan con las tablas orders y products respectivamente. La columna price es de tipo DOUBLE para poder admitir valores flotantes.

      El motor de almacenamiento InnoDB debe coincidir con las otras tablas creadas anteriormente, dado que el pedido de un cliente afectará varias tablas que utilizan transacciones en simultáneo.

      Su resultado confirmará la creación de la tabla:

      Output

      Query OK, 0 rows affected (0.02 sec)

      Por el momento, no agregará datos a las tablas orders y orders_products, pero lo hará más adelante utilizando una secuencia de comandos PHP que implementa transacciones de MySQL.

      Cierre sesión en el servidor de MySQL:

      Ahora, su esquema de base de datos está completo y lo completó con algunos registros. Ahora, creará una clase PHP para gestionar las conexiones de la base de datos y las transacciones de MySQL.

      Paso 2: Diseñar una clase PHP para administrar transacciones de MySQL

      En este paso, creará una clase PHP que usará PDO (objetos de datos de PHP) para gestionar transacciones de MySQL. La clase se conectará con su base de datos de MySQL e insertará datos de forma atómica en la base de datos.

      Guarde el archivo de clase en el directorio root de su servidor web de Apache. Para hacerlo, cree un archivo DBTransaction.php con su editor de texto:

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

      Luego, añada el siguiente código al archivo. Reemplace PASSWORD por el valor que creó en el paso 1:

      /var/www/html/DBTransaction.php

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

      Al comienzo de la clase DBTransaction, el PDO utilizará las constantes (DB_HOST, DB_NAME, DB_USER, y DB_PASSWORD) para inicializar la base de datos que creó en el paso 1 y conectarse a ella.

      Nota: Debido a que estamos demostrando transacciones de MySQL en pequeña escala en este tutorial, declaramos las variables de la base de datos en la clase DBTransaction. En un proyecto de producción de gran envergadura, normalmente, crearía un archivo de configuración separado y cargaría las constantes de la base de datos de ese archivo usando la instrucción de PHP require_once.

      A continuación, establezca dos atributos para la clase PDO:

      • ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION: este atributo le indica a PDO que inicie una excepción si se encuentra un error. Los errores se pueden registrar para su depuración.
      • ATTR_EMULATE_PREPARES, false: esta opción desactiva la emulación de las instrucciones preparadas y permite que el motor de la base de datos de MySQL las prepare por su cuenta.

      Ahora, añada el siguiente código a su archivo para crear los métodos de su clase:

      /var/www/html/DBTransaction.php

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

      Guarde y cierre el archivo pulsando CTRL + X, Y e INTRO.

      Para trabajar con las transacciones de MySQL, se crean tres métodos principales en la clase DBTransaction: startTransaction, insertTransaction y submitTransaction.

      • startTransaction: este método le indica a PDO que inicie una transacción y desactive la confirmación automática hasta que se emita un comando de confirmación.

      • insertTransaction: este método toma dos argumentos. La variable $sql contiene la instrucción SQL que se ejecutará y la variable $data es una matriz de los datos que se vincularán con la instrucción SQL, dado que se utilizarán instrucciones preparadas. Los datos se pasan como una matriz al método insertTransaction.

      • submitTransaction: este método confirma los cambios de forma permanente en la base de datos al emitir un comando commit(). Sin embargo, si hay un error y las transacciones tienen un problema, el método invoca el método rollBack() para hacer que la base de datos regrese a su estado original en caso de que se produzca una excepción de PDO.

      Su clase DBTransaction inicia una transacción, prepara los diferentes comandos SQL que se ejecutarán y, por último, confirma los cambios en la base de datos de forma atómica si no hay problemas. De lo contrario, la transacción se revierte. Además, la clase le permite obtener el registro order_id que acaba de crear al acceder a la propiedad pública last_insert_id.

      Ahora, la clase DBTransaction está lista para invocarse y utilizarse mediante cualquier código PHP, que se creará a continuación.

      Paso 3: Crear una secuencia de comandos PHP para usar la clase DBTransaction

      Creará una secuencia de comandos PHP que implementará la clase DBTransaction y enviará un conjunto de comandos SQL a la base de datos de MySQL. Simulará el flujo de trabajo de un pedido de un cliente en un carrito de compras en línea.

      Estas consultas SQL afectarán las tablas orders y orders_products. Su clase DBTransaction solo debería permitir cambios en la base de datos si todas las consultas se ejecutan sin errores. De lo contrario, verá un error y se revertirán los cambios que haya intentado implementar.

      Creará un pedido único para el cliente JOHN DOE identificado con la customer_id 1. En el pedido del cliente hay tres artículos diferentes con cantidades distintas de la tabla products. Su secuencia de comandos PHP toma los datos del pedido del cliente y los envía a la clase DBTransaction.

      Cree el archivo orders.php:

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

      Luego, añada el siguiente código al archivo:

      /var/www/html/orders.php

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

      Creó una secuencia de comandos PHP que inicia una instancia de la clase DBTransaction que creó en el paso 2.

      En esta secuencia de comandos, incluye el archivo DBTransaction.php e inicia la clase DBTransaction. Luego, prepara una matriz multidimensional con todos los productos que el cliente pide a la tienda. También invoca el método startTransaction() para iniciar una transacción.

      A continuación, añada el siguiente código para finalizar su secuencia de comandos orders.php

      /var/www/html/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.";
      }
      
      

      Guarde y cierre el archivo pulsando CTRL + X, Y e INTRO.

      El comando que se insertará en la tabla de pedidos se prepara con el método insertTransaction. A continuación, obtenga el valor de la propiedad pública last_insert_id de la clase DBTransaction y úselo como $order_id.

      Una vez que se dispone de una $order_id, se utiliza la identificación única para insertar los artículos del pedido del cliente en la tabla orders_products.

      Por último, invoque el método submitTransaction para confirmar los datos del pedido del cliente en la base de datos si no hay problemas. De lo contrario, el método submitTransaction revertirá los cambios que haya intentado realizar.

      Ahora, ejecute la secuencia de comandos orders.php en su navegador. Ejecute lo siguiente y sustituya your-server-IP por la dirección IP pública de su servidor:

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

      Verá la confirmación de que los registros se enviaron correctamente:

      Resultado de PHP de la clase de transacciones de MySQL

      Su secuencia de comandos PHP funciona según lo previsto y el pedido y los productos del pedido se enviaron a la base de datos de forma atómica.

      Ejecutó el archivo orders.php en la ventana de un navegador. La secuencia de comandos invocó la clase DBTransaction, que, a su vez, envió los datos de orders a la base de datos. En el siguiente paso, verificará si los registros se almacenaron en las tablas relacionadas de la base de datos.

      Paso 4: Confirmar los entradas en su base de datos

      En este paso, verificará si la transacción del pedido del cliente iniciada desde la ventana del navegador se envió a las tablas de la base de datos según lo previsto.

      Para hacerlo, vuelva a iniciar sesión en su base de datos de MySQL:

      • sudo mysql -u sample_user -p

      Ingrese la contraseña de sample_user y presione INTRO para continuar.

      Pase a la base de datos sample_store:

      Antes de continuar, asegúrese de que la base de datos se haya modificado al confirmar el siguiente resultado:

      Output

      Database Changed.

      Luego, emita el siguiente comando para obtener los registros de la tabla orders:

      Esto mostrará el siguiente resultado en el que se detalla el pedido del cliente:

      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)

      A continuación, obtenga los registros de la tabla orders_products:

      • SELECT * FROM orders_products;

      Verá un resultado similar al siguiente con una lista de los productos del pedido del cliente:

      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)

      El resultado confirma que la transacción se guardó en la base de datos y que su clase auxiliar DBTransaction está funcionando según lo previsto.

      Conclusión

      En esta guía, utilizó PDO de PHP para trabajar con transacciones de MySQL. Si bien en este artículo no se abarcan todos los aspectos del diseño de un software de comercio electrónico, se ofreció un ejemplo para usar transacciones de MySQL en sus aplicaciones.

      Para obtener más información sobre el modelo ACID de MySQL, considere consultar la guía InnoDB y el modelo ACID en el sitio web oficial de MySQL. Visite nuestra página de contenido de MySQL para acceder a más tutoriales, artículos y preguntas y respuestas relacionados.



      Source link

      How To Use the PDO PHP Extension to Perform MySQL Transactions in PHP on Ubuntu 18.04


      The author selected Open Sourcing Mental Illness to receive a donation as part of the Write for DOnations program.

      Introduction

      A MySQL transaction is a group of logically related SQL commands that are executed in the database as a single unit. Transactions are used to enforce ACID (Atomicity, Consistency, Isolation, and Durability) compliance in an application. This is a set of standards that govern the reliability of processing operations in a database.

      Atomicity ensures the success of related transactions or a complete failure if an error occurs. Consistency guarantees the validity of the data submitted to the database according to defined business logic. Isolation is the correct execution of concurrent transactions ensuring the effects of different clients connecting to a database do not affect each other. Durability ensures that logically related transactions remain in the database permanently.

      SQL statements issued via a transaction should either succeed or fail altogether. If any of the queries fails, MySQL rolls back the changes and they are never committed to the database.

      A good example to understand how MySQL transactions work is an e-commerce website. When a customer makes an order, the application inserts records into several tables, such as: orders and orders_products, depending on the business logic. Multi-table records related to a single order must be atomically sent to the database as a single logical unit.

      Another use-case is in a bank application. When a client is transferring money, a couple of transactions are sent to the database. The sender’s account is debited and the receiver’s party account is credited. The two transactions must be committed simultaneously. If one of them fails, the database will revert to its original state and no changes should be saved to disk.

      In this tutorial, you will use the PDO PHP Extension, which provides an interface for working with databases in PHP, to perform MySQL transactions on an Ubuntu 18.04 server.

      Prerequisites

      Before you begin, you will need the following:

      Step 1 — Creating a Sample Database and Tables

      You’ll first create a sample database and add some tables before you start working with MySQL transactions. First, log in to your MySQL server as root:

      When prompted, enter your MySQL root password and hit ENTER to proceed. Then, create a database, for the purposes of this tutorial we’ll call the database sample_store:

      • CREATE DATABASE sample_store;

      You will see the following output:

      Output

      Query OK, 1 row affected (0.00 sec)

      Create a user called sample_user for your database. Remember to replace PASSWORD with a strong value:

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

      Issue full privileges for your user to the sample_store database:

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

      Finally, reload the MySQL privileges:

      You’ll see the following output once you’ve created your user:

      Output

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

      With the database and user in place, you can now create several tables for demonstrating how MySQL transactions work.

      Log out from the MySQL server:

      Once the system logs you out, you will see the following output:

      Output

      Bye.

      Then, log in with the credentials of the sample_user you just created:

      • sudo mysql -u sample_user -p

      Enter the password for the sample_user and hit ENTER to proceed.

      Switch to the sample_store to make it the currently selected database:

      You’ll see the following output once it is selected:

      Output

      Database Changed.

      Next, create a products table:

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

      This command creates a products table with a field named product_id. You use a BIGINT data type that can accommodate a large value of up to 2^63-1. You use this same field as a PRIMARY KEY to uniquely identify products. The AUTO_INCREMENT keyword instructs MySQL to generate the next numeric value as new products are inserted.

      The product_name field is of type VARCHAR that can hold up to a maximum of 50 letters or numbers. For the product price, you use a DOUBLE data type to cater for floating point formats in prices with decimal numbers.

      Lastly, you use the InnoDB as the ENGINE because it comfortably supports MySQL transactions as opposed to other storage engines such as MyISAM.

      Once you’ve created your products table, you’ll get the following output:

      Output

      Query OK, 0 rows affected (0.02 sec)

      Next, add some items to the products table by running the following commands:

      • 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');

      You’ll see output similar to the following after each INSERT operation:

      Output

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

      Then, verify that the data was added to the products table:

      You will see a list of the four products that you have inserted:

      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)

      Next, you’ll create a customers table for holding basic information about customers:

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

      As in the products table, you use the BIGINT data type for the customer_id and this will ensure the table can support a lot of customers up to 2^63-1 records. The keyword AUTO_INCREMENT increments the value of the columns once you insert a new customer.

      Since the customer_name column accepts alphanumeric values, you use VARCHAR data type with a limit of 50 characters. Again, you use the InnoDB storage ENGINE to support transactions.

      After running the previous command to create the customers table, you will see the following output:

      Output

      Query OK, 0 rows affected (0.02 sec)

      You’ll add three sample customers to the table. Run the following commands:

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

      Once the customers have been added, you will see an output similar to the following:

      Output

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

      Then, verify the data in the customers table:

      You’ll see a list of the three customers:

      Output

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

      Next, you’ll create an orders table for recording orders placed by different customers. To create the orders table, execute the following command:

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

      You use the column order_id as the PRIMARY KEY. The BIGINT data type allows you to accommodate up to 2^63-1 orders and will auto-increment after each order insertion. The order_date field will hold the actual date and time the order is placed and hence, you use the DATETIME data type. The customer_id relates to the customers table that you created previously.

      You will see the following output:

      Output

      Query OK, 0 rows affected (0.02 sec)

      Since a single customer’s order may contain multiple items, you need to create an orders_products table to hold this information.

      To create the orders_products table, run the following command:

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

      You use the ref_id as the PRIMARY KEY and this will auto-increment after each record insertion. The order_id and product_id relate to the orders and the products tables respectively. The price column is of data type DOUBLE in order to accommodate floating values.

      The storage engine InnoDB must match the other tables created previously since a single customer’s order will affect multiple tables simultaneously using transactions.

      Your output will confirm the table’s creation:

      Output

      Query OK, 0 rows affected (0.02 sec)

      You won’t be adding any data to the orders and orders_products tables for now but you’ll do this later using a PHP script that implements MySQL transactions.

      Log out from the MySQL server:

      Your database schema is now complete and you’ve populated it with some records. You’ll now create a PHP class for handling database connections and MySQL transactions.

      Step 2 — Designing a PHP Class to Handle MySQL Transactions

      In this step, you will create a PHP class that will use PDO (PHP Data Objects) to handle MySQL transactions. The class will connect to your MySQL database and insert data atomically to the database.

      Save the class file in the root directory of your Apache web server. To do this, create a DBTransaction.php file using your text editor:

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

      Then, add the following code to the file. Replace PASSWORD with the value you created in Step 1:

      /var/www/html/DBTransaction.php

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

      Toward the beginning of the DBTransaction class, the PDO will use the constants (DB_HOST, DB_NAME, DB_USER, and DB_PASSWORD) to initialize and connect to the database that you created in step 1.

      Note: Since we are demonstrating MySQL transactions in a small scale here, we have declared the database variables in the DBTransaction class. In a large production project, you would normally create a separate configuration file and load the database constants from that file using a PHP require_once statement.

      Next, you set two attributes for the PDO class:

      • ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION: This attribute instructs PDO to throw an exception if an error is encountered. Such errors can be logged for debugging.
      • ATTR_EMULATE_PREPARES, false: This option disables emulation of prepared statements and allows the MySQL database engine to prepare the statements itself.

      Now add the following code to your file to create the methods for your class:

      /var/www/html/DBTransaction.php

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

      Save and close the file by pressing CTRL + X, Y, then ENTER.

      To work with MySQL transactions, you create three main methods in the DBTransaction class; startTransaction, insertTransaction, and submitTransaction.

      • startTransaction: This method instructs PDO to start a transaction and turns auto-commit off until a commit command is issued.

      • insertTransaction : This method takes two arguments. The $sql variable holds the SQL statement to be executed while the $data variable is an array of the data to be bound to the SQL statement since you’re using prepared statements. The data is passed as an array to the insertTransaction method.

      • submitTransaction : This method commits the changes to the database permanently by issuing a commit() command. However, if there is an error and the transactions have a problem, the method calls the rollBack() method to revert the database to its original state in case a PDO exception is raised.

      Your DBTransaction class initializes a transaction, prepares the different SQL commands to be executed, and finally commits the changes to the database atomically if there are no issues, otherwise, the transaction is rolled back. In addition, the class allows you to retrieve the record order_id you just created by accessing the public property last_insert_id.

      The DBTransaction class is now ready to be called and used by any PHP code, which you’ll create next.

      Step 3 — Creating a PHP Script to Use the DBTransaction Class

      You’ll create a PHP script that will implement the DBTransaction class and send a group of SQL commands to the MySQL database. You’ll mimic the workflow of a customer’s order in an online shopping cart.

      These SQL queries will affect the orders and the orders_products tables. Your DBTransaction class should only allow changes to the database if all of the queries are executed without any errors. Otherwise, you’ll get an error back and any attempted changes will roll back.

      You are creating a single order for the customer JOHN DOE identified with customer_id 1. The customer’s order has three different items with differing quantities from the products table. Your PHP script takes the customer’s order data and submits it into the DBTransaction class.

      Create the orders.php file:

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

      Then, add the following code to the file:

      /var/www/html/orders.php

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

      You’ve created a PHP script that initializes an instance of the DBTransaction class that you created in Step 2.

      In this script, you include the DBTransaction.php file and you initialize the DBTransaction class. Next, you prepare a multi-dimensional array of all the products the customer is ordering from the store. You also invoke the startTransaction() method to start a transaction.

      Next add the following code to finish your orders.php script:

      /var/www/html/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.";
      }
      
      

      Save and close the file by pressing CTRL + X, Y, then ENTER.

      You prepare the command to be inserted to the orders table via the insertTransaction method. After this, you retrieve the value of the public property last_insert_id from the DBTransaction class and use it as the $order_id.

      Once you have an $order_id, you use the unique ID to insert the customer’s order items to the orders_products table.

      Finally, you call the method submitTransaction to commit the entire customer’s order details to the database if there are no problems. Otherwise, the method submitTransaction will rollback the attempted changes.

      Now you’ll run the orders.php script in your browser. Run the following and replace your-server-IP with the public IP address of your server:

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

      You will see confirmation that the records were successfully submitted:

      PHP Output from MySQL Transactions Class

      Your PHP script is working as expected and the order together with the associated order products were submitted to the database atomically.

      You’ve run the orders.php file on a browser window. The script invoked the DBTransaction class which in turn submitted the orders details to the database. In the next step, you will verify if the records saved to the related database tables.

      Step 4 — Confirming the Entries in Your Database

      In this step, you’ll check if the transaction initiated from the browser window for the customer’s order was posted to the database tables as expected.

      To do this, log in to your MySQL database again:

      • sudo mysql -u sample_user -p

      Enter the password for the sample_user and hit ENTER to continue.

      Switch to the sample_store database:

      Ensure the database is changed before proceeding by confirming the following output:

      Output

      Database Changed.

      Then, issue the following command to retrieve records from the orders table:

      This will display the following output detailing the customer’s order:

      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)

      Next, retrieve the records from the orders_products table:

      • SELECT * FROM orders_products;

      You’ll see output similar to the following with a list of products from the customer’s order:

      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)

      The output confirms that the transaction was saved to the database and your helper DBTransaction class is working as expected.

      Conclusion

      In this guide, you used the PHP PDO to work with MySQL transactions. Although this is not a conclusive article on designing an e-commerce software, it has provided an example for using MySQL transactions in your applications.

      To learn more about the MySQL ACID model, consider visiting the InnoDB and the ACID Model guide from the official MySQL website. Visit our MySQL content page for more related tutorials, articles, and Q&A.



      Source link