Home arrow MySQL arrow Page 3 - Implementing the commit() and rollback() Methods with mysqli and PHP 5

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

If you want to use the "mysqli" extension, you've come to the right place. This is the second part of the series “Using mysqli with PHP 5.” Welcome back. In three tutorials, this series shows how to use the most important methods and properties included in the “mysqli” extension that comes with PHP 5, in order to take advantage of the new features bundled with MySQL 4.1 and above.

TABLE OF CONTENTS:
  1. Implementing the commit() and rollback() Methods with mysqli and PHP 5
  2. Working with “InnoDB” tables: using the “commit()” and “autocommit()” methods
  3. Canceling database modifications: using the “rollback()” method
  4. Escaping strings, counting rows and more: using the “real_escape_string()” method and the “affected_rows” property
By: Alejandro Gervasio
Rating: starstarstarstarstar / 20
July 03, 2006

print this article
SEARCH DEV SHED

TOOLS YOU CAN USE

advertisement

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.



 
 
>>> More MySQL Articles          >>> More By Alejandro Gervasio
 

blog comments powered by Disqus
escort Bursa Bursa escort Antalya eskort
   

MYSQL ARTICLES

- Oracle Unveils MySQL 5.6
- MySQL Vulnerabilities Threaten Databases
- MySQL Cloud Options Expand with Google Cloud...
- MySQL 5.6 Prepped to Handle Demanding Web Use
- ScaleBase Service Virtualizes MySQL Databases
- Oracle Unveils MySQL Conversion Tools
- Akiban Opens Database Software for MySQL Use...
- Oracle Fixes MySQL Bug
- MySQL Databases Vulnerable to Password Hack
- MySQL: Overview of the ALTER TABLE Statement
- MySQL: How to Use the GRANT Statement
- MySQL: Creating, Listing, and Removing Datab...
- MySQL: Create, Show, and Describe Database T...
- MySQL Data and Table Types
- McAfee Releases Audit Plugin for MySQL Users

Developer Shed Affiliates

 


Dev Shed Tutorial Topics: