Can PDO Prepared Statements Efficiently Insert Multiple Rows in a Single Query?

Inserting Multiple Rows with PDO Prepared Statements

To enhance security with PDO, developers seek methods to insert multiple rows in a single query using prepared statements. This article explores the feasibility and implementation of such a solution.

Inserting multiple rows in one query is faster than regular inserts. Prepared statements use constant parts explicitly written in the code, providing 100% security.

To implement this, create a VALUES part with a sequence of placeholders for a single row. Determine the number of fields and rows to repeat this sequence accordingly.

// VALUES part for a single row
$values = str_repeat('?,', count($data[0]) - 1) . '?';

// Construct the entire query
$sql = "INSERT INTO table (columnA, columnB) VALUES " .
    // Repeat the VALUES part for each row
    str_repeat("($values),", count($data) - 1) . "($values)";

$stmt = $db->prepare($sql);
// Execute with all values from $data
$stmt->execute(array_merge(...$data));

This approach is secure as the query contains constant parts. It's compatible with both MySQLi and PDO across all supported PHP versions.

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