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!