How to perform a mysql rollback in php?

[ray_broome]

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:

mysql_query("START TRANSACTION");
if(mysql_query($myquery))
  mysql_query("COMMIT");
else
  mysql_query("ROLLBACK");

Any help/ideas/suggestions?

Thanks in advance...

[kkeith29]

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

something like this maybe.

$sql = "SQL HERE";
$query = mysql_query( $sql,$con );
if ( $query ) {
  $iid = mysql_insert_id( $con );
  $sql = "SQL HERE WITH {$iid}";
  $query = mysql_query( $sql );
}

[R0bb0b]

This is how I usually do it:

<?php
$querylog = "TRANSACTION STARTED";
$commit = "commit";
mysql_query("begin", $dbconn);

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

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

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

if($commit == "rollback")
{
	$querylog .= "ERROR IN TRANSACTIONtransaction rolled back";
	//echo $querylog;
}

mysql_query($commit);
?>

[ray_broome]

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

[onlysumon]

Make sure that your Storage Engine is set to InnoDB.