Usage and examples of PHP mysqli_autocommit() function

PHP MySQLi Reference Manual

The mysqli_autocommit() function opens or closes the automatic command submission transaction mode of the current database connection

Definition and usage

MySQL databases have a feature called automatic submission. If you turn it on, changes made in the database will be automatically saved; if you turn it off, changes need to be explicitly saved.mysqli_autocommit()　Open or close the automatic command submission transaction mode of the current database connection. To confirm the current connection's automatic transaction submission status, you can execute this SQL request: SELECT @@autocommit .

This function accepts a boolean value as a parameter. IftrueGiven to this function, the automatic submission transaction mode will be enabled; iffalse, then the automatic submission transaction mode will be closed.

Syntax

mysqli_autocommit($con, $mode);

Parameter

Return value

The PHP mysqli_autocommit() function returns a boolean value, true when successful.true, fails to befalse.

Comments and hints

　This method will not take effect in form queries that do not support transaction processing, such as MyISAM or ISAM.

PHP version

This function was originally introduced in PHP version5introduced and can be used in all higher versions.

Online Example

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

The following example demonstratesmysqli_autocommit()Usage of the function (in procedural style)-

<?php
　　　//Establish connection
　　　$con = mysqli_connect("localhost", "root", "password", "mydb");
　　　//Set the auto-commit to false
　　　mysqli_autocommit($con, False);
　　　//Insert the record into the my_team table
　　　mysqli_query($con, "insert into my_team values(1, 'Shikhar', 'Dhawan', 'Delhi', 'India')");
　　　mysqli_query($con, "insert into my_team values(2, 'Jonathan', 'Trott', 'CapeTown', 'SouthAfrica')");
　　　mysqli_query($con, "insert into my_team values(3, 'Kumara', 'Sangakkara', 'Matale', 'Srilanka')");
　　　mysqli_query($con, "insert into my_team values(4, 'Virat', 'Kohli', 'Delhi', 'India')");
　　　//Verify the content of the table
　　　$result = mysqli_query($con, "SELECT　*　FROM my_team");
　　　print_r($result);
　　　//Close connection
　　　mysqli_close($con);
?>

Output results

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

Since we have turned off the automatic commit option in the previous example, the record addition will not be saved in the database, and if you verify the content of the table in MySQL, the table will be empty as follows;

mysql> select　*　from my_team;
Empty set (0.00 sec)

To save changes in the database, you need to use the mysqli_commit() function to commit changes as

mysqli_commit($con);

If you verify the content of the table my_team, you can see the inserted records as follows-

mysql> select　*　from my_team;
+----+------------+------------+----------------+-------------+
|  ID  |  First_Name  |  Last_Name  |  Place_Of_Birth  |  Country  |
+----+------------+------------+----------------+-------------+
|　　1　|  Shikhar  |  Dhawan  |  Delhi  |  India  |
|　　2　|  Jonathan  |  Trott  |  CapeTown  |  SouthAfrica  |
|　　3　|  Kumara  |  Sangakkara  |  Matale  |  Srilanka  |
|　　4　|  Virat  |  Kohli  |  Delhi  |  India  |
+----+------------+------------+----------------+-------------+
4　rows in set (0.00 sec)

Online Example

The syntax of this method in an object-oriented style is: $con-> autocommit();The following is an example of this function in an object-oriented style;

//Establish connection
$con = new mysqli("localhost", "root", "password", "mydb");
//Set the auto-commit to true
$con-> autocommit(FALSE);
//Insert the record into the my_team table
$con-> query("insert into my_team values(1, 'Shikhar', 'Dhawan', 'Delhi', 'India')");
$con-> query("insert into my_team values(2, 'Jonathan', 'Trott', 'CapeTown', 'SouthAfrica')");
$con-> query("insert into my_team values(3, 'Kumara', 'Sangakkara', 'Matale', 'Srilanka')");
$con-> query("insert into my_team values(4, 'Virat', 'Kohli', 'Delhi', 'India')");
//Verify the content of the table
$result = $con-> query("SELECT　*　FROM my_team");
print_r($result);
//Save results
$con-> commit();
//Close connection
$con　-> close();
?>

Output results

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

Online Example

The mysqli_autocommit() function can also be used as commit() when called, it will wait to save the query results to the database:

//Establish connection
$con = mysqli_connect("localhost", "root", "password", "mydb");
//Create table
mysqli_query($con, "Create table players (First_Name VARCHAR(255), Last_Name VARCHAR(255), Country VARCHAR(255))
//Set the auto-commit to false
mysqli_autocommit($con, False);
//Insert the record into the my_team table
mysqli_query($con, "insert into players values('Shikhar', 'Dhawan', 'India');")
mysqli_query($con, "insert into players values('Jonathan', 'Trott', 'SouthAfrica');")
mysqli_autocommit($con, TRUE);
//Close connection
mysqli_close($con);
?>

After executing the program, if you verify players If the content of the table is verified, then you can observe the added records as follows-

mysql> select　*　from players;
+------------+-----------+-------------+
| First_Name  | Last_Name    | Country       |
+------------+-----------+-------------+
| Shikhar     | Dhawan       | India         |
| Jonathan    | Trott        | South Africa |
+------------+-----------+-------------+
2　rows in set (0.00 sec)

Online Example

<?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 ('Sharukh',　25);
　　　mysqli_query($connection, "INSERT INTO test VALUES ('Kalyan',　30)
　　　
　　　mysqli_commit($connection);
　　　mysqli_close($connection);
?>

After executing the above program, if you verify the tabletestIf the content of the table is verified, then you can see the inserted record as-

mysql> select　*　from test;
+---------+------+
| Name        | Age        |
+---------+------+
| Sharukh   |　　　25　|
| Kalyan     |　　　30 |
+---------+------+
2　rows in set (0.00 sec)

PHP MySQLi Reference Manual