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".

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.