Home > Database > Mysql Tutorial > body text

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

Mary-Kate Olsen
Release: 2024-11-07 02:59:03
Original
582 people have browsed it

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 (?, ?, ?)";
Copy after login

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']);
}
Copy after login

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

$stmt->execute();
Copy after login

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();
Copy after login

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!

source:php.cn
Statement of this Website
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
Latest Articles by Author
Popular Tutorials
More>
Latest Downloads
More>
Web Effects
Website Source Code
Website Materials
Front End Template
About us Disclaimer Sitemap
php.cn:Public welfare online PHP training,Help PHP learners grow quickly!