How to fix SQLSTATE[HY093]: Invalid parameter number: parameter was not defined error when preparing a multiple insert query with PHP?

Spread the love

Sometimes, we want to fix SQLSTATE[HY093]: Invalid parameter number: parameter was not defined error when preparing a multiple insert query with PHP.

In this article, we’ll look at how to fix SQLSTATE[HY093]: Invalid parameter number: parameter was not defined error when preparing a multiple insert query with PHP.

How to fix SQLSTATE[HY093]: Invalid parameter number: parameter was not defined error when preparing a multiple insert query with PHP?

To fix SQLSTATE[HY093]: Invalid parameter number: parameter was not defined error when preparing a multiple insert query with PHP, we can imploded the values into the SQL string.

For instance, we write

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

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

to add the values we want to insert into the $values array.

Then we call implode to return a string with the $values entries separated by commas.

And then we put that into the $sql string.

Then we use prepare to create a prepared statement from the $sql string.

Finally we call execute to run the prepared statement.

Conclusion

To fix SQLSTATE[HY093]: Invalid parameter number: parameter was not defined error when preparing a multiple insert query with PHP, we can imploded the values into the SQL string.

Leave a Reply

Your email address will not be published.