PHP | mysqli_commit() Function

Hello folks! welcome back to a new edition of our tutorial on PHP. In this tutorial guide, we are going to be studying about the PHP mysqli_commit() Function.

The PHP mysqli_commit() function saves the current transaction for the specified database connection.

Syntax

Following below is the syntax to use this function -

mysqli_commit($con, [$flags, $name]);

READ: PHP | mysqli_change_user() Function

Parameter Details

Sr.No	Parameter & Description
1	con(Mandatory) This is an object representing a connection to MySQL Server.
2	flags(Optional) A constant which can be on of the following : MYSQLI_TRANS_COR_AND_CHAIN MYSQLI_TRANS_COR_AND_NO_CHAIN MYSQLI_TRANS_COR_RELEASE MYSQLI_TRANS_COR_NO_RELEASE
3	name(Optional) This is a name value which when given, executes as COMMIT/*name*/.

Return Value

This built-in PHP function returns true on success and false on failure.

PHP Version

This built-in function was first introduced in PHP version 5 and it works in all of the later versions.

Example1

Assuming we have created a table named my_team in the database mydb, as follows -

CREATE TABLE my_team(
   ID INT PRIMARY KEY AUTO_INCREMENT,
   First_Name VARCHAR(255), 
   Last_Name VARCHAR(255), 
   Place_Of_Birth VARCHAR(255), 
   Country VARCHAR(255)
);

Following example below demonstrates the usage of the built-in PHP mysqli_commit() function (in procedural style) -

<?php
   //Creating a connection
   $con = mysqli_connect("localhost", "root", "password", "mydb");

   //Setting auto commit to false
   mysqli_autocommit($con, False);

   //Inserting a records into the my_team table
   mysqli_query($con, "insert into my_team values(1, 'Kennedy', 'Nkpara', 'PortHarcourt', 'Nigeria')");
   mysqli_query($con, "insert into my_team values(2, 'Jonathan', 'Trott', 'CapeTown', 'SouthAfrica')");
   mysqli_query($con, "insert into my_team values(3, 'Queen', 'Dauglas', 'Texas', 'UnitedStates')");
   mysqli_query($con, "insert into my_team values(4, 'Paul', 'Francis', 'Texas', 'UnitedStates')");

   //Verifying the contents of the table
   $result = mysqli_query($con, "SELECT * FROM my_team");
   print_r($result);

   mysqli_commit($con);

   //Closing the connection
   mysqli_close($con);
?>

Output

When the above code is executed, it will produce the following result -

mysqli_result Object
(
    [current_field] => 0
    [field_count] => 5
    [lengths] =>
    [num_rows] => 4
    [type] => 0
)

If you verify the table contents my_team, then you will observe the saved data as shown below -

mysql> select * from my_team;
+----+------------+------------+----------------+-------------+
| ID | First_Name | Last_Name  | Place_Of_Birth | Country     |
+----+------------+------------+----------------+-------------+
|  1 | Kennedy    | Nkpara     | PortHarcourt   | Nigeria     |
|  2 | Jonathan   | Trott      | CapeTown       | SouthAfrica |
|  3 | Queen      | Douglas    | Texas          | UnitedStates|
|  4 | Paul       | Francis    | Texas          | UnitedStates|
+----+------------+------------+----------------+-------------+
4 rows in set (0.00 sec)

Example2

In an object oriented style the syntax of this function is $con->commit(). The following is an example of the built-in mysqli_commit() function in object oriented mode $minus;

//Creating a connection
$con = new mysqli("localhost", "root", "password", "mydb");

//Setting auto commit to true
$con->autocommit(FALSE);

//Inserting a records into the my_team table
$con->query( "insert into my_team values(1, 'Kennedy', 'Nkpara', 'PortHarcourt', 'Nigeria')");
$con->query( "insert into my_team values(2, 'Jonathan', 'Trott', 'CapeTown', 'SouthAfrica')");
$con->query( "insert into my_team values(3, 'Queen', 'Dauglas', 'Texas', 'UnitedStates')");
$con->query( "insert into my_team values(4, 'Paul', 'Francis', 'Texas', 'UnitedStates')");

//Verifying the contents of the table
$result = $con->query( "SELECT * FROM my_team");
print_r($result);

//Saving the results
$con->commit();

//Closing the connection
$con -> close();
?>

Output

When the above code is executed, it will produce the following result -

mysqli_result Object
(
    [current_field] => 0
    [field_count] => 5
    [lengths] =>
    [num_rows] => 4
    [type] => 0
)

Example3

Let us consider another example. In this example, we have created a table, turned the auto-commit option off and inserted a record and saved the changes. After the save we have inserted another record -

//Creating a connection
$con = mysqli_connect("localhost", "root", "password", "mydb");

mysqli_query($con, "Create table players (First_Name VARCHAR(255), Last_Name VARCHAR(255), Country VARCHAR(255))");
//Setting auto commit to false
mysqli_autocommit($con, False);

//Inserting a records into the my_team table
mysqli_query($con, "insert into players values('Kennedy', 'Nkpara', 'Nigeria')");

mysqli_commit($con);

mysqli_query($con, "insert into players values('Jonathan', 'Trott', 'SouthAfrica')");

//Closing the connection
mysqli_close($con);
?>

Output

Since we have not saved the last inserted query, after executing the above code if you verify the contents of the players table you can see only one record, as shown below -

mysql> select * from players;
+------------+-----------+-------------+
| First_Name | Last_Name | Country     |
+------------+-----------+-------------+
| Kennedy    | Nkpara    | Nigeria     |
|            |           |             |
+------------+-----------+-------------+
11 row in set (0.00 sec)

Example4

You can try the following example below -

<?php
   $connection = mysqli_connect("localhost", "root", "password", "mydb");
   
   if (mysqli_connect_errno($connection)){
      echo "Failed to connect to MySQL: " . mysqli_connect_error();
   }
   
   mysqli_autocommit($connection,FALSE);   
   
   mysqli_query($connection, "create table test(Name VARCHAR(255), Age INT)");   
   
   mysqli_query($connection, "INSERT INTO test VALUES ('Bethel', 27)");
   mysqli_query($connection, "INSERT INTO test VALUES ('Blessing', 30)");
   
   mysqli_commit($connection);
   mysqli_close($connection);
?>

Output

When the above code is executed, if you verify the contents of the table test, you can see the inserted records as shown below -

mysql> select * from test;
+---------+------+
| Name    | Age  |
+---------+------+
| Bethel  |   27 |
| Blessing|   30 |
+---------+------+
2 rows in set (0.00 sec)

READ: PHP | mysqli_close() Function

Alright guys! This is where we are rounding up for this tutorial post. In our next tutorial, we are going to be studying about the PHP mysqli_connect() Function.

Do feel free to ask your questions where necessary and we will attend to them as soon as possible. If this tutorial was helpful to you, you can use the share button to share this tutorial.

Do follow us on our various social media handles available and also subscribe to our newsletter to get our tutorial posts delivered directly to your emails.

Thanks for reading and bye for now.