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, [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
Re: [PHP-DB] multiple queries, one transaction - REWORDED
Crate an Array, where the Key is the ItemId, and the value is the ItemQty. If the client want to delete an the item, unset the key, if the client wont to add or remove an item quantity, just change the value. Then when the items and quantities are correct, just start transaction begin foreach If (! (INSERT . (order, ItemID (the array key), ItemQty (the array value of the key))...)) rollback transaction break end foreach commit transaction So if the insert is failed, you rollbackit. No need for a DELETE -- Atte, Andrés G. Montañez Técnico en Redes y Telecomunicaciones Montevideo - Uruguay -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
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 -Original Message- From: Miguel Guirao [mailto:[EMAIL PROTECTED] Sent: Tuesday, May 17, 2005 7:41 PM To: mayo; php-db@lists.php.net Subject: RE: [PHP-DB] multiple queries, one transaction There is a function that gets the last auto increment value for an ID field!! -Original Message- From: mayo [mailto:[EMAIL PROTECTED] Sent: Martes, 17 de Mayo de 2005 10:27 a.m. To: php-db@lists.php.net Subject: [PHP-DB] multiple queries, one transaction I would like to get the itemID number (autoincrement) of the last insert. (Insert order, get last orderID number and use it elsewhere.) I'm having trouble understanding how to do a transaction in mysql/php Code below: $conn = mysql_connect($dbhost, $dbuser, $dbpass) or die ('Error connecting to mysql'); $dbname = 'mail'; mysql_query(BEGIN); // starts the transaction mysql_query($query) or die('Error, insert query failed'); mysql_query($query2) or die('Error, select query failed'); $query = INSERT INTO orders (orderDate) VALUES ('2005-05-17'); $query = SELECT max ordered FROM orders; mysql_query($query) or die('Error, insert query failed'); mysql_query($query2) or die('Error, update query failed'); mysql_query(COMMIT); // ends the transaction mysql_close($conn); ? thx -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php