使用Mysql多次插入三行

Question

Let's say I have three rows I need to insert for each ID.

ID   -   FOO    -    BAR
0       test1     something
0       test2     something
0       test3     something

12       test1     something
12       test2     something
12       test3     something

34       test1     something
34       test2     something
34       test3     something

Here is my current code

<?php 

$connection = new PDO("mysql:host=$dbhost;dbname=$dbname",$dbuser,$dbpass);

$sql = "INSERT INTO books (id,foo,bar) VALUES (?,?,?)";

$statement = $connection->prepare("$sql");

$statement->execute(array("1", "test1", "something"));

At the moment, I am only able to insert 1 row at a time, updating the values in the execute array each time. Is it possible to loop through an insert whilst using some sort of array to insert all my values?

Answer 1

I would suggest using a simple prepared statement with value binding, like so:

// array with data you want to insert
$books = array(
    0 => array('id' => 1, 'foo' => 'somefoo1', 'bar' => 'somebar1'),
    1 => array('id' => 2, 'foo' => 'somefoo2', 'bar' => 'somebar2'),
);

// create PDO connection
$pdo = new PDO("mysql:host=$dbhost;dbname=$dbname",$dbuser,$dbpass);

// create a prepared SQL statement (for multiple executions)
$stmt = $pdo->prepare('INSERT INTO books (id,foo,bar) VALUES (:id,:foo,:bar)');

// iterate over your data, bind the new values to the prepared statement
// finally execute = insert
foreach($books as $book)
{
    $stmt->bindValue(':id', $book['id']);
    $stmt->bindValue(':foo', $bookm['foo']);
    $stmt->bindValue(':bar', $book['bar']);
    $stmt->execute();
}