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!

Be the first to hear about i5/OS V6R1! Hold your calendar on January 30, 2008 for this free webcast on the new i5/OS. Rational's Enterprise Modernization products will be discussed at this webcast as they help to drive the application development environment for this new System i OS. <br />And learn how i5/OS will take you to the next step of efficient, resilient business processing. You will hear about the new i5/OS capabilities as it will be the most significant i5/OS release in years. If you cannot join the webcast on 1/30/08 you can still use this link to listen to the replay.<br /> FREE! Go There Now!

NEW! Best Practices: The Integrated Project and Portfolio Management Platform. Hear how IBM Rational Project and Portfolio Management integrated solutions help teams put the right tools and processes in place to maximize the effectiveness and efficiency of project teams and ensure that the business vision is being executed correctly. Learn how to automate and integrate requirements prioritization, top-down project planning, communications and controls, and methodology deployment to keep your scope, costs, and schedules under control. Tackle with an end-to-end approach the management of scope and scope changes, usage of methodology to control and empower project teams, and optimization of resources to align activity costs with the overall project plan. FREE! Go There Now!

NEW! Calling all CC Power Users – and those that would like to be! Join this Rational Talks to You teleconference, featuring Paul Boustany and Mark Krasovich, to speak to the experts about becoming a Rational ClearCase power user. Get a chance to ask your questions and learn tips and tricks for using Rational ClearCase in Agile development FREE! Go There Now!

NEW! Did you say mainframe? e-kit Learn how you can extend modern application lifecycle management to IBM System z through the IBM Rational Software Delivery Platform (SDP). The Did you say mainframe? e-kit includes podcasts, webcasts, tutorials, white and red papers, demos, and articles designed to help ease the challenges of modernizing your enterprise. This complimentary kit for mainframe developers is a practical, how-to guide for making the most of an existing development environment, including the skills and infrastructure already in place at an established enterprise. 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! Krugle, developerWorks, and code search Ken Krugler, co-founder of code search company Krugle, and Laura Merling, vice president of Marketing and Business Development for Krugle, join to talk about the ins and outs of code search and what it means as a new feature for developerWorks users. FREE! Go There Now!

NEW! Section 508 of the U.S. Rehabilitation Act: Web accessibility compliance Because access to government information continues to be an area of concern for many U.S. citizens with disabilities, the U.S. government enacted Section 508 of the Rehabilitation Act in 2001 to ensure that government agencies create accessible Web content, enabling all citizens to access the information they need. A fully accessible Web site makes Web content accessible to all individuals, including those with disabilities, who may be accessing Web content via a variety of user agents. Common user agents include standard Web browsers, text-only browsers, assistive devices and mobile devices such as cell phones or personal digital assistants (PDAs). FREE! Go There Now!

NEW! Trial download: IBM Rational Performance Tester V7.0.1 Get a free trial download of the latest version of IBM Rational Performance Tester V7.0.1, a load and performance testing solution for teams concerned about the scalability of their Web-based applications. Combining multiple ease-of-use features with granular detail, Rational Performance Tester simplifies the test-creation, load-generation and data-collection processes that help teams ensure the ability of their applications to accommodate required user loads. 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!

NEW! Webcast: IBM Rational Build Forge - Beyond the Build The discipline of assembling and delivering software is maturing beyond standard developer-centric compile/test software builds. The end-to-end software development lifecycle is emerging as the new focus moves “Beyond the Build.” Join this on demand webcast to learn about methods for streamlining software delivery and key capabilities of the IBM Rational Build Forge framework for automating build and release management in environments of any size. FREE! Go There Now!

All FREE IBM® developerWorks Tools!