Home >Database >Mysql Tutorial >How to Insert Multiple Rows into a MySQL Database with a Single PDO Query?

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

Mary-Kate Olsen
Mary-Kate OlsenOriginal
2024-11-07 02:59:03669browse

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!

Statement:
The content of this article is voluntarily contributed by netizens, and the copyright belongs to the original author. This site does not assume corresponding legal responsibility. If you find any content suspected of plagiarism or infringement, please contact admin@php.cn