Can I Use a Single Prepared Statement to Insert Multiple Rows in PDO?

Inserting Multiple Rows Using Prepared Statements in PDO

Inserting data into a database efficiently is crucial, especially when dealing with large datasets. This question explores the possibility of inserting multiple rows using a single prepared statement in PHP's PDO extension, to optimize the insertion process.

The Problem:

The need arises to insert multiple rows into a table using dynamic data from an array. A typical approach to insert a single row using PDO is demonstrated:

$params = [
    ':val1' => 'val1',
    ':val2' => 'val2',
    ':val3' => 'val3',
];

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

The Answer:

To insert multiple rows with a single prepared statement, it's possible to construct a single INSERT query that contains multiple values. The parameters are passed separately to ensure security and flexibility.

Example:

Given an array of rows to insert:

$rows = [
    ['abc', 'def', 'ghi'],
    ['abc', 'def', 'ghi'],
    ['abc', 'def', 'ghi'],
];

The following code creates a prepared query with placeholders for each row:

$row_length = count($rows[0]);
$nb_rows = count($rows);
$length = $nb_rows * $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);

Each row's values are passed as parameters separately, maintaining security and allowing for efficient insertion of multiple rows.

The above is the detailed content of Can I Use a Single Prepared Statement to Insert Multiple Rows in PDO?. For more information, please follow other related articles on the PHP Chinese website!