Using Transactions and Stored Procedures in MySQL - PHP and MySQL Integration - Sams Teach Yourself PHP, MySQL and Apache All in One (2012)

Sams Teach Yourself PHP, MySQL and Apache All in One (2012)

Part IV. PHP and MySQL Integration

Chapter 17. Using Transactions and Stored Procedures in MySQL


In this chapter, you learn the following:

The basics of transactions and how to use them in MySQL

The basics of stored procedures and how to create and access them in MySQL


In the preceding chapter, you learned the basics of SQL and how to use the MySQL command-line interface to issue queries and retrieve the results. Armed with only that knowledge, you can successfully complete the projects found in the remaining chapters of this book. However, when you move forward with your knowledge and think about building applications suitable for use in an enterprise environment, you might require more advanced tactics to maintain the integrity of your data and enhance your application’s communication with MySQL.

Although the remaining chapters in this book do not contain elements from this chapter—keeping the examples as simple as possible so that your foundational knowledge is strong—you can easily update the code on your own to include the information you learn in this chapter.

What Are Transactions?

Database transactions are sets of queries that must execute in such a way so that if one query fails to execute completely they all fail. For instance, suppose that you have a set of three queries, the second dependent on the results of the first, and the third dependent on the results of the second. If the second query fails, you need to have a way to negate the results of the first query; similarly, if the third query fails, you need to negate the results of the first and second queries, as well.

By instituting transactional processing in your database-driven applications, you ensure the integrity of the data stored in your database. The following sections describe the process of using transactions both through the command-line interface and PHP functions.


Note

