DaniWeb Forum Index > Web Development > PHP

How to perform a mysql rollback in php?

Please support our PHP advertiser: PostgreSQL or MySQL? Compare and contrast the two most popular open source databases

•

Join Date: Aug 2004

Posts: 24

Reputation: ray_broome is an unknown quantity at this point

Solved Threads: 0

ray_broome ray_broome is offline

Offline

Newbie Poster

How to perform a mysql rollback in php?

Oct 21st, 2008

Hi,

I have a situation where i am inserting to two tables from a web form but the first table needs to be inserted to so i can get the PK, then use the PK to insert into the second table. So what I would like to do is rollback the insert operation if there is some error inserting to the first table or vice versa.

This is what i'm thinkin of doin so far:

 Help with Code Tags 
 php Syntax (Toggle Plain Text) 
mysql_query("START TRANSACTION");
if(mysql_query($myquery))
  mysql_query("COMMIT");
else
  mysql_query("ROLLBACK");
mysql_query("START TRANSACTION");
if(mysql_query($myquery))
  mysql_query("COMMIT");
else
  mysql_query("ROLLBACK");

Any help/ideas/suggestions?

Thanks in advance...

•

Join Date: Jun 2007

Posts: 1,227

Reputation: kkeith29 has a spectacular aura about

Solved Threads: 167

kkeith29

Offline

Nearly a Posting Virtuoso

Re: How to perform a mysql rollback in php?

Oct 21st, 2008

so pretty much you want to take the insertion uid and use it in the second table.

something like this maybe.

 Help with Code Tags 
 PHP Syntax (Toggle Plain Text) 
$sql = "SQL HERE";
$query = mysql_query( $sql,$con );
if ( $query ) {
  $iid = mysql_insert_id( $con );
  $sql = "SQL HERE WITH {$iid}";
  $query = mysql_query( $sql );
}
$sql = "SQL HERE";
$query = mysql_query( $sql,$con );
if ( $query ) {
  $iid = mysql_insert_id( $con );
  $sql = "SQL HERE WITH {$iid}";
  $query = mysql_query( $sql );
}

•

Join Date: Jun 2008

Posts: 849

Reputation: R0bb0b is on a distinguished road

Solved Threads: 67

R0bb0b

Offline

Practically a Posting Shark

Re: How to perform a mysql rollback in php?

Oct 22nd, 2008

This is how I usually do it:

 Help with Code Tags 
 php Syntax (Toggle Plain Text) 
<?php
$querylog = "TRANSACTION STARTED<br /><br />";
$commit = "commit";
mysql_query("begin", $dbconn);
 
$query = "insert into ...";
if(!mysql_query($query, $dbconn))
{
	$commit = "rollback";
	$querylog .= "error in query: " . $query . " : " . mysql_error($dbconn) . "<br /><br />";
}
 
$query = "insert into ...";
if(!mysql_query($query, $dbconn))
{
	$commit = "rollback";
	$querylog .= "error in query: " . $query . " : " . mysql_error($dbconn) . "<br /><br />";
}
 
$query = "insert into ...";
if(!mysql_query($query, $dbconn))
{
	$commit = "rollback";
	$querylog .= "error in query: " . $query . " : " . mysql_error($dbconn) . "<br /><br />";
}
 
if($commit == "rollback")
{
	$querylog .= "ERROR IN TRANSACTION<br /><br />transaction rolled back<br /><br />";
	//echo $querylog;
}
 
mysql_query($commit);
?>
<?php
$querylog = "TRANSACTION STARTED<br /><br />";
$commit = "commit";
mysql_query("begin", $dbconn);

$query = "insert into ...";
if(!mysql_query($query, $dbconn))
{
	$commit = "rollback";
	$querylog .= "error in query: " . $query . " : " . mysql_error($dbconn) . "<br /><br />";
}

$query = "insert into ...";
if(!mysql_query($query, $dbconn))
{
	$commit = "rollback";
	$querylog .= "error in query: " . $query . " : " . mysql_error($dbconn) . "<br /><br />";
}

$query = "insert into ...";
if(!mysql_query($query, $dbconn))
{
	$commit = "rollback";
	$querylog .= "error in query: " . $query . " : " . mysql_error($dbconn) . "<br /><br />";
}

if($commit == "rollback")
{
	$querylog .= "ERROR IN TRANSACTION<br /><br />transaction rolled back<br /><br />";
	//echo $querylog;
}

mysql_query($commit);
?>

Last edited by R0bb0b; Oct 22nd, 2008 at 3:46 am.

“Be who you are and say what you feel because those who mind don't matter and those who matter don't mind.” - Dr. Seuss

-- The documentation is inevitable, you may get away with it for a little while but eventually you too will have to do the deed.

•

Join Date: Aug 2004

Posts: 24

Reputation: ray_broome is an unknown quantity at this point

Solved Threads: 0

ray_broome ray_broome is offline

Offline

Newbie Poster

Re: How to perform a mysql rollback in php?

Oct 22nd, 2008

cool, thanks for the help guys, r0bb0b's solution seems closer to what i'm trying to do though because it is supposed to be an "all or nothing" situation