How to use transaction in php/mysql

Question

i am using php/mysql. i know about the transaction in mysql but can't use in my script. below is my script how can i use php transaction in my code.i.e BEGIN, ROLLBACK, COMMIT

foreach($json_a['shop'] as $jsondata=>$json)
{
if($json['category']==='product')
{
$name_product=$json['name'];
$query1="insert into product(id,name,user_id)values('','" . mysql_real_escape_string($name_product). "','1')";

$result1=mysql_query($query1) or die("error in query".mysql_errno());
//echo "success...!";
$product++;
}
else
if($json['category']==='order')
{
$name_order=$json['name'];
$query2="insert into order(id,name,user_id)values('','" . mysql_real_escape_string($name_order). "','1')";

$result2=mysql_query($query2) or die("error in query".mysql_errno());
$order++;
}
else
if($json['category']==='sale')
{
$name_sale=$json['name'];
$query3="insert into sale(id,name,user_id)values('','" . mysql_real_escape_string($name_sale). "','1')";

$result3=mysql_query($query3) or die("error in query".mysql_errno());
$sale++;
}
}

You can use them by ... using them in the right places? Can you clarify your question, please? It's hard to tell what you're asking. — Charles
– Charles, Commented Mar 9, 2011 at 6:20
i am asking about the transaction and the purpose of my question is sometimes first query is executed and data is inserted in database but the second query is not executed properly so i want to rollback the transaction so that the order of insertion is not disturbed. — hunter
– hunter, Commented Mar 9, 2011 at 6:25

Sphinx · Accepted Answer · 2017-06-14 08:35:26Z

14

Simply issue mysql_query('START TRANSACTION'); and check for errors at every one of your inserts. If one of them doesn't succeed issue a ROLLBACK immediately without doing any of the remaining queries. If everything goes fine with all of them issue a COMMIT.

It may be easier to put them in a try-catch block to avoid using too many levels of nesting with if-else.

// START TRANSACTION
try{
    // INSERT 1
    if(failed)
        throw new Exception();

    // INSERT 2
    if(failed)
        throw new Exception();

    // INSERT 3
    if(failed)
        throw new Exception();

    // COMMIT
}
catch(Exception $e){
    // ROLLBACK
}

You may also want to take a look into PHP's PDO extension. Transactions are part of its features.

edited Jun 14, 2017 at 8:35

Sphinx

9547 silver badges21 bronze badges

answered Mar 9, 2011 at 6:22

Alin P.

44.5k13 gold badges79 silver badges93 bronze badges

Sign up to request clarification or add additional context in comments.

8 Comments

philwinkle Over a year ago

+1 this is sexy, nice concise representation of how this should ideally be structured.

hunter Over a year ago

i nested the queries just because it is part of foreach loop look at my updated question.

Alin P. Over a year ago

@hunter You can use that strategy no mater what you have. Just wrap everything up with the try and when you've decided a ROLLBACK is mandated you throw an exception. Note that in your case it can't happen for two of the inserts to be executed in the same run, so transactions aren't really needed. Do you plan to add some other queries?

hunter Over a year ago

yeah, after collecting the three variables product, order and sale i want to insert these in user table after executing these three queries. so what should you recommend for me.

hunter Over a year ago

as you said that so transactions aren't really needed so in my case if query1 is inserted successfully and in second query there is some error than how could be able to rollback.

|

Christian Burger · Accepted Answer · 2011-03-09 06:31:25Z

5

One option is to use PDO. Example:

$db = new PDO($dsn,$user,$password);

$db->beginTransaction();
$db->exec("delete from mytable");

$allGood = doSomethingElse();

if ($allGood)
{
  $db->commit();
} else {
  $db->rollBack();
}

or a more elegant method:

$db = new PDO($dsn,$user,$password);
$db->beginTransaction();

try{
  //first execution      
  $db->exec("delete from mytable");

  //second execution
  $db->exec("insert into anothertable");

  //if all went well
  $db->commit();

} catch (Exception $e) {

  //something broke, hit undo
  $db->rollBack();

}

edited Mar 9, 2011 at 6:31

answered Mar 9, 2011 at 6:25

Christian Burger

6754 silver badges7 bronze badges

Comments

philwinkle · Accepted Answer · 2011-03-09 06:19:42Z

2

The same rules/syntax apply here as they do in regular MySQL statements regarding transactions.

Here's an example:

$query0 = 'START TRANSACTION;';
mysql_query($query0) or die('woops' . mysql_error());

/* all of your queries here */

$query5 = 'COMMIT;';
mysql_query($query5) or die('woops' . mysql_error());

More information on the MySQL syntax for transactions can be found here: http://dev.mysql.com/doc/refman/5.0/en/commit.html

answered Mar 9, 2011 at 6:19

philwinkle

7,0193 gold badges29 silver badges46 bronze badges

Collectives™ on Stack Overflow

How to use transaction in php/mysql

3 Answers 3

8 Comments

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

8 Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related