Database transactions are simply sets of queries that must be executed in such a way so that if one fails to execute completely, they all fail. For instance, say 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 are ensuring 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. Basic Syntax Used in TransactionsYou will need to understand the following key terms when thinking about using transactions with MySQL:
Going back to the example used previously, of three queries dependent on each other, a sequence of events in the MySQL command-line interface might look something like this:
For more information on the inner workings of transactions in MySQL, see the MySQL Manual at http://dev.mysql.com/doc/refman/5.0/en/transactional-commands.html. In the next section, you'll see an example of transactions as used with tables concerning inventory and sales records. Working Example Using TransactionsFor the purposes of this example, imagine that you've created an online storefront with database tables that hold inventory, sales records, and the line items for the sales records: mysql> DESC store_inventory; +------------+-------------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +------------+-------------+------+-----+---------+----------------+ | id | int(11) | NO | PRI | | auto_increment | | item_name | varchar(25) | YES | | | | | item_price | float(6,2) | YES | | | | | item_qty | int(11) | YES | | | | +------------+-------------+------+-----+---------+----------------+ 4 rows in set (0.02 sec) mysql> DESC store_orders; +----------------+-------------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +----------------+-------------+------+-----+---------+----------------+ | id | int(11) | NO | PRI | | auto_increment | | purchaser_name | varchar(50) | YES | | | | | purchase_date | datetime | YES | | | | +----------------+-------------+------+-----+---------+----------------+ 3 rows in set (0.00 sec) mysql> DESC store_orders_lineitems; +--------------+---------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +--------------+---------+------+-----+---------+----------------+ | id | int(11) | NO | PRI | | auto_increment | | order_id | int(11) | YES | | | | | inventory_id | int(11) | YES | | | | | item_qty | int(11) | YES | | | | +--------------+---------+------+-----+---------+----------------+ 4 rows in set (0.00 sec) In the store_inventory table for this example you can find two records: mysql> SELECT * FROM store_inventory; +----+------------+------------+----------+ | id | item_name | item_price | item_qty | +----+------------+------------+----------+ | 1 | Great Book | 19.99 | 10 | | 2 | Awesome CD | 9.99 | 20 | +----+------------+------------+----------+ 2 rows in set (0.00 sec) If a shopper wants to purchase two Great Books and one Awesome CD through your online store, the process would go something like this:
Of course, an online storefront would not directly interface with MySQL via the command-line interface but rather through a scripting language such as PHP. But if you understand the processes behind the transaction, plugging it into PHP is simpleissuing the queries and commands listed previously. lt is no different from any other PHP-to-MySQL communication, which you will learn about in Chapter 18, "Interacting with MySQL Using PHP." In addition to the information you'll learn in Chapter 18, be sure to review these function definitions in the PHP Manual if you intend to use transactions in your scripts:
|