How can I insert multiple rows of data into a database using a single prepared statement with PDO?

Inserting Multiple Rows with PDO Prepared Statements

Insertion of multiple data rows into a database using a single prepared statement is possible. Prepared statements enhance security and efficiency by preventing SQL injection attacks and optimizing query execution.

Consider the following example for inserting data into a table named 'Table' with columns 'col1', 'col2', and 'col3':

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

If multiple rows need to be inserted, the following approach can be implemented:

1. Construct the VALUES Clause: Join multiple sets of values using parentheses and commas. For instance, values for three rows would look like: ('abc', 'def', 'ghi'), ('abc', 'def', 'ghi'), ('abc', 'def', 'ghi').
2. Generate the Placeholder Template: Fill an array with placeholders (?) for each value to be inserted. For three rows with three columns each, this would be (?, ?, ?), (?, ?, ?), (?, ?, ?).

3. Combine the SQL Statement: Append the placeholder template as the VALUES clause to the INSERT query. The final statement would be:

   INSERT INTO Table (col1, col2, col3) VALUES (?, ?, ?), (?, ?, ?), (?, ?, ?)

4. Prepare and Execute: Prepare the extended statement using PDO. Execute the statement, passing an array with all the values to be inserted in the same order as the placeholders.

   $rows = array(
               array('abc', 'def', 'ghi'),
               array('abc', 'def', 'ghi'),
               array('abc', 'def', 'ghi')
   );
   
   // Your code here as explained in the full response

The advantage of this approach is that it combines all values into a single INSERT query while maintaining the security and optimization benefits of prepared statements.

For scenarios involving a large number of rows, it may be more efficient to use a loop to insert them one at a time. This ensures better performance and avoids potential memory issues.

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