RE: [PHP] Multiple inserts as a single string?
> > Thanks Richard that makes it clear. > > Thomas > > Hello Thomas, > > Thursday, June 2, 2005, 10:20:11 AM, you wrote: > > T> I have a bit of strange question: when wanting to insert multiple > T> records into the db, instead of looping through the set > and executing > T> mysql_query (which will then call the db n times), is it > not better > T> to concat a string with all the insert statements and let mysql > T> handle the inserting, that way we don't call the db n > times from php. > T> Does that make any difference? Inserting multiple rows with one INSERT statement is part of the SQL standard (IIRC), and MySQL supports it. INSERT INTO table(a,b) VALUES (1,1), (2,2), (3,3), ... ,(n, n) Jared -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
RE: [PHP] Multiple inserts as a single string?
Thanks Richard that makes it clear. Thomas -Original Message- From: Richard Davey [mailto:[EMAIL PROTECTED] Sent: 02 June 2005 12:43 PM To: php-general@lists.php.net Subject: Re: [PHP] Multiple inserts as a single string? Hello Thomas, Thursday, June 2, 2005, 10:20:11 AM, you wrote: T> I have a bit of strange question: when wanting to insert multiple T> records into the db, instead of looping through the set and T> executing mysql_query (which will then call the db n times), is it T> not better to concat a string with all the insert statements and T> let mysql handle the inserting, that way we don't call the db n T> times from php. Does that make any difference? Sure.. mysql_query doesn't support more than one query in the sql statement. If you want to do that then upgrade to PHP 5 (if you're not using it already) and use mysqli_multi_query instead. I guess packages like Pear DB may offer similar functionality, but I don't know for certain. Best regards, Richard Davey -- http://www.launchcode.co.uk - PHP Development Services "I do not fear computers. I fear the lack of them." - Isaac Asimov -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
RE: [PHP] Multiple inserts as a single string?
Hi Chris, Thanks, I thought so. You are quite right with the errors, I ran into some where it looked like that php does not allow you to execute such a concated string ... the error started at the second insert statement with no apparent reason. Is that a ph restriction? Thomas -Original Message- From: Shaw, Chris - Accenture [mailto:[EMAIL PROTECTED] Sent: 02 June 2005 12:20 PM To: Thomas; php-general@lists.php.net Subject: RE: [PHP] Multiple inserts as a single string? Thomas, If you're inserting alot of rows, (eg millions) then concating them and calling the db once probably would give a small speed advantage, because of the database handshaking. But I would look at the load the database is under, if there is alot of users hitting the database with small inserts/queries then the database is going to suffer as opposed to the same number of users hitting the database once with a script. The only problem I can think of, from php (client) point of view, if the script (concated inserts) errors, then you will need to handle where it went wrong, if it was loop, you know exactly what went wrong by outputing the current insert statement. C. -Original Message- From: Thomas [mailto:[EMAIL PROTECTED] Sent: 02 June 2005 10:20 To: php-general@lists.php.net Subject: [PHP] Multiple inserts as a single string? Hi there, I have a bit of strange question: when wanting to insert multiple records into the db, instead of looping through the set and executing mysql_query (which will then call the db n times), is it not better to concat a string with all the insert statements and let mysql handle the inserting, that way we don't call the db n times from php. Does that make any difference? Maybe I am just stupid . Thomas This message has been delivered to the Internet by the Revenue Internet e-mail service * -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] Multiple inserts as a single string?
Richard Davey wrote: > > Sure.. mysql_query doesn't support more than one query in the sql > statement. > At the risk of being thick, does the OP not mean something like this: INSERT INTO blah VALUES (value1, value2), (value3, value4) versus INSERT INTO blah VALUES (value1, value2) next iteration INSERT INTO blah VALUES (value3, value4) Regards Rory -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
RE: [PHP] Multiple inserts as a single string?
Thomas, I am not sure what database you are using, but there was a previous email thread about using execQuery($concatstring) for mySQL, which apparently worked. Make sure that your insert statements end with the standard line terminator character, usually ';'. Personally, I would output the statements to a file, then use the cmd line function for executing .sql files for that database. You could execute this from php by using the shell_exec function. This is so I could look at the executed sql file and find where the error is, I'm not a big fan of debugging in php. C. -Original Message- From: Thomas [mailto:[EMAIL PROTECTED] Sent: 02 June 2005 12:20 To: Shaw, Chris - Accenture Cc: php-general@lists.php.net Subject: RE: [PHP] Multiple inserts as a single string? * This e-mail has been received by the Revenue Internet e-mail service. * Hi Chris, Thanks, I thought so. You are quite right with the errors, I ran into some where it looked like that php does not allow you to execute such a concated string ... the error started at the second insert statement with no apparent reason. Is that a ph restriction? Thomas -Original Message- From: Shaw, Chris - Accenture [mailto:[EMAIL PROTECTED] Sent: 02 June 2005 12:20 PM To: Thomas; php-general@lists.php.net Subject: RE: [PHP] Multiple inserts as a single string? Thomas, If you're inserting alot of rows, (eg millions) then concating them and calling the db once probably would give a small speed advantage, because of the database handshaking. But I would look at the load the database is under, if there is alot of users hitting the database with small inserts/queries then the database is going to suffer as opposed to the same number of users hitting the database once with a script. The only problem I can think of, from php (client) point of view, if the script (concated inserts) errors, then you will need to handle where it went wrong, if it was loop, you know exactly what went wrong by outputing the current insert statement. C. -Original Message- From: Thomas [mailto:[EMAIL PROTECTED] Sent: 02 June 2005 10:20 To: php-general@lists.php.net Subject: [PHP] Multiple inserts as a single string? Hi there, I have a bit of strange question: when wanting to insert multiple records into the db, instead of looping through the set and executing mysql_query (which will then call the db n times), is it not better to concat a string with all the insert statements and let mysql handle the inserting, that way we don't call the db n times from php. Does that make any difference? Maybe I am just stupid . Thomas This message has been delivered to the Internet by the Revenue Internet e-mail service * -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php This message has been delivered to the Internet by the Revenue Internet e-mail service * -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] Multiple inserts as a single string?
Hello Thomas, Thursday, June 2, 2005, 10:20:11 AM, you wrote: T> I have a bit of strange question: when wanting to insert multiple T> records into the db, instead of looping through the set and T> executing mysql_query (which will then call the db n times), is it T> not better to concat a string with all the insert statements and T> let mysql handle the inserting, that way we don't call the db n T> times from php. Does that make any difference? Sure.. mysql_query doesn't support more than one query in the sql statement. If you want to do that then upgrade to PHP 5 (if you're not using it already) and use mysqli_multi_query instead. I guess packages like Pear DB may offer similar functionality, but I don't know for certain. Best regards, Richard Davey -- http://www.launchcode.co.uk - PHP Development Services "I do not fear computers. I fear the lack of them." - Isaac Asimov -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
RE: [PHP] Multiple inserts as a single string?
Thomas, If you're inserting alot of rows, (eg millions) then concating them and calling the db once probably would give a small speed advantage, because of the database handshaking. But I would look at the load the database is under, if there is alot of users hitting the database with small inserts/queries then the database is going to suffer as opposed to the same number of users hitting the database once with a script. The only problem I can think of, from php (client) point of view, if the script (concated inserts) errors, then you will need to handle where it went wrong, if it was loop, you know exactly what went wrong by outputing the current insert statement. C. -Original Message- From: Thomas [mailto:[EMAIL PROTECTED] Sent: 02 June 2005 10:20 To: php-general@lists.php.net Subject: [PHP] Multiple inserts as a single string? Hi there, I have a bit of strange question: when wanting to insert multiple records into the db, instead of looping through the set and executing mysql_query (which will then call the db n times), is it not better to concat a string with all the insert statements and let mysql handle the inserting, that way we don't call the db n times from php. Does that make any difference? Maybe I am just stupid . Thomas This message has been delivered to the Internet by the Revenue Internet e-mail service * -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
[PHP] Multiple inserts as a single string?
Hi there, I have a bit of strange question: when wanting to insert multiple records into the db, instead of looping through the set and executing mysql_query (which will then call the db n times), is it not better to concat a string with all the insert statements and let mysql handle the inserting, that way we don't call the db n times from php. Does that make any difference? Maybe I am just stupid . Thomas
Re: [PHP] multiple inserts into a db
On Wed, May 18, 2005 9:47 pm, mayo said:
> $ses_basket_items is the total number of items
> $orderID = the orderID which these items are a part of
> $ses_basket_id = is the itemID number
Which item ID number?
Cuz, like, you've got FOUR different itemID numbers.
Something isn't making sense here...
> for ($i=0;$i<$ses_basket_items;$i++){
>
> $query = "INSERT INTO orderedItems (orderID,itemID) VALUES
> ('$orderID','$ses_basket_id')";
echo $query, "\n";
You probably have an ARRAY of itemID numbers. You need to get each itemID
number out of that array and get it into your query.
> mysql_query($query) or die('Error, insert query failed');
>
> }
>
> mysql_close($conn);
You really do need to re-read the PHP Manual section about arrays and what
they are and how they work...
--
Like Music?
http://l-i-e.com/artists.htm
--
PHP General Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
[PHP] multiple inserts into a db
I'm putting ordered items into a db. The information is stored in
session variables.
Session_variable_with_itemID_has(1001,1002,1003,1004) however when
inserted into the db only 0,0,0,0 is recorded.
Assuming that this was the 40th recorded order the table should look
like this
TABLE: orderedItems
orderedItemsID -- orderID - itemID
159 - 40 - 1001
160 - 40 - 1002
161 - 40 -- 1003
162 - 40 - 1004
What comes out is:
orderedItemsID -- orderID - itemID
159 - 40 - 0
160 - 40 - 0
161 - 40 -- 0
162 - 40 - 0
The loop itself works as intended. However it is not inserting this
variable.
$ses_basket_items is the total number of items
$orderID = the orderID which these items are a part of
$ses_basket_id = is the itemID number
for ($i=0;$i<$ses_basket_items;$i++){
$query = "INSERT INTO orderedItems (orderID,itemID) VALUES
('$orderID','$ses_basket_id')";
mysql_query($query) or die('Error, insert query failed');
}
mysql_close($conn);
thx
Re: [PHP] Multiple Inserts
The trick here is to do some debugging :)
Firstly make sure that your error reporting level is set to E_ALL in
php.ini - then you've got some chance of seeing error and warning messages.
Secondly, while there are probably loads of ways of doing what you are
trying to do, all the correct ones will check the return value from ALL the
mysql calls. The mysql functions return 0 if something went wrong. There's
absolutely no point carrying on to the next stage since when you pass the 0
into the next stage it will just complain (if your error reporting level is
set high enough) - it simply can't do the next bit with a connection or
result handle of 0.
So, for each mysql call do something like:
$Thingy = mysql_whatnot(blah) or die('it went wrong doing whatnot, mysql
says '.mysql_error());
and if that doesn't give you the clue you need (which it will 99% of the
time, echo out the SQL statement and see if that looks silly in any way.
Good luck.
--
Phil Driscoll
Dial Solutions
+44 (0)113 294 5112
http://www.dialsolutions.com
http://www.dtonline.org
-Original Message-
From: [EMAIL PROTECTED] <[EMAIL PROTECTED]>
To: [EMAIL PROTECTED] <[EMAIL PROTECTED]>
Date: Friday, March 09, 2001 12:31 PM
Subject: [PHP] Multiple Inserts
>Hi,
>
>I am trying to do some multiple inserts using the following code but it
>doesn`t seem to work, it inserts the first one but then the second is
>inserted as blank. Where the value being passed is
>
>$Emails="[EMAIL PROTECTED], [EMAIL PROTECTED]";
>
>// Get Login info
>
>require("blah...");
>
>// Connect to MySQL
>
>$connection=mysql_connect($host, $user, $pass);
>
>// Format the Emails
>
>$Emails1=str_replace(" ","",$Emails);
>$GetEmails=explode(",",$Emails1);
>
>// Insert into Members Database
>
>for
=0;$a$SQLStatement = "INSERT INTO Members (Email) VALUES ('".$GetEmails[$a] ."')"
>or die ("Problem");
>$db = mysql_select_db($dbase, $connection);
>$SQLResult = mysql_query($SQLState
ment);
>
>}
>
>?>
>
>Anyone know what might be going wrong, or perhaps I have the wrong method?
>
>Thanks
>Ade
>
>--
>PHP General 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]
>
>
--
PHP General 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] Multiple Inserts
In a message dated 09/03/2001 12:38:08 GMT Standard Time,
[EMAIL PROTECTED] writes:
<< Whoa, you're mixing apples with oranges. Try:
$db = mysql_select_db($dbase, $connection);
$SQLStatement = "INSERT INTO Members (Email) VALUES ('" . $GetEmails[$a] .
"')"; //removed the 'or die' portion
$SQLResult = mysql_query($SQLStatement,$connection) or die ("Problem " .
mysql_error() ); //moved 'or die' here, using more detailed info
--
CC >>
Still no joy, and it doesn`t give out any errors whatsoever.
Ade
--
PHP General 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] Multiple Inserts
In article <[EMAIL PROTECTED]>, [EMAIL PROTECTED] wrote:
> $SQLStatement = "INSERT INTO Members (Email) VALUES ('".$GetEmails[$a] ."')"
> or die ("Problem");
> $db = mysql_select_db($dbase, $connection);
> $SQLResult = mysql_query($SQLStatement);
Whoa, you're mixing apples with oranges. Try:
$db = mysql_select_db($dbase, $connection);
$SQLStatement = "INSERT INTO Members (Email) VALUES ('" . $GetEmails[$a] .
"')"; //removed the 'or die' portion
$SQLResult = mysql_query($SQLStatement,$connection) or die ("Problem " .
mysql_error() ); //moved 'or die' here, using more detailed info
--
CC
--
PHP General 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]
[PHP] Multiple Inserts
Hi, I am trying to do some multiple inserts using the following code but it doesn`t seem to work, it inserts the first one but then the second is inserted as blank. Where the value being passed is $Emails="[EMAIL PROTECTED], [EMAIL PROTECTED]"; Anyone know what might be going wrong, or perhaps I have the wrong method? Thanks Ade -- PHP General 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]
