CODEWITHSUNDEEP
phpmysqlsqlpdo
clzola
clzola

Reputation: 2025

Invalid parameter number for PDO statement

Next code gives me an error SQLSTATE[HY093]: Invalid parameter number

$sql = "INSERT INTO `users` (`id`, `date_install`, `date_ping`, `cc`, `uv`, `pid`, `pv`, `aff_id`, `sub_id`, `channel`, `cid`, `os`, `av`, `db`) VALUES (:id, :date_install, now(), :country, :updaterVersion, :productId, :productVersion, :affiliateId, :subId, :channel, :commandId, :os, :av, :defaultBrowser) "
     . "ON DUPLICATE KEY UPDATE `date_install` = :date_install, `date_ping` = now(), `cc` = :country, `uv` = :updaterVersion, `pid` = :productId, `pv` = :productVersion, `aff_id` = :affiliateId, `sub_id` = :subId, `channel` = :channel, `cid` = :commandId, `os` = :os, `av` = :av, `db` = :defaultBrowser ";

$statement = $database->prepare($sql);
$statement->bindValue(":id", $user->id, PDO::PARAM_INT);
$statement->bindValue(":date_install", $user->date_install, PDO::PARAM_STR);
$statement->bindValue(":country", $user->cc, PDO::PARAM_STR);
$statement->bindValue(":updaterVersion", $user->uv, PDO::PARAM_INT);
$statement->bindValue(":productId", $user->pid, PDO::PARAM_INT);
$statement->bindValue(":productVersion", $user->pv, PDO::PARAM_INT);
$statement->bindValue(":affiliateId", $user->aff_id, PDO::PARAM_INT);
$statement->bindValue(":subId", $user->sub_id, PDO::PARAM_INT);
$statement->bindValue(":channel", $user->channel, PDO::PARAM_STR);
$statement->bindValue(":commandId", $user->cid, PDO::PARAM_INT);
$statement->bindValue(":os", $user->os, PDO::PARAM_STR);
$statement->bindValue(":av", $user->av, PDO::PARAM_STR);
$statement->bindValue(":defaultBrowser", $user->db, PDO::PARAM_STR);

$statement->execute();

I have no idea what is wrong and if I copy/paste SQL into console and replace all values manually everything works. Also if I comment out part ON DUPLICATE KEY..., also works.

Upvotes: 0

Views: 86

Answers (3)

Bartosz Zasada
Bartosz Zasada

Reputation: 3900

In order to use the same parameter names twice, you must set PDO::ATTR_EMULATE_PREPARES attribute to true:

$database->setAttribute(PDO::ATTR_EMULATE_PREPARES, true);

Upvotes: 1

Your Common Sense
Your Common Sense

Reputation: 157872

If emulation mode is turned off for your PDO instance, you won't be able to use the same placeholder name more than once in the query.

Besides, for the ON DUPLICATE it is not necessary either, as you can always use the VALUES operator that will take the value from the VALUES clause:

ON DUPLICATE KEY UPDATE `date_install` = VALUES(date_install), ...

Upvotes: 3

martijn
martijn

Reputation: 80

PDO Doesn't allow repetition of variable names. Your ON DUPLICATE KEY UPDATE should have it's own variable names. Which also means you have to assign your variables 2 times. This is a sad limitation :(.

Upvotes: 1

Related Questions