A community in which webmasters can ask for help with topics such as PHP coding , MySQL , IT jobs, web design, IT security.
Current location:homephp forumphp talk in 2009 yearIs it bad to put a MySQL query in a PHP loop? - page 1
User InfoPosts
Is it bad to put a MySQL query in a PHP loop?#1
I often have large arrays, or large amounts of dynamic data in PHP that I need to run MySQL queries to handle.

Is there a better way to run many processes like INSERT or UPDATE without looping through the information to be INSERT-ed or UPDATE-ed?

Example (I didn(t use prepared statement for brevity sake):


$myArray = array((apple(,(orange(,(grape();

foreach($myArray as $arrayFruit) {
$query = "INSERT INTO `Fruits` (`FruitName`) VALUES ((" . $arrayFruit . "()";
mysql_query($query, $connection);
}

posted date: 2009-04-08 22:15:00


Re: Is it bad to put a MySQL query in a PHP loop?#2
I had made out the solution of this problem. click to view my topic...

hope that hepls.

posted date: 2009-04-08 22:15:01


Re: Is it bad to put a MySQL query in a PHP loop?#3
OPTION 1You can actually run multiple queries at once.$queries = ((;foreach(){ $queries .= "INSERT....;"; //notice the semi colon}mysql_query($queries, $connection);This would save on your processing.OPTION 2If your insert is that simple for the same table, you can do multiple inserts in ONE query$fruits = "((".implode("(), ((", $fruitsArray)."()";mysql_query("INSERT INTO Fruits (Fruit) VALUES $fruits", $connection);The query ends up looking something like this:$query = "INSERT INTO Fruits (Fruit) VALUES ((Apple(), ((Pear(), ((Banana()";This is probably the way you want to go.

posted date: 2009-04-08 22:20:00


Re: Is it bad to put a MySQL query in a PHP loop?#4
right...so one large payload (potentially 100 insert statements bundled) over separate. makes sense...thanks:)

posted date: 2009-04-08 22:27:00


Re: Is it bad to put a MySQL query in a PHP loop?#5
Option #2 is really the better way to go if you can swing it.

posted date: 2009-04-08 22:28:00


Re: Is it bad to put a MySQL query in a PHP loop?#6
haha that is some savvy implosion...clever. thanks

posted date: 2009-04-08 22:30:00


Re: Is it bad to put a MySQL query in a PHP loop?#7
Yeah...the day I showed that to some co-workers it opened up new doors

posted date: 2009-04-08 22:31:00


Re: Is it bad to put a MySQL query in a PHP loop?#8
Of course, don't forget to escape the $fruitsArray, else it'll bite you the moment somebody tries to insert "bunch o'grapes"

posted date: 2009-04-08 23:45:00


Re: Is it bad to put a MySQL query in a PHP loop?#9
If you have the mysqli class, you can iterate over the values to insert using a prepared statement.$sth = $dbh->prepare("INSERT INTO Fruits (Fruit) VALUES (?)");foreach($fruits as $fruit){ $sth->reset(); // make sure we are fresh from the previous iteration $sth->bind_param((s(, $fruit); // bind one or more variables to the query $sth->execute(); // execute the query}

posted date: 2009-04-09 09:44:00


Re: Is it bad to put a MySQL query in a PHP loop?#10
good question ;)

posted date: 2009-04-09 09:57:00


Re: Is it bad to put a MySQL query in a PHP loop?#11
one thing to note about your original solution over the implosion method of jerebear (which I have used before, and love) is that it is easier to read. The implosion takes more programmer brain cycles to understand, which can be more expensive than processor cycles. premature optimisation, blah, blah, blah... :)

posted date: 2009-04-09 10:19:00


Re: Is it bad to put a MySQL query in a PHP loop?#12
haha...sorry i was really curious, and i didn't wanna look like a jerk hounding you

posted date: 2009-04-10 12:00:00


select page: « 1 2 »
Copyright ©2008-2017 www.momige.com, all rights reserved.