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
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, 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?
Richard Davey wrote: snip Sure.. mysql_query doesn't support more than one query in the sql statement. snip 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?
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?
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?
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
