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

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.

Introduction

Stepping back for a moment to the topics that I covered in the first tutorial, you’ll probably remember how to connect to the MySQL server and use the “multi_query()” method for running multiple queries, in addition to handling several result sets at the same time. This can definitely can introduce significant changes to the way that you develop your MySQL-based applications.

Also, I’d like to mention the great functionality provided by this extension to bind different queries to specific parameters, which are replaced with real values during the execution of an application. This particular feature allows the definition of “generic” queries that eventually will be attached to different arguments, resulting in an easier and more flexible process for generating dynamic queries.

All right, I believe that all the topics I mentioned before are now fresh in your mind. Therefore it’s time to move on and focus our attention on the subject of this second article, so you know what to expect from it. Over the course of this installment, I’ll explain how to use the “commit()” and “rollback()” methods included within the “mysqli” library, among other handy methods, which hopefully will provide you with a more robust grounding in the capabilities of MySQL 4.1 and above.

Are you ready to continue learning more about all these useful features? Okay, let’s get started!

{mospagebreak title=Working with “InnoDB” tables: using the “commit()” and “autocommit()” methods}

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’);
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);
$mysqli->query(“INSERT INTO customers (id,name,email) VALUES
(NULL,’customer1′,’email1@domain.com’)”);
$mysqli->query(“INSERT INTO customers (id,name,email) VALUES
(NULL,’customer2′,’email2@domain.com’)”);
// commit transaction
$mysqli->commit();
// close connection
$mysqli->close();

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. 

{mospagebreak title=Canceling database modifications: using the “rollback()” method}

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.

{mospagebreak title=Escaping strings, counting rows and more: using the “real_escape_string()” method and the “affected_rows” property}

Escaping conflictive characters before inserting data into the tables of a database has always been a problematic topic, since most of the time it’s not properly addressed. Luckily, the “mysqli” extension also comes with a useful method for escaping input data. Here, I’m talking about the “real_escape_string()” method, which works in a way closely similar to its “cousin,” that is, the “mysql_escape_string()” function.

Having introduced this method, here is a simple example that shows how to use it. Take a look:

// escaping single quotes
$mysqli=new mysqli(‘host’,’user’,’password’,’database’);
if(mysqli_connect_errno()){
    trigger_error(‘Error connecting to host. ‘.$mysqli-
>error,E_USER_ERROR);
}
$customerName=”Sandra Zet’s Smith”;
$customerName=$mysqli->real_escape_string($customerName);
// run query
$mysqli->query(“INSERT INTO CUSTOMERS (id,name,email) VALUES
(NULL,’$customerName’,’customer1@domain.com’)”);
// close connection
$mysqli->close();

As you can see, the above example is extremely simple, and shows a typical case for escaping single quotes on a given string, before proceeding to insert this data into a sample database table. Nearly identical to the “mysql_escape_string()” function, this one will escape single and double quotes, new lines, NULL characters and semicolons, which makes escaping potentially-conflictive data a no-brainer process.

Now that you know how the “real_escape_string()” works, let’s examine a couple of properties that can be used for counting rows, after performing a specific query. The first property that I’ll explain is “affected_rows,” which comes in handy for counting the number of rows that have been affected after running a SQL query.

With reference to this property, I wrote an example below that demonstrates how to use it. Please examine the corresponding source code:

// using the ‘affected_rows’ property
$mysqli=new mysqli(‘host’,’user’,’password’,’database’);
if(mysqli_connect_errno()){
    trigger_error(‘Error connecting to host. ‘.$mysqli-
>error,E_USER_ERROR);
}
$mysqli->query(“SELECT * FROM customers WHERE id>5″);
echo ‘Number of affected rows: ‘.$mysqli->affected_rows;

In this case, the previous example doesn’t bear much discussion. What I did basically was use the property in question, in order to count the number of rows returned by a simple SELECT statement. Assuming that the sample “CUSTOMERS” database table originally held only two rows with an ID less than 5, then the result echoed by the prior code snippet would be the following:

Number of affected rows: 2

And since I’m talking about counting rows, there’s also another property that can be used for determining the number of rows contained in a result set. That’s precisely the functionality of the “num_rows” property, which can be utilized as follows:

// using the ‘num_rows’ property
$mysqli=new mysqli(‘host’,’user’,’password’,’database’);
if(mysqli_connect_errno()){
    trigger_error(‘Error connecting to host. ‘.$mysqli-
>error,E_USER_ERROR);
}
if($result=$mysqli->query(“SELECT * FROM customers”)){
    // display number of rows
    echo ‘Query returned the following number of
rows:<br />’.$result->num_rows;
    // close result set
    $result->close();
}
// close connection
$mysqli->close();

All right, the above example uses the “num_rows” property to determine the number of rows returned by the corresponding “SELECT” statement. However, it should be noticed that there’s a difference between the “affected_rows” property that you learned before and this one: the “affected_rows” property belongs to the “mysqli” class, while the current one is only a property of dynamically-generated result set objects. Thus, whenever you need to use one or both properties, be careful spotting the difference.

Finally, and returning to the above example, say you have a dozen records stored in the “CUSTOMERS” database table. The output echoed to the browser would be the following:

Query returned the following number of rows: 12

Although these examples might look rather trivial at first glance, I purposely kept all the source code simple, since I want you to learn properly how the methods and properties that I covered in this tutorial fit into the whole picture. If you have already grasped the concepts for putting the “mysqli” extension to work for you, then I must say my journey has almost finished.

Wrapping up

Over this second part of the series, I explained several methods and properties bundled with the “mysqli” library, to show you how to get the most out of them. During this tutorial, you hopefully learned how to handle the “COMMIT” and “ROLLBACK” features of MySQL 4.1 and above, as well as counting rows in result sets.

However, the series hasn’t ended yet. In the last article, I’ll be covering some additional methods, useful for seeking data within result sets, finding insertion IDs and much more. See you in the last part!

[gp-comments width="770" linklove="off" ]
antalya escort bayan antalya escort bayan