Canceling database modifications: using the “rollback()” method - MySQL

As I explained in the previous section, the ROLLBACK feature allows you to cancel all the modifications that were made during the course of a particular transaction. As usual, the best way to understand this concept is by example, therefore have a look at the following script, which uses the “rollback()” method to implement the corresponding ROLLBACK feature:

// example of rollback transaction
$mysqli=new mysqli('host','user','password','database');
if(mysqli_connect_errno()){
    trigger_error('Error connecting to host. '.$mysqli-
>error,E_USER_ERROR);
}
// turn off AUTOCOMMIT, then run some queries
$mysqli->autocommit(FALSE);
// delete all rows in 'customers' table
$mysqli->query("DELETE FROM customers");
// display number of rows
if($result=$mysqli->query("SELECT COUNT(*) FROM customers")){
    $row=$result->fetch_row();
    echo 'Number of rows in CUSTOMERS table '.$row[0];
    // free result set
    $result->close();
}
// rollback transaction
$mysqli->rollback();
// display number of rows
if($result=$mysqli->query("SELECT COUNT(*) FROM customers")){
    $row=$result->fetch_row();
    echo 'Number of rows in CUSTOMERS table '.$row[0];
    // free result set
    $result->close();
}
// close connection
$mysqli->close();

As shown above, after disabling the AUTOCOMMIT feature (AUTOCOMMIT==FALSE), the previous script deletes all the rows from the sample “CUSTOMERS” database table, and next displays the number of rows. Assuming that the “CUSTOMERS” table was populated with the following records:

1 User 1  user1@domain.com
2 User 2  user2@domain.com
3 User 3  user3@domain.com
4 User 4  user4@domain.com
5 User 5  user5@domain.com

then, obviously, after deleting all the respective records, the number of returned rows will be equal to zero:

Number of rows in CUSTOMERS table 0

Now, it’s possible to cancel all the table modifications made previously by calling the “rollback()” method, as illustrated below:

$mysqli->rollback();

As expected, the “rollback()” method cancels the deletion of all the records performed previously, therefore after counting the number of rows, this time the result will be the following:

Number of rows in CUSTOMERS table 5

As you can see, the combination of the respective “autocommit(), “commit()” and “rollback()” methods makes it very convenient to handle programmatically the AUTOCOMMIT, COMMIT and ROLLBACK features available in MySQL 4.1 and above with relative ease.

Well, provided that you already understand the functionality of the methods that I covered before, the next step rests on exploring some additional methods included within the “mysqli” extension. These can be helpful when performing some basic operations with MySQL.