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
RE: [PHP-DB] multiple queries, one transaction
Thanks, the $orderId = mysql_insert_id($result) was what I was looking
for.
But in the long run, I'm still concerned about how to group several
queries into one transaction. As for example when one wants to make
multiple inserts with one submit.
Ex: User wants to input inventory. He's going to put in 11 new jeans
sizes 28-38
You create a loop inserting the following
itemType:jean
itemDesigner:Antik
itemSize:$i
This should all be one transaction. I would like to do this with
php/mysql.
Thx
-Original Message-
From: Jason [mailto:[EMAIL PROTECTED]
Sent: Saturday, May 17, 2003 1:30 PM
To: 'mayo'
Subject: RE: [PHP-DB] multiple queries, one transaction
You could always use $orderId = mysql_insert_id($result) to pull the
autoinc
id that was created from your insert statement...
But I think your question revolves more around how do I do a query
then
how do I pull the id back
So to answer what I think your asking...
I write my queries like this:
$query = INSERT into orders(
orderData)
values(
'$orderData');
$result = mysql_query($query);
$orderId= mysql_insert_id($result);
Hope that helps.
-Original Message-
From: mayo [mailto:[EMAIL PROTECTED]
Sent: Tuesday, May 17, 2005 8:27 AM
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
RE: [PHP-DB] multiple queries, one transaction
Well, for something like this you would need one row for each item.
I just did a db for a clothing store (with cold fusion)
itemID: 1001 // autoincrement
itemTypeID: 1 // number -- refers to jeans
itemDesignerID: 5 // number -- refers to designer Antik
itemSize: 32 // number -- refers to waist size
itemColor: 7 // number -- referes to color blue
each item must have its own row
itemID:1002 and 1003 could be the same designer, color and size but it
refers to a different item
I see there is a way to do consider all querys and to fail the entire
procedure if one query fails. It's a BEGIN and COMMIT statement.
mysql_query(BEGIN); // starts the transaction
mysql_query(COMMIT); // ends the transaction
I'm just not certain how it's used.
Thx
-Original Message-
From: Jason [mailto:[EMAIL PROTECTED]
Sent: Saturday, May 17, 2003 2:43 PM
To: 'mayo'
Subject: RE: [PHP-DB] multiple queries, one transaction
I see what your saying.. you can join together select queries, but I
don't
know how to join insert queries...
From what I know, which isn't that much, If you feel like you need to
multiple queries to do a single task, you should relook how your setting
up
your code/db.
Your example, for example :), you shouldn't need to do a loop to
accomplish
that task, rather you'd just have a qty table in your database that you
add
+n to when you add inventory.
There are times when a loop is necessary, and that is ok, but for the
most
part there are usually a few ways around doing multiple queries that
should
at least be looked at :)
Hope that helps... if it doesn't, e-mail me off of the list with your
actual
situation, I may be able to help
J
-Original Message-
From: mayo [mailto:[EMAIL PROTECTED]
Sent: Tuesday, May 17, 2005 10:39 AM
To: 'Jason'; php-db@lists.php.net
Subject: RE: [PHP-DB] multiple queries, one transaction
Thanks, the $orderId = mysql_insert_id($result) was what I was looking
for.
But in the long run, I'm still concerned about how to group several
queries into one transaction. As for example when one wants to make
multiple inserts with one submit.
Ex: User wants to input inventory. He's going to put in 11 new jeans
sizes 28-38
You create a loop inserting the following
itemType:jean
itemDesigner:Antik
itemSize:$i
This should all be one transaction. I would like to do this with
php/mysql.
Thx
-Original Message-
From: Jason [mailto:[EMAIL PROTECTED]
Sent: Saturday, May 17, 2003 1:30 PM
To: 'mayo'
Subject: RE: [PHP-DB] multiple queries, one transaction
You could always use $orderId = mysql_insert_id($result) to pull the
autoinc
id that was created from your insert statement...
But I think your question revolves more around how do I do a query
then
how do I pull the id back
So to answer what I think your asking...
I write my queries like this:
$query = INSERT into orders(
orderData)
values(
'$orderData');
$result = mysql_query($query);
$orderId= mysql_insert_id($result);
Hope that helps.
-Original Message-
From: mayo [mailto:[EMAIL PROTECTED]
Sent: Tuesday, May 17, 2005 8:27 AM
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
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
