RE: [PHP-DB] multiple queries, one transaction - REWORDED

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, php-db-digest-help@lists.php.net wrote:

From: "mayo" <mayo@nycinteractive.com> Date: May 20, 2005 4:45:05 PM PDT To: "'Miguel Guirao'" <miguel.guirao@mail.telcel.com>, <php-db@lists.php.net> 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