Fully transactional tables in MySQL are available when using the InnoDB storage engine. Beginning with MySQL 5.5.5, InnoDB is the default storage engine for new tables (see http://dev.mysql.com/doc/refman/5.5/en/innodb-default-se.html for more information). If your tables are of the MyISAM type, they will not be fully transactional.


Basic Syntax Used in Transactions

You need to understand the following key terms when thinking about using transactions with MySQL:

COMMIT—This command occurs at the end of the series of queries in your transaction and is issued only if all the required queries have executed successfully.

ROLLBACK—This command is used when one or more of the series of queries in your transaction fails and resets the affected tables to their pretransaction state.

Going back to the scenario mentioned at the start of this discussion, of three queries dependent on each other, a sequence of events in the MySQL command-line interface might look something like this:

1. Issue the BEGIN command to begin a new transaction.

2. Select a value from table1 to insert into table2.

3. If a value cannot be selected from table1, issue a ROLLBACK command to ensure that the transaction ends and that the tables return to their previous state.

4. If a value can be selected from table1, insert a value into table2.

5. If the insertion of a record into table2 fails, issue a ROLLBACK command to ensure that the transaction ends and that the tables return to their previous state.

6. If a value can be inserted into table1, insert a value into table2.

7. If the insertion of a record into table3 fails, issue a ROLLBACK command to ensure that the transaction ends and that the tables return to their previous state.

8. However, if the insertion of a record into table3 is successful, issue a COMMIT command to ensure that the transaction ends and that the tables update appropriately.

For more information on the inner workings of transactions in MySQL, see the MySQL Manual at http://dev.mysql.com/doc/refman/5.5/en/sql-syntax-transactions.html.

The next section provides an example of transactions as used with inventory and sales records tables.

Working Example Using Transactions

For the purposes of this example, suppose that you’ve created an online storefront with database tables that hold inventory, sales records, and the line items for the sales records. The CREATE statements might look something like this:

CREATE TABLE store_inventory (
id int not null primary key auto_increment,
item_name varchar(50),
item_price float(6,2),
item_qty int
) ENGINE=InnoDB;

CREATE TABLE store_orders (
id int not null primary key auto_increment,
purchaser_name varchar(50),
purchase_date datetime
) ENGINE=InnoDB;

CREATE TABLE store_orders_items (
id int not null primary key auto_increment,
order_id int,
inventory_id int,
item_qty int
) ENGINE=InnoDB;

In the store_inventory table for this example, you can find two records:

+----+------------+------------+----------+
| id | item_name | item_price | item_qty |
+----+------------+------------+----------+
| 1 | Great Book | 19.99 | 10 |
| 2 | Awesome CD | 9.99 | 20 |
+----+------------+------------+----------+

If a shopper wants to purchase two Great Books and one Awesome CD through your online store, the process would go something like this:

1. The user completes an online form and attempts to pay for the purchases, so issue a BEGIN command for the transaction that would be part of the checkout script:

BEGIN;

2. Decrement the quantity of items in the store_inventory table:

UPDATE store_inventory SET item_qty = item_qty - 2 WHERE id = 1;
UPDATE store_inventory SET item_qty = item_qty - 1 WHERE id = 2;

3. Add a record to the store_orders table:

INSERT INTO store_orders (purchaser_name, purchase_date)
VALUES ('John Smith', now());

4. If adding the record fails, issue a ROLLBACK command to reset the available quantity of the items:

ROLLBACK;

5. If adding the record succeeds, get the ID of the record just added and use it in your query to add line items to the sales record by inserting records in the store_orders_items table:

INSERT INTO store_orders_items (order_id, inventory_id, item_qty)
VALUES ('1', '1', '2');
INSERT INTO store_orders_items (order_id, inventory_id, item_qty)
VALUES ('1', '2', '1');

6. If adding the records fails, issue a ROLLBACK command to reset the available quantity of the items and remove the record in store_orders:

ROLLBACK;

7. If adding the records succeeds but the subsequent charging of a credit card or other payment method fails, issue a ROLLBACK command to reset the available quantity of the items, remove the record in store_orders, and remove the records in store_orders_items:

ROLLBACK;

8. If adding the records succeeds and the subsequent charging of a credit card or other payment method succeeds, issue a COMMIT command to ensure that all the changes are stored and the transaction ends:

COMMIT;

Of course, an online storefront does not directly interface with MySQL via the command-line interface but rather through a scripting language such as PHP. However, if you understand the processes behind the transaction, plugging it into PHP is simple—issuing the queries and commands listed previously is no different from any other PHP-to-MySQL communication, which you learn about in Chapter 18, “Interacting with MySQL Using PHP.”

In addition to the information you learn in Chapter 18, be sure to review these function definitions in the PHP Manual if you intend to use transactions in your scripts:

mysqli_autocommit()http://www.php.net/mysqli_autocommit

mysqli_commit()http://www.php.net/mysqli_commit

mysqli_rollback()http://www.php.net/mysqli_rollback

I also can’t stress enough that you review the MySQL Manual for more information on transactions, at http://dev.mysql.com/doc/refman/5.5/en/sql-syntax-transactions.html, especially the areas that discuss the types of transactions that cannot be rolled back—administrative actions, for the most part, but still useful to know.

What Are Stored Procedures?

Simply put, a stored procedure is a procedure in SQL that is stored in the database server rather than the web server. You might be thinking that you don’t store any procedures on the web server, but in fact you do: Any script that contains SQL queries counts as a procedure stored on the web server. For example, every query in your application that selects, deletes, updates, or inserts data into tables—which you will have painstakingly coded in your scripts—can be stored in the database as a stored procedure and referenced as such in your scripts.

Proponents of using stored procedures in code point to performance and maintenance as key reasons for doing so:

Better performance—Stored procedures exist as precompiled SQL in the database, so a typical two-step process (compile and execute) becomes a single-step process (execute).

Ease of maintenance—Maintaining one statement in one place (the database) is significantly less time-consuming than maintaining one statement in numerous places, such as all through scripts on your web server. In addition, storing all your statements in the database as opposed to actual text files in your web server document root is one more line of defense should someone gain access to the files on your web server; all that person has are queries that call stored procedures instead of the logic of the procedure itself.

An example of a useful stored procedure is the SQL query used to generate a report of some sort—be it financial data, sales inventory, or otherwise; just imagine a complex query that involves a lot of processing. Creating a stored procedure out of this type of query goes along with the performance benefits of stored procedures. If you have a simple query used often throughout your application, creating a stored procedure for it would go along with the maintenance benefits of stored procedures. Regardless of the simplicity or complexity of the stored procedure, creating and using it follows the same basic process.

This stored procedure example uses the following table:

CREATE TABLE testSP (
id int not null primary key auto_increment,
field_name varchar(25),
date_added datetime
) ENGINE=InnoDB;

The values of this table for testing purposes are as follows:

+----+------------+---------------------+
| id | field_name | date_added |
+----+------------+---------------------+
| 1 | Value 1 | 2012-01-23 09:40:24 |
| 2 | Value 2 | 2012-01-24 09:40:24 |
| 3 | Value 3 | 2012-01-25 09:40:24 |
| 4 | Value 4 | 2012-01-26 09:40:24 |
| 5 | Value 5 | 2012-01-27 09:40:24 |
| 6 | Value 6 | 2012-01-30 09:40:24 |
| 7 | Value 7 | 2012-01-31 09:40:24 |
| 8 | Value 8 | 2012-02-01 09:40:24 |
| 9 | Value 9 | 2012-02-02 09:40:24 |
| 10 | Value 10 | 2012-02-14 09:40:24 |
+----+------------+---------------------+

Moving past this sample table and into the stored procedure syntax, you next must ensure that MySQL knows the delimiter character you’ll be using in your stored procedures. This example uses // as the delimiter, so you issue the following query:

DELIMITER //

The syntax for creating a basic stored procedure is as follows:

CREATE PROCEDURE procedure_name () query //

For this example, the stored procedure simply selects all data from the testSP table that has been added in the past 7 days. The name of the stored procedure is sp1:

CREATE PROCEDURE sp1 () SELECT * FROM testSP WHERE date_added BETWEEN
DATE_SUB(NOW(), INTERVAL 7 DAY) AND NOW() //

To call the stored procedure, use the CALL command:

CALL sp1 () //

The results of the stored procedure (the SELECT query) are returned to you:

+----+------------+---------------------+
| id | field_name | date_added |
+----+------------+---------------------+
| 2 | Value 2 | 2012-01-24 09:40:24 |
| 3 | Value 3 | 2012-01-25 09:40:24 |
| 4 | Value 4 | 2012-01-26 09:40:24 |
| 5 | Value 5 | 2012-01-27 09:40:24 |
| 6 | Value 6 | 2012-01-30 09:40:24 |
+----+------------+---------------------+

In Chapter 18, you learn the process for issuing these and all SQL queries using PHP.

Clearly, these few pages do not even begin to scratch the surface of working with stored procedures. This section intends only to introduce you to the concept. Additional recommended reading includes the MySQL Manual entries on stored procedures, athttp://dev.mysql.com/doc/refman/5.5/en/stored-routines.html.

Summary

This short chapter provided an introduction to the concepts of transactional processing and the use of stored procedures in MySQL when using the InnoDB storage engine. In addition to a brief overview of both topics, real-life examples described each of these advanced topics. The overall purpose of this chapter was to introduce you to some concepts and keywords you should understand by the time you are developing enterprise applications on your own. Keep these ideas in the back of your mind as you work through the examples in this book and try to find ways to improve on the basic queries used.

Q&A

Q. Do I have to use transactions all the time now that MySQL supports them?

A. No, especially not if the dynamic aspect of your application or site is for dynamic display of data and not for dynamic insertion of data. In addition, if the insertion of data is not necessarily related to any financial- or inventory-related actions, you could get away with not using transactions. In other words, if you do not use transactions and an insert or update query fails, be sure that you can live with the failure—either because no money or crucial customer data would be lost.

Workshop

The workshop is designed to help you review what you’ve learned and begin putting your knowledge into practice.

Quiz

1. True or False: MyISAM is the default and fully transactional storage engine in MySQL.

2. If step two of a three-step transaction fails, what command would you issue?

3. What are two advantages of using stored procedures?

Answers

1. False. InnoDB is the default storage engine and supports full transactions.

2. ROLLBACK

3. Better performance and ease of maintenance

Activities

1. Think of some additional types of database interactions that might require transactions, and create your own set of tables like those shown in this chapter. Practice issuing and rolling back commands in all the logical places where your interactions could call for them.

2. Create a stored procedure (and its required tables) that reports on all sales transactions that include a specific item, that took place between two given dates.