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