How to Insert Multiple Rows into a MySQL Database using PDO in a Single Query?

PDO MySQL: Insert Multiple Rows in One Query

Inserting multiple rows into a MySQL database using PDO can be achieved by carefully constructing a single query. The common approach of preparing a query with placeholders and then binding values loop through the data array does not work with multiple inserts.

To overcome this limitation, we can create a long query that contains all the necessary values and placeholders. Here's an example:

$query = "INSERT INTO $table (key1, key2, key3, etc) VALUES (:key1, :key2, :key3, etc), (:key1, :key2, :key3, etc), (:key1, :key2, :key3, etc)";

$dbh = new PDO(...);
$stmt = $dbh->prepare($query);

$i = 1;
$data = [[], ...]; // Assuming $data contains the rows to be inserted

foreach($data as $row) {
    foreach ($row as $key => $value) {
        $stmt->bindValue($i++, $value);
    }
}

$stmt->execute();

In this example, the $data array contains the values of each row. We loop through the array and bind each value to a placeholder with a specific index. This ensures that the values are properly ordered and inserted into the database.

By preparing a single query and binding the values in this manner, we can efficiently insert multiple rows into a MySQL table using PDO.

The above is the detailed content of How to Insert Multiple Rows into a MySQL Database using PDO in a Single Query?. For more information, please follow other related articles on the PHP Chinese website!