PDO MySQL: How to Insert Multiple Rows in One Query Without "Array to string conversion" Error?

PDO MySQL: Insert Multiple Rows in One Query

Question:

In a PDO class designed for multiple insert, despite creating a parameterized query, an "Array to string conversion" error occurs when executing the query. How can this error be resolved?

Answer:

To resolve this error, one approach is to iterate through the data array and bind the values one by one using bindValue instead of binding the entire array. This ensures that each value is properly bound to a specific parameter.

Here's an example of implementing this approach:

$query = "INSERT INTO $table (key1, key2, key3) VALUES ";
$qPart = array_fill(0, count($data), "(?, ?, ?)");
$query .= implode(",", $qPart);
$stmt = $this->start->prepare($query);
$i = 1;
foreach ($data as $item) { 
    $stmt->bindValue($i++, $item['key1']);
    $stmt->bindValue($i++, $item['key2']); 
    $stmt->bindValue($i++, $item['key3']);
}
$stmt->execute();

Alternatively, you can use the foreach loop inside the prepare statement to bind values for each row:

$stmt = $pdo->prepare("INSERT INTO foo VALUES (1, 2, 3)");
foreach($data as $item) {
    $stmt->execute($item);
}

This approach executes the query multiple times, one for each row. However, it is simpler to implement than the former approach.

The above is the detailed content of PDO MySQL: How to Insert Multiple Rows in One Query Without "Array to string conversion" Error?. For more information, please follow other related articles on the PHP Chinese website!