Reading Time: 7 minutes

1,649 Views

Inside this article we will see the concept i.e How To Use PHP MySQLi Transaction & Commit Statement. This tutorial will give you a classified information about PHP MySQLi Transaction and Commit statements.

If you are looking for an article which will give you an idea that is how to insert large amount of data in database table then this article will give the complete overview of MySQLi transactions.

A transaction will ensure that all operations either complete in their entirety or have no effect whatsoever. Another important advantage of using a transaction is that it makes multiple inserts dramatically faster, eliminating all possible delays that could be caused by separate query execution.

A prepared statement will add some little boost to the speed and of course ensure that there will be no errors or injections.

Learn More –

Let’s get started.

Create Database & Table

To create a database, either we can create via Manual tool of PhpMyadmin or by means of a mysql command.

CREATE DATABASE php_applications;

Inside this database, we need to create a table.

Next,

Table: items

CREATE TABLE `items` (
 `id` int(11) NOT NULL AUTO_INCREMENT,
 `name` varchar(120) NOT NULL,
 `description` text NOT NULL,
 `price` int(5) NOT NULL,
 `category_id` int(5) NOT NULL,
 PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

Inside this table we will insert large amount of data.

How To Use Transaction & Commit

A mysqli transaction will ensure that all operations either complete in their entirety or have no effect whatsoever. Another important advantage of using a transaction is that it makes multiple inserts dramatically faster, eliminating all possible delays that could be caused by separate query execution.

Step #1 – Database Connection

Create an instance of database.

$servername = "localhost";
$username = "admin";
$password = "Admin@123";
$dbname = "php_applications";

// Create connection
$conn = new mysqli($servername, $username, $password, $dbname);

Here, $conn is an object of database connection.

To check database connection i.e successfully connected or not –

if ($conn->connect_errno) {
    echo "Failed to connect to MySQL: " . $conn->connect_error;
    exit();
}

Step #2 – Prepare Test Data

Here, we have 3 rows only and it is just for demonstration. You can take more than that like 1000, 20000, etc.

// Sample array of data
$data = [
    [
        "name" => "Item 1",
        'description' => "Sample description of Item 1",
        "price" => 5000,
        "category_id" => 3
    ],
    [
        "name" => "Item 2",
        'description' => "Sample description of Item 2",
        "price" => 7000,
        "category_id" => 4
    ],
    [
        "name" => "Item 3",
        'description' => "Sample description of Item 3",
        "price" => 9000,
        "category_id" => 3
    ]
];

Step #3 – Create Prepare Method

Next,

We need to use prepare method to delete data and also we will create an instance of that.

// Prepare the SQL query once
$stmt = $conn->prepare("INSERT INTO items SET name = ?, description = ?, price = ?, category_id = ?");

Step #4 – Begin Transaction & Commit

$conn->begin_transaction();

// Loop over the data array
foreach ($data as $row) {
    $stmt->bind_param("ssii", $row['name'], $row['description'], $row['price'], $row['category_id']);
    $stmt->execute();
}
$conn->commit();

Let’s connect all code into a single file.

Complete Code – MySQLi Prepare Method

Here, is the complete code which insert large amount of data to database table.

<?php
// Database configuration
$host   = "localhost";
$dbuser = "admin";
$dbpass = "Admin@123";
$dbname = "php_applications";

// Create database connection
$conn = new mysqli($host, $dbuser, $dbpass, $dbname);

// Check connection
if ($conn->connect_error) {
    die("Connection failed: " . $conn->connect_error);
}

// Sample array of data
$data = [
    [
        "name" => "Item 1",
        'description' => "Sample description of Item 1",
        "price" => 5000,
        "category_id" => 3
    ],
    [
        "name" => "Item 2",
        'description' => "Sample description of Item 2",
        "price" => 7000,
        "category_id" => 4
    ],
    [
        "name" => "Item 3",
        'description' => "Sample description of Item 3",
        "price" => 9000,
        "category_id" => 3
    ]
];
// Prepare the SQL query once
$stmt = $conn->prepare("INSERT INTO items SET name = ?, description = ?, price = ?, category_id = ?");

$conn->begin_transaction();

// Loop over the data array
foreach ($data as $row) {
    $stmt->bind_param("ssii", $row['name'], $row['description'], $row['price'], $row['category_id']);
    $stmt->execute();
}
$conn->commit();

Application Testing

Open application into browser.

URL: http://localhost/mysqli-prepare/index.php

We hope this article helped you to learn How To Use PHP MySQLi Transaction & Commit Statement in a very detailed way.

If you liked this article, then please subscribe to our YouTube Channel for PHP & it’s framework, WordPress, Node Js video tutorials. You can also find us on Twitter and Facebook.