PHP Basic Tutorial

PHP Advanced Tutorial

PHP & MySQL

PHP Reference Manual

PHP CURL Functions PHP OpenSSL Functions

PHP mysqli_rollback() Function Usage and Example

The mysqli_rollback() function rolls back the current transaction

Definition and Usage

MySQL database has the feature of automatic transaction commit. If you turn it on, the changes made in the database will be automatically saved; if you turn it off, you need to explicitly save the changes using the mysqli_commit() function.

mysqli_rollback()The function rolls back the current transaction to the last save point (or the specified save point).

Syntax

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

Parameter

Serial number	Parameters and descriptions
1	con (required) This is an object representing the connection to the MySQL Server.
2	flags (optional) It can be one of the following constants: 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, when specified, it will be used as ROLLBACK /* name */ execution.

Return value

The mysqli_rollback() function returns a boolean value, if the operation is successful, it returnstrue,otherwisefalse.

PHP version

This function was initially introduced in PHP version5introduced and available in all higher versions.

Online Example

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

Example

<?php
　　　//Establish connection
　　　$con = mysqli_connect("localhost", "root", "password", "mydb");
　　　//Set automatic commit to false
　　　mysqli_autocommit($con, False);
　　　mysqli_query($con, "CREATE TABLE IF NOT EXISTS my_team(ID INT, First_Name VARCHAR(255), Last_Name VARCHAR(255), Place_Of_Birth VARCHAR(255), Country VARCHAR(255))');
　　　//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(4The array contains 'Virat', 'Kohli', 'Delhi', 'India')");
　　　$res = mysqli_query($con, 'SELECT　*　FROM my_team);
　　　print('Number of rows (at commit time): '.mysqli_affected_rows($con).'
');
　　　//Save changes
　　　mysqli_commit($con);
　　　//Truncate table
　　　mysqli_query($con, 'DELETE FROM my_team where id in(3,4);　
　　　$res = mysqli_query($con, 'SELECT　*　FROM my_team);
　　　print('Number of rows (before rollback): '.mysqli_affected_rows($con).'
');
　　　//rollback
　　　mysqli_rollback($con);
　　　//Contents of the table
　　　$res = mysqli_query($con, 'SELECT　*　FROM my_team);
　　　print('Number of rows (after rollback): '.mysqli_affected_rows($con));
　　　//Close Connection
　　　mysqli_close($con);
?>

Output Result

Number of rows (at commit time):　4
Number of rows (before rollback):　2
Number of rows (after rollback):　4

Online Example

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

Example

//Establish connection
$con = new mysqli('localhost', 'root', 'password', 'mydb');
//Set automatic commit to false
$con->autocommit(FALSE);
//Insert a record into the players table
$con->query('CREATE TABLE IF NOT EXISTS players(First_Name VARCHAR(255), Last_Name VARCHAR(255), Country VARCHAR(255))');
$con->query('insert into players values('Shikhar', 'Dhawan', 'India')');
$con->query('insert into players values('Jonathan', 'Trott', 'SouthAfrica')');
//Save results
$con->commit();
$con->query('insert into players values('Kumara', 'Sangakkara', 'Srilanka')');
$con->query('insert into players values('Virat', 'Kohli', 'India')');
//rollback
$con-> rollback();
$res = $con-> query("SELECT　*　FROM players);
print_r($res);
//Close Connection
$res = $con　-> close();
?>

Output Result

mysqli_result Object
(
　　　　[current_field] => 0
　　　　[field_count] =>　3
　　　　[lengths] =>
　　　　[num_rows] =>　2
　　　　[type] => 0
)

Online Example

Disable automatic commit, perform some queries, commit the queries, and then rollback the current transaction:

Example

<?php
　　　$connection = mysqli_connect("localhost", "root", "password", "mydb");
　　　
　　　if (mysqli_connect_errno($connection)){
　　　　　　echo "Connection to MySQL failed: " . 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_commit($connection);
　　　
　　　mysqli_query($connection, "INSERT INTO test VALUES ('Kalyan',　30)
　　　
　　　mysqli_rollback($connection);
　　　mysqli_close($connection);
?>

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

mysql> select　*　from test;
+---------+------+
| Name | Age |
+---------+------+
| Sharukh |　　　25　|
+---------+------+
1　row in set (0.00 sec)

PHP MySQLi Reference Manual

PHP CURL Functions PHP OpenSSL Functions