Re: [PHP-DB] multiple queries in the same request
Gabriel B. wrote: SELECT (@category_id:=id) FROM categories WHERE description = cat1; REPLACE INTO data VALUES( 10, @category_id); i send this as a single query in PHP and it returns an error quoting everything after the first ; anyidea if i can't send several queries at once? any workaround? You can't send multiple queries in an SQL statement. You can just split that in two separates queries. It's not really slower by any practical means because the DB system would have to execute them both anyway. -- Bruno Ferreira --- [This E-mail scanned for viruses by Declude Virus] -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] multiple queries in the same request
Gabriel B. wrote: You can't send multiple queries in an SQL statement. You can just split that in two separates queries. It's not really slower by any I'm not really concerned about performance. i'm *really* concerned about race conditions. I have more than 3mi hits per day. the chance that two pairs of queries will run consecutively is imense. Then you need to use transactions, which fill your purpose :) I presume you're using MySQL or Postgres, google for START TRANSACTION and COMMIT. -- Bruno Ferreira
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
Re: [PHP-DB] multiple queries
Or do the same with the BETWEEN keyword: SELECT * FROM dates WHERE store='$store' AND date BETWEEN DATE_SUB(now(), interval 3 day) AND DATE_ADD(now(), interval 3 day) Richard Davey wrote: Hello Cameron, Tuesday, December 2, 2003, 1:08:19 AM, you wrote: CS $sql = SELECT * from dates where store=$store and date=$date; CS ultimately I would like to display data for 3 days on either side of CS this. The following is un-tested, but a quick look over the MySQL manual should firm this up for you: SELECT * FROM dates WHERE store='$store' AND date DATE_SUB(now(), interval 3 day) AND date DATE_ADD(now(), interval 3 day) -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] multiple queries
Hello Cameron, Tuesday, December 2, 2003, 1:08:19 AM, you wrote: CS $sql = SELECT * from dates where store=$store and date=$date; CS ultimately I would like to display data for 3 days on either side of CS this. The following is un-tested, but a quick look over the MySQL manual should firm this up for you: SELECT * FROM dates WHERE store='$store' AND date DATE_SUB(now(), interval 3 day) AND date DATE_ADD(now(), interval 3 day) -- Best regards, Richardmailto:[EMAIL PROTECTED] -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] multiple queries with PHP
John Ryan wrote: I want to SELECT * from a table and also update the hit counter by 1. So theres 2 different queries which I know I can easily do in PHP seperately, but I thought itd be quicker and more efficient to run them in the same query, ie seperated by a ';'. But PHP returns an error, and it works fine on the command line. I read somewhere thats its a security risk in PHP to allow 2 queries, so its turned off by default. Is this true? Yes. Also, just because you can fit something onto one line it doesn't mean it's more efficient. -- ---John Holmes... Amazon Wishlist: www.amazon.com/o/registry/3BEXC84AB3A5E/ php|architect: The Magazine for PHP Professionals www.phparch.com -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] multiple queries with PHP
You wouldnt know where to change this setting? John W. Holmes [EMAIL PROTECTED] wrote in message news:[EMAIL PROTECTED] John Ryan wrote: I want to SELECT * from a table and also update the hit counter by 1. So theres 2 different queries which I know I can easily do in PHP seperately, but I thought itd be quicker and more efficient to run them in the same query, ie seperated by a ';'. But PHP returns an error, and it works fine on the command line. I read somewhere thats its a security risk in PHP to allow 2 queries, so its turned off by default. Is this true? Yes. Also, just because you can fit something onto one line it doesn't mean it's more efficient. -- ---John Holmes... Amazon Wishlist: www.amazon.com/o/registry/3BEXC84AB3A5E/ php|architect: The Magazine for PHP Professionals www.phparch.com -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] multiple queries with PHP
John Ryan wrote: You wouldnt know where to change this setting? It's not a setting, you just can't do it. Use two mysql_query() calls. -- ---John Holmes... Amazon Wishlist: www.amazon.com/o/registry/3BEXC84AB3A5E/ php|architect: The Magazine for PHP Professionals www.phparch.com -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] multiple queries in PHP to mysql database.
Hi Jerry Inline comments On 28 Mar,2003 at 12:31 JeRRy JeRRy wrote: How can I update 2 tables at once if a match occours from information inputed into a PHP form. How about turning this around and re-organising the data a little? Why not have a single score table with references to a central username / pw / id table. Table 1 - users id username password Table 2 - scores id userid game1 game2 game3 game4 ... overall You can then do UPDATE table2 SET game1=game1+1, overall=overall+1 WHERE userid = $userid Getting a results table would then be a single query... Does this help at all ? Hopefully I've grasped enough of the concept to not make a complete fool of myself ! Jerry -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] multiple queries in a rov
Load the query into a variable, usually $query. $query = SELECT * FROM table; SELECT * FROM othertable;; $result = mysql_query($query, $link_id) or die(error msg); Mattia [EMAIL PROTECTED] 06/27/02 02:19PM I would like to make multiple queries in a single mysql_query(...) statement!! Like in the mysql client given with the distribution, separated by a ; or a \g !!! How can I??? Mattia -- 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 in a row
On Thursday 27 June 2002 02:19 pm, Mattia wrote: I would like to make multiple queries in a single mysql_query(...) statement!! Like in the mysql client given with the distribution, separated by a ; or a \g !!! How can I??? You should be able to use it exactly the same. $foo = mysql_query(' select something fromsomewhere; select something_else fromsomewhere_else; ', $link_id); -- Casey Allen Shobe / Network Security Analyst PHP Developer SecureWorks, Inc. / 404.327.6339 x169 / Fax: 404.728.0144 [EMAIL PROTECTED] / http://www.secureworks.net Content is my own and does not necessarily represent my company. -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] multiple queries in a row
On Wednesday 03 July 2002 04:36, Casey Allen Shobe wrote: On Thursday 27 June 2002 02:19 pm, Mattia wrote: I would like to make multiple queries in a single mysql_query(...) statement!! Like in the mysql client given with the distribution, separated by a ; or a \g !!! How can I??? You should be able to use it exactly the same. $foo = mysql_query(' selectsomething from somewhere; selectsomething_else from somewhere_else; ', $link_id); IIRC php only allows you to pass a single query at a time to mysql. -- Jason Wong - Gremlins Associates - www.gremlins.com.hk Open Source Software Systems Integrators * Web Design Hosting * Internet Intranet Applications Development * /* Every program is a part of some other program, and rarely fits. */ -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] multiple queries in one php statement....
Could someone give me a tip ang tell me if i can insert more than one queries in a mysql_query() statement?? etc $quer=mysql_query(" BEGIN WORK , QUERY 1 , QUERY 2, COMMIT",$db) No, you can't. You've got to execute them one at a time. -- -Ryan :: ICQ - 595003 :: GigaBoard - http://www.gigaboard.net/ -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED] To contact the list administrators, e-mail: [EMAIL PROTECTED]
Re: [PHP-DB] multiple queries in one php statement....
cant... since the function returns a code if the call is true or not. if you try multiple statements, you cant know which failed and which was good... Koutsogiannopoulos Karolos wrote: Hello everyone... Could someone give me a tip ang tell me if i can insert more than one queries in a mysql_query() statement?? etc $quer=mysql_query( BEGIN WORK , QUERY 1 , QUERY 2, COMMIT,$db) Thanks. ___ PGP KEY ID: 0xA86600E9 ___ -- Leo G. Divinagracia III [EMAIL PROTECTED] -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED] To contact the list administrators, e-mail: [EMAIL PROTECTED]