PDO MySQL: Insert multiple rows in one query

Question

Hello I am making a class for doing multiple insert in pdo. It is something like this ``` INSERT INTO $table (key1,key2,key3,etc) VALUE (value1,value2,value3,etc), (value1,value2,value3,etc), (value1,value2,value3,etc) ``` After searching I found out that I have to build something like ``` INSERT INTO $table (key1,key2,key3,etc) VALUE (:key1,:key2,:key3,etc), (:key1,:key2,:key3,etc), (:key1,:key2,:key3,etc) ``` then execute with this `$this->execute($data);` where `$data` is ``` 0 => array 'key1' => 'value1' 'key2' => 'value2' 'key3' => 'value3' 1 => array 'key1' => 'value1' 'key2' => 'value2' 'key3' => 'value3' etc ``` the problem is i still get an error `Array to string conversion` on `$insert->execute($data);` how can i fix that? here's a snippet of what I'm making. ``` public function multipleInsert($table, $data = array()) { # INSERT (name) VALUE (value),(value) if (count($data) > 1) { $fieldnames = array_keys($data[0]); $count_inserts = count(array_values($data)); $count_values…

Accepted Answer

An easy way for this avoiding the complications would be something like this

```
$stmt = $pdo->prepare('INSERT INTO foo VALUES(:a, :b, :c)');
foreach($data as $item)
{
    $stmt->bindValue(':a', $item[0]);
    $stmt->bindValue(':b', $item[1]);
    $stmt->bindValue(':c', $item[2]);
    $stmt->execute();
}
```

However, this executes the statement multiple times. So, it is better if we create a long single query in order to do this.

Here is an example of how we can do this.

```
$query = "INSERT INTO foo (key1, key2) VALUES "; //Prequery
$qPart = array_fill(0, count($data), "(?, ?)");
$query .=  implode(",",$qPart);
$stmt = $dbh -> prepare($query); 
$i = 1;
foreach($data as $item) { //bind the values one by one
   $stmt->bindValue($i++, $item['key1']);
   $stmt->bindValue($i++, $item['key2']);
}
$stmt -> execute(); //execute
```

PDO MySQL: Insert multiple rows in one query

Solution

Problem