Implementing the commit() and rollback() Methods with mysqli and PHP 5 - Canceling database modifications: using the “rollback()” method

(Page 3 of 4 )

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.

Next: Escaping strings, counting rows and more: using the “real_escape_string()” method and the “affected_rows” property >>
 

More MySQL Articles
More By Alejandro Gervasio

Comments On: Implementing the commit() and rollback() Methods with mysqli and PHP 5

· In this second installment of the series, the use of the "commit()" and rollback()...    · what about MyISAM type databases, can same methods work with MyISAM DBs. If not,...    · Thank you for commenting on my article. With regard to your question, you have some...    · Thank you for this tutorial. As I was implementing transactions into my project I...    · I made a mistake. I was using mysqli->multi_query(), this prevented the transaction...    · Hello again Rafael,Thank you for posting your comments here, and also I'm glad...   >>> Post your comment now!