This is a well-written article by Joel about MySQL transactions. If you are new to MySQL 4.0, then you must read this so you can ensure data integrity.
With the arrival of MySQL 4.0, you finally have full support of Transactions. This course will help you get started using this great new feature using PHP.
Requirements for this Article
- MySQL and PHP installed on a local or remote server.
- Knowledge of using the MySQL Monitor program.
- MySQL connectivity using PHP.
- MySQL Version 4.0.12 using InnoDB type tables.
You have heard of Transacations but you are not sure on how they are used. They are simple to utilize after you learn the basics.
Lets start with defining Transactions. A good definition would be the following:
"Transaction Processing is used to maintain database integrity by ensuring that SQL operations execute completely or not at all."
Thats fine for a definition but a visual example would be better.
If you go to the store and take 3 of the 10 items on the shelf, that leaves 7 left for others to purchase. You decide to checkout and make your way to the purchase your items. If you agree to buy the items then you give the merchant your money and COMMIT to the Transaction. After you receive your receipt the Transaction is completed.
You have learned all there is to know about Transactions.
Not really, but its a start.
Here are the steps for using Transactions in MySQL:
- BEGIN the Transaction.
- Update, insert or delete entries in the database.
- If you like the changes to the database, then you COMMIT to the Transaction.
- If you do not like the changes then you ROLLBACK the changes to the original condition of the database.
Note: You must use InnoDB type tables or Transactions, will not work.
Lets create a basic table in your database called "trans", and make the table type "innodb".
CREATE TABLE trans
(
id int not null auto_increment,
item varchar(30) not null,
quantity varchar(10) not null,
primary key(id)
)type=innodb;
Heres the contruction of the table:
mysql> DESC trans;
| Field | Type | Null | Key | Default | Extra |
| id | int(11) | | PRI | NULL | auto_increment |
| item | varchar(30) | | | | |
| quantity | varchar(10) | | | | |
3 rows in set (0.00 sec)
Insert some data into the table:
mysql> INSERT INTO trans (id,item,quantity) VALUES (NULL,'Computer','5');
Query OK, 1 row affected (0.00 sec)
Begin the Transaction by using the BEGIN command and update the entry:
mysql> BEGIN;
Query OK, 0 rows affected (0.00 sec)
mysql> UPDATE trans SET quantity ='4' WHERE id=1;
Query OK, 1 row affected (0.01 sec)
Rows matched: 1 Changed: 1 Warnings: 0
View the results of the update:
mysql> SELECT * FROM trans;
| id | item | quantity |
| 1 | Computer | 4 |
1 row in set (0.00 sec)
If you do not like the changes, then use the ROLLBACK command to revert back to the original version of the table.
mysql> ROLLBACK;
Query OK, 0 rows affected (0.00 sec)
Notice the table has reverted back to the original insertion:
mysql> SELECT * FROM trans;
| id | item | quantity |
| 1 | Computer | 5 |
1 row in set (0.01 sec)
Lets update the table using another Transaction and commit to the changes:
mysql> BEGIN;
Query OK, 0 rows affected (0.00 sec)
mysql> UPDATE trans SET quantity ='2' WHERE id=1;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0
mysql> SELECT * FROM trans;
| id | item | quantity |
| 1 | Computer | 2 |
1 row in set (0.00 sec)
mysql> COMMIT;
Query OK, 0 rows affected (0.00 sec)
After you use the COMMIT command, the table will take on the changes and remain that way until they are modified.
mysql> SELECT * FROM trans;
| id | item | quantity |
| 1 | Computer | 2 |
1 row in set (0.00 sec)
Now that you understand the basics of Transactions lets create a PHP script that will insert new data into the table.
Here is the code for the transaction script:
<?php
// trans.php
function begin()
{
@mysql_query("BEGIN");
}
function commit()
{
@mysql_query("COMMIT");
}
function rollback()
{
@mysql_query("ROLLBACK");
}
@mysql_connect("localhost","username", "password") or die(mysql_error());
@mysql_select_db("test") or die(mysql_error());
$query = "INSERT INTO trans (id,item,quantity)
values (null,'Baseball',4)";
begin(); // transaction begins
$result = @mysql_query($query);
if(!$result)
{
rollback(); // transaction rolls back
echo "you rolled back";
exit;
}
else
{
commit(); // transaction is committed
echo "your insertion was successful";
}
?>
Explanation of the script
- Functions are created for the BEGIN, COMMIT and ROLLBACK commands.
- The script connects to the server and runs the query of inserting data into the table.
- If the query is successful then it COMMITS the Transaction.
- If the query is unsuccessful then the Transaction will ROLLBACK.
Here is the table after the script executes:
mysql> SELECT * FROM trans;
| id | item | quantity |
| 1 | Computer | 2 |
| 2 | Baseball | 4 |
2 rows in set (0.00 sec)
I hope that this gets you started using Transactions in MySQL 4.0 using PHP. Its a great feature and will open up new ideas when building web applications.
Copyright 2003 - Written by Joel Philip for DevArticles - All Rights Reserved.
| DISCLAIMER: The content provided in this article is not warranted or guaranteed by Developer Shed, Inc. The content provided is intended for entertainment and/or educational purposes in order to introduce to the reader key ideas, concepts, and/or product reviews. As such it is incumbent upon the reader to employ real-world tactics for security and implementation of best practices. We are not liable for any negative consequences that may result from implementing any information covered in our articles or tutorials. If this is a hardware review, it is not recommended to open and/or modify your hardware. |
More MySQL Articles
More By Joel Philip
 developerWorks - FREE Tools!
