How can I efficiently insert multiple rows into a database using PDO prepared statements?

Inserting Multiple Rows Efficiently with PDO Prepared Statements

Instead of executing multiple insert statements with separate parameters, PDO allows for the insertion of multiple rows using a single query. This approach enhances performance and security by preventing the need to prepare and execute the statement multiple times.

For example, to insert values from the array $values into the table table:

$params = [];
foreach ($values as $value) {
    array_push($params, $value['val1'], $value['val2'], $value['val3']);
}

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

$args = implode(',', array_map(function($el) { return '('.implode(',', $el).')'; }, array_chunk(array_fill(0, $length, '?'), $row_length)));

$query = "INSERT INTO table VALUES " . $args;
$stmt = DB::getInstance()->prepare($query);
$stmt->execute($params);

In this solution, the ? placeholders are dynamically generated based on the number of rows and columns. The parameters are then appended to an array and passed to the execute method as a single array. This guarantees that each parameter is handled separately, ensuring data security.

Alternatively, if there are too many rows to insert efficiently using a single query, consider executing them individually:

$args = array_fill(0, count($values[0]), '?');
$query = "INSERT INTO table VALUES (".implode(',', $args).")";
$stmt = $pdo->prepare($query);

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

This approach maintains the security benefits of using prepared statements while accommodating large datasets.

The above is the detailed content of How can I efficiently insert multiple rows into a database using PDO prepared statements?. For more information, please follow other related articles on the PHP Chinese website!