PHP & mySQL: Simple code to implement Transaction - Commit & Rollback

Question

MY PLATFORM:

PHP & mySQL

MY SITUATION:

I am trying to implement transactions within my code. I tried to follow examples, but it's not much help. I am running 3 queries and I wanted to write a transaction in such a way so that if any of the query(ies) fail, the whole transaction should roll back. I would really appreciate a simple, efficient and non-object oriented PHP code to achieve this goal. Thank you in advance.

MY PHP CODE:

//db_res calls a custom function that performs a mysql_query on the query
$res1 = db_res("SELECT c1, c2 FROM t1 WHERE c5 = 3");
$res2 = db_res("UPDATE t2 SET c1 = 5 WHERE c2 = 10");
$res3 = db_res("DELETE FROM t3 WHERE c1 = 20");

if( $res1 && $res2 && $res3 )
{
 //commit --- but how?
}
else
{
 //rollback --- but how?
}

Check this dev.mysql.com/doc/refman/5.0/en/commit.html. This should direct you on the right direction. — Shadrack Orina
– Shadrack Orina, Commented Jul 25, 2011 at 7:18
a simple and EXCELLENT answer/example (try catch) was given to the same question at stackoverflow.com/questions/2708237/… must read, shows EXACTLY what to do in clear and straightforward terms. — tony gil
– tony gil, Commented Feb 11, 2012 at 16:24

rjmunro · Accepted Answer · 2014-03-10 18:20:45Z

19

You don't need to use mysqli. You can just issue the transaction commands as queries.

So for your example:

mysql_query("start transaction;");

//db_res calls a custom function that performs a mysql_query on the query
$res1 = db_res("SELECT c1, c2 FROM t1 WHERE c5 = 3");
$res2 = db_res("UPDATE t2 SET c1 = 5 WHERE c2 = 10");
$res3 = db_res("DELETE FROM t3 WHERE c1 = 20");

if( $res1 && $res2 && $res3 )
{
  mysql_query("commit;");
}
else
{
  mysql_query("rollback;");
}

By they way if you are thinking about upgrading to mysqli, please don't. Upgrade to PDO instead, it's much more sane.

edited Mar 10, 2014 at 18:20

answered Oct 25, 2010 at 15:46

rjmunro

28.3k21 gold badges113 silver badges135 bronze badges

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

4 Comments

Rooster242 Over a year ago

I think you mean mysql_query("start transaction"); or mysql_query("begin");

rjmunro Over a year ago

Yeah, got mixed up between the two... Will fix.

Asuquo12 Over a year ago

mysql is not safe , try using mysqli or PDO

rjmunro Over a year ago

@Asuquo12 Yes, absolutely use PDO. I even say so at the end of my answer. But if you are using mysql on some legacy internal thing, you can do transactions as I show above without upgrading.

John Conde · Accepted Answer · 2010-01-25 15:06:56Z

9

You need to use the mysqli extension to use this functionality.

See: autocommit(), commit(), and rollback()

<?php
$link = mysqli_connect("localhost", "my_user", "my_password", "world");

/* check connection */
if (mysqli_connect_errno()) {
    printf("Connect failed: %s\n", mysqli_connect_error());
    exit();
}

/* disable autocommit */
mysqli_autocommit($link, FALSE);

mysqli_query($link, "CREATE TABLE myCity LIKE City");
mysqli_query($link, "ALTER TABLE myCity Type=InnoDB");
mysqli_query($link, "INSERT INTO myCity SELECT * FROM City LIMIT 50");

/* commit insert */
mysqli_commit($link);

/* delete all rows */
mysqli_query($link, "DELETE FROM myCity");

if ($result = mysqli_query($link, "SELECT COUNT(*) FROM myCity")) {
    $row = mysqli_fetch_row($result);
    printf("%d rows in table myCity.\n", $row[0]);
    /* Free result */
    mysqli_free_result($result);
}

/* Rollback */
mysqli_rollback($link);

if ($result = mysqli_query($link, "SELECT COUNT(*) FROM myCity")) {
    $row = mysqli_fetch_row($result);
    printf("%d rows in table myCity (after rollback).\n", $row[0]);
    /* Free result */
    mysqli_free_result($result);
}

/* Drop table myCity */
mysqli_query($link, "DROP TABLE myCity");

mysqli_close($link);
?>

answered Jan 25, 2010 at 15:06

John Conde

220k100 gold badges464 silver badges504 bronze badges

2 Comments

Devner Over a year ago

@John Thank you for your response. I have built more than 85% of my application using mysql_query. I think I will need to modify my application and rework it from scratch if I have to use mysqli. Is it possible in any way, to do it using mysql_query? I am not too sure I can switch to mysqli at this point of time, so an alternate solution is highly appreciated. Look forward to your reply.

John Conde Over a year ago

Unfortunately no. the mysql_* functions do not offer access to the advanced features offered by MySQL 4 and later like transactions and stored procedures. You don't need to go back and change every call to MySQL to use mysqli. Just change the pages that will actually need use it.

Collectives™ on Stack Overflow

PHP & mySQL: Simple code to implement Transaction - Commit & Rollback

2 Answers 2

4 Comments

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

4 Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related