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

Working with “InnoDB” tables: using the “commit()” and “autocommit()” methods - 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.

  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



In order to use the “commit()” and “autocommit()” methods, you should define your database tables as being of type “InnoDB.” Other types, such as “MyISAM” or “ISAM” won’t support these features. Keep this limitation in mind before implementing the methods that I mentioned before.

Having clarified the previous issue, let me describe briefly the meaning of the COMMIT and ROLLBACK statements. In short, the COMMIT statement means that the changes made during a transaction to a particular database (or a set of databases) are permanent and become visible to other users. On the other hand, as you may have guessed, a ROLLBACK statement will allow you to cancel the modifications made during the current transaction. Quite simple, right?

Since the above mentioned features are only available in “InnoDB” tables, here is an example that shows hot to use the “commit()” and “autocommit()” methods, assuming that the correct type of table is used:

// commit-autocommit example that uses an InnoDB table
$mysqli=new mysqli('host','user','password','database');
    trigger_error('Error connecting to host. '.$mysqli-
// turn off AUTOCOMMIT, then run some queries
$mysqli->query("INSERT INTO customers (id,name,email) VALUES
$mysqli->query("INSERT INTO customers (id,name,email) VALUES
// commit transaction
// close connection

In this case, I used three different methods that come with the “mysqli” extension, after performing the corresponding connection to the MySQL server and selecting the appropriate database. The first one, “autocommit()”, turns off the AUTOCOMMIT feature of MySQL, which means that the current transaction will be open for the selected user. Then, the script inserts two new customers into the sample “CUSTOMERS” table via the familiar “query()” method, and finally it commits the transaction, obviously by using the “commit()” method.

As you can see, using the AUTOCOMMIT and COMMIT features available in “InnoDB” tables is a fairly understandable process, which can be performed by using a few simple methods. Now, let’s move on and see how the “ROLLBACK” feature can be implemented with the “mysqli” extension. That’s exactly the subject of the next section. 

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

blog comments powered by Disqus
escort Bursa Bursa escort Antalya eskort


- 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: