How to Bulk Insert Array Elements into MySQL Using PDO and Prepared Statements?

Bulk Inserting Array Elements into MySQL Using PDO and Prepared Statements

In database management systems, situations arise where you need to insert multiple elements from an array into a single table. To execute such queries efficiently, this article will guide you through the approach of using PDO prepared statements and provide a solution.

Solution Overview

We start by creating a dynamic SQL query that will accommodate multiple parameter placeholders. Using a loop, we build our query by iterating over the array elements, creating one set of parameter placeholders for each element.

Next, we initialize our prepared statement using PDO::prepare() and pass the constructed SQL query as its argument. Finally, we execute the prepared statement using PDOStatement::execute(), passing an array containing all the parameter values we collected in the previous step.

Implementation

Let's walk through the code implementation below:

<code class="php">$sql = 'INSERT INTO table (memberID, programID) VALUES ';
$insertQuery = array();
$insertData = array();

foreach ($data as $row) {
    $insertQuery[] = '(?, ?)';
    $insertData[] = $memberid;
    $insertData[] = $row;
}

if (!empty($insertQuery)) {
    $sql .= implode(', ', $insertQuery);
    $stmt = $db->prepare($sql);
    $stmt->execute($insertData);
}</code>

Conclusion

Using this approach, you can efficiently insert elements from an array as a single insert statement, avoiding the need for multiple individual queries. This method is not only more efficient but also safer, as prepared statements protect against SQL injection attacks.

The above is the detailed content of How to Bulk Insert Array Elements into MySQL Using PDO and Prepared Statements?. For more information, please follow other related articles on the PHP Chinese website!