php - Insert multiple rows with PDO prepared statements

Question

I would like to know if it is possible to insert multiple rows using one prepared statement. Below is an example of how I would normally insert one row into the db:

$params=array();
$params[':val1']="val1";
$params[':val2']="val2";
$params[':val3']="val3";
$sql="INSERT INTO table VALUES (col1,col2,col3) VALUES (:val1,:val2,:val3)";
$stmt=DB::getInstance()->prepare($sql);
$stmt->execute($params);

The values I want to insert will come from an array, for example: $values[0]['val1']; $values[0]['val2']; $values[0]['val3']; $values[1]['val1']; $values[2]['val2'];

etc.

This code may have to insert a few hundred rows at once, I thought about creating a loop to create hundreds of params and then append the sql statement with an extra insert for each row but I thought there must be a better way. What would be the best way to do this?

Answer 1

The first important thing to say is that you can insert multiple rows thanks to only one INSERT query

INSERT INTO Table (col1, col2, col3) 
VALUES ('abc', 'def', 'ghi'),
       ('abc', 'def', 'ghi'),
       ('abc', 'def', 'ghi'),
       ('abc', 'def', 'ghi'),
       ('abc', 'def', 'ghi')
       -- and so on...

Once you know that, you're able to get a good solution with PDO (for instance).
You have to keep in mind that you want a complete prepare and execute process (in term of security, you have to pass each parameter separately).

Let's say you have rows to insert structured as follow:

$rows = array(
              array('abc', 'def', 'ghi'), // row 1 to insert
              array('abc', 'def', 'ghi'), // row 2 to insert
              array('abc', 'def', 'ghi')  // row 3 to insert
              // and so on ...
);

Your goal is to have this result as a prepared query:

INSERT INTO Table (col1, col2, col3) 
VALUES (?, ?, ?),
       (?, ?, ?),
       (?, ?, ?)

With its corresponding execute:

PDOStatement::execute(array('abc', 'def', 'ghi', 'abc', 'def', 'ghi', 'abc', 'def', 'ghi'));

Well, you only have to do it now:

$rows = array(
              array('abc', 'def', 'ghi'),
              array('abc', 'def', 'ghi'),
              array('abc', 'def', 'ghi')
);

$row_length = count($rows[0]);
$nb_rows = count($rows);
$length = $nb_rows * $row_length;

/* Fill in chunks with '?' and separate them by group of $row_length */
$args = implode(',', array_map(
                                function($el) { return '('.implode(',', $el).')'; },
                                array_chunk(array_fill(0, $length, '?'), $row_length)
                            ));

$params = array();
foreach($rows as $row)
{
   foreach($row as $value)
   {
      $params[] = $value;
   }
}

$query = "INSERT INTO Table (col1, col2, col3) VALUES ".$args;
$stmt = DB::getInstance()->prepare($query);
$stmt->execute($params);

And... That's it!

This way, each param is treated separately, which is what you want (security, security, security!) and all of it, in a dynamic way, with only one INSERT query

---

If you have too many rows to insert (see this), you should execute one by one

$rows = array(
              array('abc', 'def', 'ghi'), // row 1 to insert
              array('abc', 'def', 'ghi'), // row 2 to insert
              array('abc', 'def', 'ghi')  // row 3 to insert
              // and so on ...
);

$args = array_fill(0, count($rows[0]), '?');

$query = "INSERT INTO Table (col1, col2, col3) VALUES (".implode(',', $args).")";
$stmt = $pdo->prepare($query);

foreach ($rows as $row) 
{
   $stmt->execute($row);
}