MySQL
  Home arrow MySQL arrow Using Transactions with MySQL 4.0 and PHP
Dev Articles Forums 
ADO.NET  
Apache  
ASP  
ASP.NET  
C#  
C++  
ColdFusion  
COM/COM+  
Delphi-Kylix  
Design Usability  
Development Cycles  
DHTML  
Embedded Tools  
Flash  
Graphic Design  
HTML  
IIS  
Interviews  
Java  
JavaScript  
MySQL  
Oracle  
Photoshop  
PHP  
Reviews  
Ruby-on-Rails  
SQL  
SQL Server  
Style Sheets  
VB.Net  
Visual Basic  
Web Authoring  
Web Services  
Web Standards  
XML  
Dedicated Servers  
Moblin 
JMSL Numerical Library 
IBM® developerWorks 
Sun Developer Network 
Weekly Newsletter
 
Developer Updates  
Free Website Content 
 RSS  Articles
 RSS  Forums
 RSS  All Feeds
Write For Us Get Paid 
Request Media Kit
Contact Us 
Site Map 
Privacy Policy 
Support 
 USERNAME
 
 PASSWORD
 
 
  >>> SIGN UP!  
  Lost Password? 
MYSQL

Using Transactions with MySQL 4.0 and PHP
By: Joel Philip
  • Search For More Articles!
  • Disclaimer
  • Author Terms
  • Rating: 5 stars5 stars5 stars5 stars5 stars / 41
    2003-05-08

    Table of Contents:

    Rate this Article: Poor Best 
      ADD THIS ARTICLE TO:
      Del.ici.ous Digg
      Blink Simpy
      Google Spurl
      Y! MyWeb Furl
    Email Me Similar Content When Posted
    Add Developer Shed Article Feed To Your Site
    Email Article To Friend
    Print Version Of Article
    PDF Version Of Article
     
     
    ADVERTISEMENT


    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

     

    IBM® developerWorks 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!

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







    © 2003-2008 by Developer Shed. All rights reserved. DS Cluster 5 hosted by Hostway