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

PDO MySQL: Inserting Multiple Rows in a Single Query with BindValues

To insert multiple rows into a MySQL database in one query using PHP Data Objects (PDO), consider the following steps:

1. Prepare the Query:
Create a customizable query string using placeholders for the values you want to insert. For example:

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

2. Bind Values to Placeholders:
Use the bindValue() method of the prepared statement to bind the values from your data array to the placeholders in the query.

$i = 1;
foreach ($data as $item) {
    $stmt->bindValue($i++, $item['key1']);
    $stmt->bindValue($i++, $item['key2']);
    $stmt->bindValue($i++, $item['key3']);
}

3. Execute the Query:
Finally, execute the prepared statement, which will insert all the bound values into the database.

$stmt->execute();

Example:
Here's an example of how to implement this technique in your code:

$pdo = new PDO('mysql:host=localhost;dbname=database_name', 'username', 'password');

$query = "INSERT INTO table_name (column1, column2) VALUES (?, ?), (?, ?), (?, ?)";

$data = [
    ['value1', 'value2'],
    ['value3', 'value4'],
    ['value5', 'value6'],
];

$stmt = $pdo->prepare($query);

$i = 1;
foreach ($data as $item) {
    $stmt->bindValue($i++, $item[0]);
    $stmt->bindValue($i++, $item[1]);
}

$stmt->execute();

By utilizing this method, you can efficiently insert multiple rows into a MySQL database with a single query, avoiding the limitations of the original code.

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