Home arrow MySQL arrow Using Transactions with MySQL 4.0 and PHP
MYSQL

Using Transactions with MySQL 4.0 and PHP


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.

Author Info:
By: Joel Philip
Rating: 5 stars5 stars5 stars5 stars5 stars / 90
May 08, 2003

print this article
SEARCH DEVARTICLES

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.

All MySQL Tutorials
More By Joel Philip


blog comments powered by Disqus
MYSQL ARTICLES

- MySQL and BLOBs
- Two Lessons in ASP and MySQL
- Lord Of The Strings Part 2
- Lord Of The Strings Part 1
- Importing Data into MySQL with Navicat
- Building a Sustainable Web Site
- Creating An Online Photo Album with PHP and ...
- Creating An Online Photo Album with PHP and ...
- PhpED 3.2 More Features Than You Can Poke ...
- Creating An Online Photo Album with PHP and ...
- Creating An Online Photo Album with PHP and ...
- Security and Sessions in PHP
- Setup Your Personal Reminder System Using PHP
- Create a IP-Country Database Using PERL and ...
- Developing a Dynamic Document Search in PHP ...

Watch our Tech Videos 
Dev Articles Forums 
 RSS  Articles
 RSS  Forums
 RSS  All Feeds
Write For Us 
Weekly Newsletter
 
Developer Updates  
Free Website Content 
Contact Us 
Site Map 
Privacy Policy 
Support 

Developer Shed Affiliates

 




© 2003-2014 by Developer Shed. All rights reserved. DS Cluster - Follow our Sitemap
Popular Web Development Topics
All Web Development Tutorials