How Can I Efficiently Insert an Array of Values into a MySQL Database Using PDO?

Using PDO to Insert an Array into a MySQL Database with a Single Prepared Statement

When performing online enrollments, it's common to encounter situations where customers select multiple programs for enrollment. These programs can be represented as an array of three-digit integers. To insert these arrays into a database, a single prepared statement can be more efficient than multiple executions.

Example Scenario

Consider an array containing the following program IDs:

$data = [155, 165, 175, 185];

Traditional Loop Approach

A typical approach would involve iterating through the array and executing multiple INSERT statements:

<code class="php">for ($j = 0; $j < count($data) - 1; $j++) {
    $stmt = $db->prepare("INSERT INTO table SET memberID=?, programID=?, date_added=NOW()");
    $stmt->execute(array($memberid, $data[$j]));
}</code>

However, this method is less efficient than a single INSERT statement.

Dynamic SQL Statement

Building a single dynamic SQL statement is a more efficient option:

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

This approach uses PDO to create a single prepared statement by concatenating placeholders with the appropriate data. It's important to validate the $insertQuery array before executing the statement to avoid empty insertions.

The above is the detailed content of How Can I Efficiently Insert an Array of Values into a MySQL Database Using PDO?. For more information, please follow other related articles on the PHP Chinese website!