A few ideas occur to me. You don't have enough information about
your schema for me to say for sure which of these would be best.
1 - put all the inserts into one statement:
$myQuery = "INSERT INTO purchaseItems (orderID, itemIDs,
itemQty) VALUES (";
$i = 1;
foreach ($order as $item)
{
$valueLine[$i] = "(" . $item['orderID'] . ", " . $item
['itemIDs'] . ", " . $item['itemQty'] . ")";
$i = $i +1;
}
$myQuery = join($valueLine, ",") . ")";
then execute $myQuery as one statement.
2 - Why `DELETE FROM purchaseItems WHERE orderID = '789' ` why not:
DELETE FROM purchaseItems WHERE orderID = '789' and itemIDs =
<whatever>
Good Luck,
Frank
On May 20, 2005, at 5:02 PM, [EMAIL PROTECTED] wrote:
From: "mayo" <[EMAIL PROTECTED]>
Date: May 20, 2005 4:45:05 PM PDT
To: "'Miguel Guirao'" <[EMAIL PROTECTED]>, <php-
[EMAIL PROTECTED]>
Subject: RE: [PHP-DB] multiple queries, one transaction - REWORDED
I have a scenario where I have multiple inserts into a table and
need to
know that ALL inserts were successful and if not that there were no
inserts.
I've seen an article on transactions in php/mysql and have a few
questions.
I have a table with orderID, itemIDs and itemQty
Someone with an orderID of 789 may want to purchase itemID 1 and
itemID
2 and item 3. At purchase I give the customer a final shot of changing
his mind. (While shopping he puts the items into a session variables,
now that he's in the process of purchasing its in a database.)
Say he want to remove itemID 1.
The solution I've been using is to
DELETE FROM purchaseItems
WHERE orderID = '789'
Now I have to reinsert.
<LOOP>
INSERT INTO purchaseItems
...
</LOOP>
These multiple queries (DELETE and INSERTS) should be considered one
transaction so that if one query fails, they all do.
Thx, mayo
