MySQL transaction example

juvenile386 · #1 05-Dec-2007, 04:57

I'd like to ask for a simple example of a transaction using php + mysql,
i only find a little from the web and the tutorials that i know doesn't have any of it... the more i see some codes, i think i'll understand it better...
just the code that only emphasizes its main concept would be enough...

like for example BEGIN then some SQL then some condition, if true: commit,else rollback... then some php codes outside it...

thanks!

juvenile386 · #2 05-Dec-2007, 16:23

Quote:

Originally Posted by juvenile386

I'd like to ask for a simple example of a transaction using php + mysql,
i only find a little from the web and the tutorials that i know doesn't have any of it... the more i see some codes, i think i'll understand it better...
just the code that only emphasizes its main concept would be enough...

like for example BEGIN then some SQL then some condition, if true: commit,else rollback... then some php codes outside it...

thanks!

update:
i'm only having problems with multi-line queries using transaction in php,
how is it done??
I would really appreciate any help...

admin · #3 07-Dec-2007, 10:16

You need transactions when queries need to be correct AND done in a certain order AND all these queries are completed without errors.

The most common example for a very good reason to use transactions is when money or points are transferred between two parties/persons.

For example, your queries in a script may want transfer 50.00 from John to Jacob.

Code:

UPDATE users SET `balance`=`balance` + $transfer WHERE `user`='Jacob'
UPDATE users SET `balance`=`balance` - $transfer WHERE `user`='John'

Now imagine if your script fails half way, after only one of these queries being run! Jacob will have that extra 50.00 in his account, but John will not lose the amount he is supposed to lose.

To avoid errors like these, you wrap the series of queries between the BEGIN and COMMIT (or ROLLBACK) statements.

PHP Code:


<?php

// resource to database connection.
$conn = mysql_connect( '', '', '' );

// BEGIN transaction
mysql_unbuffered_query( 'BEGIN', $conn );

// should be 'ROLLBACK' or 'COMMIT', default is ROLLBACK.
$commit_flag = 'ROLLBACK';

if( mysql_unbuffered_query("UPDATE `mydb`.`users` SET `balance` = `balance` + $transfer WHERE `user`='Jacob'", $conn) )
{
    if( mysql_unbuffered_query("UPDATE `mydb`.`users` SET `balance`=`balance` - $transfer WHERE `user`='John'", $conn) )
    {
        // both queries ran successfully! We can commit the changes now.
        $commit_flag = 'COMMIT';
    }
}

mysql_unbuffered_query( $commit_flag, $conn );
?>

Thread Tools	Search this Thread
Show Printable Version Email this Page	Search this Thread: Advanced Search
Rate This Thread
Rate This Thread:

Similar Threads
Thread	Thread Starter	Forum	Replies	Last Post
Moving data from MySQL to MySQL	meurer	MySQL / PHP Forum	0	25-Jan-2007 09:03
i need urgent help with arrays!!	alfie27	CPP / C++ Forum	3	14-Oct-2006 18:13
Cpanel downgrade MYSQL 4.1.X to MySQL 4.0.xx	Webhosting-live	Web Hosting Forum	1	01-Sep-2006 02:54
Windows: From only £20p/y,Linux: from $10p/m. ASP, ASP.NET, PHP, Free MySQL, +More	EyotaHosts	Web Hosting Advertisements & Offers	0	28-Jun-2003 13:54

MySQL transaction example

Re: transaction

Re: MySQL (innodb) transaction example