Inserting Multiple Rows with PDO Prepared Statements

Inserting multiple rows into a database using a single prepared statement is not only possible but also recommended for its efficiency and security.

Approach with a Single Insert Query

The first method involves using a single INSERT query with multiple values:

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...

To execute this query using PDO, you can follow these steps:

$query = "INSERT INTO Table (col1, col2, col3) 
VALUES (?, ?, ?),
       (?, ?, ?),
       (?, ?, ?)
";
$params = array('abc', 'def', 'ghi', 'abc', 'def', 'ghi', 'abc', 'def', 'ghi');
$stmt = DB::getInstance()->prepare($query);
$stmt->execute($params);

Approach with Individual Insert Queries

If the number of rows to be inserted is too large, you may need to execute individual insert queries:

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

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

This approach ensures that each row is inserted separately, minimizing the risk of issues during insertion.

Conclusion

Whether you use a single insert query with multiple values or individual insert queries, PDO prepared statements provide efficient and secure methods for inserting multiple rows into a database. Choosing the right approach depends on the specific requirements of the application and database.