How to Efficiently Insert Multiple Rows into a MySQL Table Using PHP and PDO?

Inserting Arrays into MySQL with PHP and PDO

When initializing online programs, customers may enroll in multiple programs represented as three-digit integers and stored in an array. For example, to enroll in programs 155, 165, 175, and 185, the following array would be used:

<code class="php">$data = array(155, 165, 175, 185);</code>

Traditionally, one would create a loop to execute multiple INSERT statements, such as:

<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, building a single dynamic SQL statement is considered more efficient. A possible solution would be:

<code class="php">$sql = array();
foreach( $data as $row ) {
    $sql[] = '("'.$memberid.'", "'.$row[$j].'", NOW()")';
}
mysql_real_query('INSERT INTO table (memberid, programid) VALUES '.implode(',', $sql));</code>

With PDO, a more suitable approach is:

<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 technique allows inserting multiple rows into a database table using a single prepared statement, reducing the number of database calls and improving performance.

The above is the detailed content of How to Efficiently Insert Multiple Rows into a MySQL Table Using PHP and PDO?. For more information, please follow other related articles on the PHP Chinese website!