|
Learn field-tested SOA principles, methodology, technology and implementation from the global SOA market leader - in a new e-book by an IBM SOA expert. Written by IBM Certified SOA Solution Designer Bobby Woolf, "Exploring IBM SOA Technology & Practice" is the ultimate insider's guide to SOA - a PDF e-book packed cover to cover with IBM's specific advice on how to make your SOA implementation a success.
FREE! Go There Now!
|
|
|
|
CakePHP is a stable production-ready, rapid-development aid for building Web sites in PHP. This "Cook up Web sites fast with CakePHP" series shows you how to build an online product catalog using CakePHP.
FREE! Go There Now!
|
|
|
|
WebSphere Process Server delivers a unique integration framework that simplifies existing IT resources. Often, as IT assets grow to support business demand, so too does their complexity and manageability. In this webcast, we’ll discuss how WebSphere Process Server helps deliver an SOA infrastructure that provides a common model to orchestrate, mediate, connect, map, and execute the underlying IT functions. Discover how WebSphere Process Server simplifies integration of business processes by leveraging existing IT assets as reusable services without the complexities of traditional integration methodologies.
FREE! Go There Now!
|
|
|
|
Download the Rational Application Developer (RAD) v7.5 open beta code and start developing applications for the JEE5 standard which features EJB3.0, JPA, JSF 1.2, JSP 2.1 and Servlet 2.5 standards. When you use this beta you will see how you can increase developer productivity for already existing applications with improved support for refactoring, as well as adding new features to existing applications. In addition, the beta provides tooling for JD Edwards, Oracle, SAP, Siebel and PeopleSoft to improve the developer productivity with these enterprise systems.
FREE! Go There Now!
|
|
|
|
Download the IBM WebSphere Portal V6.1 beta code and learn more about the rich features and enhancements in IBM WebSphere Portal V6.1. WebSphere Portal provides a composite application or business mashup framework and the advanced tooling needed to build flexible, SOA-based solutions, and scalability to meet the needs of any size organization.
FREE! Go There Now!
|
|
|
|
Learn how Rational Build Forge can extend a simple compile and package build process by adding customization and deployment capability. Go from a manual method to automating: checking for code changes; getting the latest source; compiling and packaging; customizing; copying to and restarting a deployment server; and sending e-mail notification that a new version is available.
FREE! Go There Now!
|
|
|
|
XML has become a common way of storing business data as flat files and many data server vendors including IBM have provided ways to store this data within relational database systems. Increasingly collections of XML files are accessed like databases using an xQuery and other XML standard mechanisms. Businesses find the need to combine the traditional tabular structured data with XML formatted data. In this webcast, you’ll learn about IBM’s WebSphere Federation Server technology, which provides users with the ability to integrate these two data formats.
FREE! Go There Now!
|
|
|
|
Rational Modeling Extension for Microsoft .NET enhances usability for code generation supporting a more intelligent refactoring. The latest enhancements enable organizations with Java and .NET systems and software development maintain architectural integrity across heterogeneous platforms.
FREE! Go There Now!
|
|
|
|
You can now evaluate IBM Rational Asset Manager V7.0 online without installing or configuring it on your own system! Rational Asset Manager helps create, modify, govern, find, and reuse any type of development assets, including SOA and systems development assets. Rational Asset Manager helps you reduce software development costs and improve quality by facilitating the reuse of all types of software development-related assets. Visit developerWorks to learn more about this product and register to explore its capabilities online.
FREE! Go There Now!
|
|
|
|
Join this webcast to learn how IBM Rational's Functional Testing solution enables you to implement automation your way, at your pace, with your existing staff. In this webcast, you’ll learn how you can eliminate redundancy of manual test scripts, reduce errors, and increase test coverage through test automation. After this presentation you will understand how IBM Rational Functional Testing solution can streamline your manual testing and make test automation easily attainable.
FREE! Go There Now!
|
|
|
|
All FREE IBM® developerWorks Tools!
|
/ 47 
