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

1. MySQL and PHP installed on a local or remote server.
2. Knowledge of using the MySQL Monitor program.
3. MySQL connectivity using PHP.
4. 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:

1. BEGIN the Transaction.
2. Update, insert or delete entries in the database.
3. If you like the changes to the database, then you COMMIT to the Transaction.
4. 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

1. Functions are created for the BEGIN, COMMIT and ROLLBACK commands.
2. The script connects to the server and runs the query of inserting data into the table.
3. If the query is successful then it COMMITS the Transaction.
4. 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!

Check out the new Jazz space on developerWorks <a href="http://zeus.developershed.com/shonuff.php?blackbird=3853&zoneid=442&source=&dest=http%3A%2F%2Fwww.ibm.com%2Fdeveloperworks%2Fspaces%2Fjazz%3FS_TACT%3D105AGY31%26S_CMP%3DDEVSHED&ismap="><img src="http://images.devshed.com/corp/img/news/jazz01.gif" alt="developerWorks Jazz space" align="left"></a>You've heard the buzz about Jazz... want to know more about it from a developer's perspective? Check out the Jazz space on developerWorks. This space is an up-to-date resource for developers, including technical information about Jazz and products built on Jazz, like Rational Team Concert Express. The Jazz space includes content from a wide variety of sources, including links, feeds, and comments from experts. FREE! Go There Now!

IBM DB2 Deep Compression ROI Tool The IBM DB2 Deep Compression ROI tool is designed for DBA’s and IT management personnel to perform a clinical analysis of the cost savings gained from the Storage Optimization feature of DB2 9 for Linux, UNIX and Windows. The feature, also known as Deep Compression, compresses data that lies within a database by up to 80% at times. FREE! Go There Now!

NEW! Accelerating Software Innovation on i on Power Systems Attend this launch webcast with Scott Hebner, Vice President of IBM Rational Marketing and Strategy, for an overview of Rational’s new software offerings and resources to help modernize and accelerate software innovation on i on Power Systems – while ensuring past application investments are protected and continue to grow. Learn how these solutions are helping customers extend their core i5/OS solutions toward modern architectures such as SOA and web technologies to deliver business improvements that stand the test of time. FREE! Go There Now!

NEW! Build Web services with transport-level security using Rational Application Developer V7, Part 1: Build Web services and Web services clients Build secure Web services with transport-level security using IBM Rational Application Developer V7 and IBM WebSphere Application Server V6.1. Follow this three-part series for step-by-step instructions about how to develop Web services and clients, configure HTTP basic authentication, and configure HTTP over SSL (HTTPS). This first part of the series walks you through building a Web service for a simple calculator application. You generate and test two different types of Web services clients: a Java Platform, Enterprise Edition (Java EE) client and a stand-alone Java client. You also handle user-defined exceptions in Web services. FREE! Go There Now!

NEW! Hello World: Learn how to install and use the Rational Asset Manager Eclipse client In this tutorial, you can learn how to install and configure the IBM Rational Asset Manager Eclipse client, explore the different views in the Asset Management perspective, learn various search techniques, work with existing assets, and submit a new asset. FREE! Go There Now!

NEW! Improve your build process with IBM Rational Build Forge, Part 1: Create a continuous build and integration environment Learn how to implement a build management system that uses and extends your existing automation technologies. This tutorial shows, step-by-step, how to install and configure IBM Rational Build Forge to manage builds for Jakarta Tomcat from source code. FREE! Go There Now!

NEW! Project and Portfolio Management Executive Resource Kit Portfolio Management is about effectively managing portfolio value by aligning portfolio investments with business goals. This complimentary e-kit provides a collection of materials that can help you understand how IBM Rational enables and automates best practices for improved governance and clear visibility into portfolio and project performance across the entire IT project lifecycle. FREE! Go There Now!

NEW! Rational Modeling Extension for Microsoft.Net 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!

NEW! Webcast: Calling All Testers! Find Application Vulnerabilities Early in the Development Process Where they are Easier to Fix and Less Risky to your Business In this webcast, IBM Rational will discuss the importance of Web application security and will share techniques and best practices to introduce application security testing into current QA processes including: understanding common security vulnerabilities and techniques to integrate security testing with defect tracking and remediation systems in an effort to safeguard sensitive online information. FREE! Go There Now!

Refresh! IBM Rational Systems Development Solution eKit With IBM Rational Systems Development Solution, you can deliver products faster with higher quality. Within this kit, Read the “Model Driven Systems Development” white paper to see how to improve product quality and communication. Then check out the rest of the e-Kit to learn more about important topics that can affect the success of any software project through customer examples, tutorials, informative Webcasts, and best practices for designing, building and managing systems. From start to finish, at every stage in your projects, Rational Systems Development Solution can help your company reach its full potential. FREE! Go There Now!

All FREE IBM® developerWorks Tools!