How to Insert an Array into a MySQL Prepared Statement with PHP and PDO?

Inserting an Array into a MySQL Prepared Statement with PHP and PDO

When attempting to insert an array of values into a MySQL table using a prepared statement, it's important to consider the appropriate approach for efficiency and security. This article explores how to construct a dynamic SQL statement that can handle an array of values.

Traditionally, a loop could be used to execute multiple prepared statements, but building a single dynamic SQL statement is generally preferred. To create such a statement, you can use the approach outlined below:

1. Define the base SQL query:

   $sql = 'INSERT INTO table (memberID, programID) VALUES ';

2. Loop through the array:

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

3. Combine the query parts:

   if (!empty($insertQuery)) {
    $sql .= implode(', ', $insertQuery);
   }

4. Prepare and execute the statement:

   $stmt = $db->prepare($sql);
   $stmt->execute($insertData);

This approach ensures that all values are inserted in one go, while maintaining the security of prepared statements by using placeholders (?). It is more efficient than executing multiple statements and minimizes potential SQL injection vulnerabilities.

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