Is it bad to put a MySQL query in a PHP loop?

Question

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);
}

Answer 1

OPTION 1 You 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 2

If 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.

Answer 2

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
}