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
}

Answer 3

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... :)

Answer 4

I was inspired by jerebear's answer to build something like his second option for one of my current projects. Because of the shear volume of records I couldn't save and do all the data at once. So I built this to do imports. You add your data, and then call a method when each record is done. After a certain, configurable, number of records the data in memory will be saved with a mass insert like jerebear's second option.

// CREATE TABLE example ( Id INT, Field1 INT, Field2 INT, Field3 INT);
$import=new DataImport($dbh, 'example', 'Id, Field1, Field2, Field3');
foreach ($whatever as $row) {
  // add data in the order of your column definition
  $import->addValue($Id);
  $import->addValue($Field1);
  $import->addValue($Field2);
  $import->addValue($Field3);
  $import->nextRow();
}
$import->lastRow();

Answer 5

One thing to note about jerebear's answer with multiple VALUE-blocks in one INSERT:

It can be rather dangerous for really large amounts of data, because most DBMS have an upper limit on the size of the commands they can handle. If you exceed that with too many VALUE-blocks, your insert will fail. On MySQL for example the limit is usually 1MB AFAIK.

So you should figure out what the maximum size is (ideally at runtime, might be available from the database metadata), and make sure you don't exceed it by spreading your lists of values over several INSERTs.