Why Does My Multiple Insert Query Fail with 'SQLSTATE[HY093]: Invalid parameter number'?

"Error when preparing a multiple insert query"

When attempting to execute a multiple insert query, you may encounter an error like "SQLSTATE[HY093]: Invalid parameter number: parameter was not defined". This error occurs when the number of placeholder values (?) in the query does not match the number of parameters being passed in.

Consider the following code example:

// BUILD VALUES
$count = count($matches);
for($i = 0; $i < $count; ++$i) {
    $values[] = '(?)';
}
// INSERT INTO DATABASE
$q = $this->dbc->prepare("INSERT INTO hashes (hash) VALUES " . implode(', ', $values) . " ON DUPLICATE KEY UPDATE hash = hash");
$q->execute($matches);

In this scenario, you might be puzzled because count($matches) is equal to count($values) just before execute is called. However, the error persists.

Underlying Issue:
The underlying issue arises when $values is initialized with existing values before populating it with placeholder values. As a result, the count mismatch occurs.

Solution:
To resolve this error, ensure that you initialize an empty array for $values before populating it within the loop. This guarantees that the number of placeholder values matches the number of data points being inserted.

Additional Considerations:

• Verify that the hash column has a unique index to prevent duplicate entries.
• Use a prepared statement ($stmt) instead of the prepare and execute methods.

Revised Code Example:

$matches = array('1');
$count = count($matches);
$values = [];
for($i = 0; $i < $count; ++$i) {
    $values[] = '(?)';
}

// INSERT INTO DATABASE
$sql = "INSERT INTO hashes (hash) VALUES " . implode(', ', $values) . " ON DUPLICATE KEY UPDATE hash=values(hash)";
$stmt = $dbh->prepare($sql);
$data = $stmt->execute($matches);

The above is the detailed content of Why Does My Multiple Insert Query Fail with 'SQLSTATE[HY093]: Invalid parameter number'?. For more information, please follow other related articles on the PHP Chinese website!