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

PDO MySQL: Insert Multiple Rows in One Query

Inserting multiple rows in a single query using PDO can be a daunting task. However, by utilizing the power of prepared statements, we can simplify this process and avoid common errors.

The Problem: Array to String Conversion

When you encounter an "Array to string conversion" error when attempting to execute an INSERT query with multiple rows, it is likely that your PDO statement is attempting to bind an array to a placeholder instead of individual values.

Solution: Bind Individual Values

To resolve this issue, you should iterate through the values of each row and bind them individually to the prepared statement. This can be achieved using the bindParam() method of the PDO statement object.

For example, consider the following code:

$query = "INSERT INTO $table (key1, key2, key3) VALUES (:key1, :key2, :key3)";
$stmt = $pdo->prepare($query);

foreach ($data as $item) {
    $stmt->bindParam(':key1', $item['key1']);
    $stmt->bindParam(':key2', $item['key2']);
    $stmt->bindParam(':key3', $item['key3']);
    $stmt->execute();
}

This code binds each value in the $data array to the corresponding placeholder in the prepared statement and executes the query multiple times, one for each row in the array.

Alternative Solution: Use Array Bindings

Alternatively, some PDO drivers support array bindings, which allow you to bind an entire array of values to a single placeholder. However, this feature is not universally supported and may not be available in all cases.

Improved Code Example

Here is an improved version of your multipleInsert() function that uses prepared statements and binds values individually:

public function multipleInsert($table, $data = array())
{
    if (count($data) > 1) {
        $fieldnames = array_keys($data[0]);
        $query = "INSERT INTO $table (`" . implode('`, `', $fieldnames) . "`) VALUES ";
        $query .= implode(', ', array_fill(0, count($data), "(:" . implode(', :', $fieldnames) . ")"));
        $stmt = $this->start->prepare($query);

        $i = 1;
        foreach ($data as $item) {
            foreach ($item as $key => $value) {
                $stmt->bindParam(":$key$i", $item[$key]);
                $i++;
            }
        }

        $stmt->execute();

        $return['status'] = true;
        $return['lastid'] = $this->start->lastInsertId();

        return $return;
    } else {
        die('$data is less than two arrays, use single insert instead.');
    }
}

The above is the detailed content of How to Insert Multiple Rows in a Single MySQL Query with PDO?. For more information, please follow other related articles on the PHP Chinese website!