How Can I Efficiently Insert Multiple Data Points from an Array into MySQL Using Prepared Statements in PHP & PDO?

Inserting Multiple Data into MySQL Using Prepared Statements with PHP & PDO

In scenarios like online enrollment, users often select multiple items they wish to purchase, enroll in, or join. These items are usually stored in arrays to facilitate their processing. However, inserting data from arrays into MySQL databases using prepared statements in PHP and PDO can be challenging. This article explores methods to overcome this challenge.

One approach is to use a for loop and execute multiple instances of the SQL statement, as seen below:

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

While this method is relatively simple, it presents performance issues when dealing with large datasets.

A more efficient approach is to dynamically build a single SQL statement. One can achieve this by iterating over the array and generating a comma-separated list of placeholders and values within parentheses:

<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 method effectively combines the individual data into a single query for insertion into the database. It ensures efficient processing and reduced overhead, making it suitable for scenarios with large datasets or when performance optimization is critical.

The above is the detailed content of How Can I Efficiently Insert Multiple Data Points from an Array into MySQL Using Prepared Statements in PHP & PDO?. For more information, please follow other related articles on the PHP Chinese website!