Can anyone help? I'm trying to implement a transaction (using InnoDB
tables ) in which
everything done in it can be rolled back at any stage. There are several
operations
involved and I need to be able to undo all the work done so far at any
point.
In my code below, I've deliberately set the variable I use to check whether
to go on
with the transaction so that everything should get rolled back. However,
only the last
operation ( an INSERT ) gets rolled back. Please, is that MySQL's default
behaviour
or am I doing something wrong?
<?
include("transactions.inc");
function PlaceOrder($MerchantName,$WalletID,$CartContents)
{
if(is_array($CartContents) && sizeof($CartContents)>0){
ConnectDBServer();
$DBName=MerchantDB($MerchantName);
$i=1;
$SQLQuery=" SELECT id,name,description,price
FROM products WHERE id IN(";
foreach($CartContents as $CartItem){
if(is_array($CartItem)){
if($i!=1) $SQLQuery.=",";
$SQLQuery.=$CartItem["pid"];
$i++;
}
}
$SQLQuery.=")";
//print $SQLQuery."<br>";
//exit;
if($i>0){
$Products=array();
$result=mysql_db_query("$DBName",$SQLQuery);
while($row=mysql_fetch_array($result)){
$Products[$row["id"]]=array("name"=>$row["name"],
"description"=>$row["description"],
"price"=>$row["price"]);
}
mysql_free_result($result);
}
else return 'Items in cart are not recognizable!';
// Calculate cost of items in shopping cart
$TotalCost=0;
foreach($CartContents as $CartItem){
$price=$Products[$CartItem["pid"]]["price"];
$qty=(int) $CartItem["qty"];
$TotalCost+=$price*$qty;
}
/*
// Get current value in Wallet
$SQLQuery="SELECT value FROM wallets WHERE id=$WalletID"; // LOCK IN
SHARE
MODE
//print $SQLQuery."<br>";
//exit;
$result=mysql_db_query("paymentsystem",$SQLQuery);
$CurrentValue=mysql_result($result,0);
mysql_free_result($result);
if($TotalCost<=$CurrentValue){
*/
$SQLQuery="SELECT id FROM merchants WHERE
name='$MerchantName'";
$result=mysql_db_query("paymentsystem",$SQLQuery);
$MerchantID=mysql_result($result,0,"id");
mysql_free_result($result);
mysql_select_db("$DBName");
// --------------------- TRANSACTION STARTS
HERE -----------------------------------
mysql_query("set autocommit=0");
// function I embedded the payment in before
//$PaymentID=PaymentTransaction($WalletID,$MerchantID,$TotalCost);
// code I used to replace the function call
$Amount=$TotalCost;
@mysql_select_db("paymentsystem");
if(is_Wallet($WalletID)){
if(is_Merchant($MerchantID)){
if(is_numeric($Amount)){
//mysql_query("set autocommit=0");
// from,to and amount are valid
$SQLQuery=" UPDATE wallets
SET value=value-$Amount
WHERE id=$WalletID AND value>=$Amount";
$DebitSucceeded=@mysql_query($SQLQuery);
if($DebitSucceeded){
$SQLQuery=" INSERT INTO
payments(value,paymentdate,merchantid,walletid)
VALUES($Amount,NOW(),$MerchantID,$WalletID)";
$RecordSucceeded=@mysql_query($SQLQuery);
$PaymentID=@mysql_insert_id();
if($RecordSucceeded) mysql_query("COMMIT");
else mysql_query("ROLLBACK");
}
else mysql_query("ROLLBACK");
}
}
}
// --- code to replace function call ends
$PaymentID){
$SQLQuery="INSERT INTO
orders(walletid,paymentid,dateplaced) VALUES($WalletID,$PaymentID,CURDATE())";
$ParentDone=@mysql_db_query("$DBName",$SQLQuery);
// ------------------------ deliberate assignment to test how ROLLBACK works
$ParentDone=false;
if($ParentDone){
$OrderID=@mysql_insert_id();
$PurchaseDetails="<table width='100%'
cellspacing=0 cellpadding=2 style='font-family:verdana;font-size:11px'>
<tr><td colspan=6></td></tr><tr><td
colspan=6> </td></tr>
<tr><td colspan=5><img
src=/images/shop/horzbot.gif height=1 width='100%'></td></tr>
<tr
align=center><td>Product</td><td>Price
(<strike>N</strike>)</td><td>Qty.</td><td>Details</td><td>Total (<strike>N</strik
e>)</td></tr>
<tr><td colspan=5><img
src=/images/shop/horzbot.gif height=1
width='100%'></td></tr>";
$Total=0;
$count=0;
$i=0;
foreach($CartContents as $CartItem){
$i++;
$ProductID=$CartItem["pid"];
$Product=$Products[$ProductID]["name"];
$Description=$Products[$ProductID]["description"];
$price=$Products[$ProductID]["price"];
$qty=$CartItem["qty"];
$details=$CartItem["details"];
$SubTotal=$qty*$price;
$Total += $SubTotal;
$details=str_replace("\n","<br>",$details);
$SQLQuery=" INSERT INTO
orderdetails(orderid,productid,price,quantity,note)
VALUES($OrderID,$ProductID,$price,$qty,'$details')";
$ChildDone=@mysql_db_query("$DBName",$SQLQuery);
if($ChildDone) $count++;
//else{
//
@mysql_db_query("$DBName","ROLLBACK");
// break;
//}
if($Description)
$DescriptionLine="<div><font
class=description>$Description</font></div>";
else $DescriptionLine='';
$PurchaseDetails.="<tr><td
valign=top><font
class=product>".$Product."</font>".$DescriptionLine."</td><td align=right
valign=top>".number_format($price,2)."</td><td align=center valign=top>
".$CartItem["qty"]."</td><td
valign=top>$details</td><td align=right
valign=top>".number_format($SubTotal,2)."</td></tr>";
$PurchaseDetails.="<tr><td
colspan=5><img src=/images/shop/horzbot.gif
height=1 width='100%'></td></tr>";
}
$PurchaseDetails.="<tr><td colspan=4
align=right>Total
<strike>N</strike></td><td align=right
class=total><b>".number_format($Total,2)."</b></td></tr>
<tr><td colspan=5><img
src=/images/shop/horzbot.gif height=1
width='100%'></td></tr></table>";
//$count=0;
//if($count==$i){
@mysql_db_query("$DBName","COMMIT");
$HTML="<table width='100%'
cellpadding=2 cellspacing=2 align=center
style='font-family:verdana;font-size:12px'>
<tr><td><h3>Thank You for your
order.</h3></td></tr><!--<tr><td><b>Purchase</b></td></tr>-->
<tr><td>Print this page out as
your
receipt.</td></tr><tr><td>$PurchaseDetails</td></tr>
<tr><td><font size=1
face=verdana>Check your <a
href=/wallet/wallet.php target=_blank>Wallet</a>
for more
info.</font></td></tr></table>";
//}
//else{
// @mysql_db_query("$DBName","ROLLBACK");
// $HTML='Your order could not be placed.
count = '.$count.' : i =
'.$i;
//}
}
else{
@mysql_db_query("$DBName","ROLLBACK");
$HTML='Your order could not be placed.';
}
}
else{
@mysql_db_query("$DBName","ROLLBACK");
$HTML='Your Wallet does not have enough funds for this
order.';
}
@mysql_close();
}
else $HTML='Your cart is empty!';
return $HTML;
}
?>
---------------------------------------------------------------------
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)
To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
