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

Unexpected Error in Multiple Insert Query

The code snippet attempts to prepare and execute an SQL query to insert multiple values into a table. However, the execution fails with the error "SQLSTATE[HY093]: Invalid parameter number: parameter was not defined."

Cause of the Error

The error occurs because the number of elements in the $values array does not match the number of elements in the $matches array. The SQL query expects a specific number of parameters, but the PHP code is providing different numbers.

Solution

To resolve the error, ensure that the $values and $matches arrays contain the same number of elements. This can be achieved by initializing the $values array before the loop.

Additionally, it is recommended to check if the hash column has a unique index. If it does not, the ON DUPLICATE KEY UPDATE clause may not work as expected.

Revised Code

$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 SQL INSERT Query Fail with 'SQLSTATE[HY093]: Invalid parameter number'?. For more information, please follow other related articles on the PHP Chinese website!