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.
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]);
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 :
|
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)
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.
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.