Ad:

PHP Discussion Thread
Marked Solved
Views: 18128

Oct 21st, 2008

How to perform a mysql rollback in php?

Expand Post »

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:

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

ray_broome

Reputation Points: 10

Solved Threads: 0

Light Poster

Offline

25 posts
since Aug 2004

Permalink

Oct 21st, 2008

Re: How to perform a mysql rollback in php?

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

something like this maybe.

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 );
}

kkeith29

Reputation Points: 235

Solved Threads: 193

Nearly a Posting Virtuoso

Offline

1,315 posts
since Jun 2007

Permalink

Oct 22nd, 2008

Re: How to perform a mysql rollback in php?

This is how I usually do it:

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.

R0bb0b

Reputation Points: 358

Solved Threads: 89

Posting Shark

Offline

986 posts
since Jun 2008

Permalink

Oct 22nd, 2008

Re: How to perform a mysql rollback in php?

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

ray_broome

Reputation Points: 10

Solved Threads: 0

Light Poster