[SOLVED] Error when preparing a multiple insert query

Error when preparing a multiple insert query

// 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);

The code above fails with the following error

SQLSTATE[HY093]: Invalid parameter number: parameter was not defined

Although when count($matches) == count($values) just before execute is called?

What is going on here?

Solution

This error you are receiving is because the number of elements in $values & $matches does not match.

If $values & $matches do not contain the same number of elements then the insert will fail, due to the query expecting X params but it is receiving Y data $matches. In your case, $values probably already contains some values, which is the reason for the count mismatch. To avoid that, you must always initialize an array before the loop.

I believe you will also need to ensure the column hash has a unique index on it as well.

$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);