CODEWITHSUNDEEP
phpmysql
user2178521
user2178521

Reputation: 843

ON DUPLICATE KEY UPDATE not working in PDO

INSERT INTO b (id, website...) 
VALUES (:id, :website...)
ON DUPLICATE KEY UPDATE  
website=:website ...

I have a MYSQL QUERY, I have SET id unique, why

website=:website ...

is not working, when I change to website="whatever" it works. anyone know why?

$job_B->bindValue(':website', $website, PDO::PARAM_STR);

Upvotes: 1

Views: 2503

Answers (3)

SupremeClicker
SupremeClicker

Reputation: 21

The use of VALUES() to refer to the new row and columns is deprecated beginning with MySQL 8.0.20, and is subject to removal in a future version of MySQL.

Upvotes: 2

Marc B
Marc B

Reputation: 360732

As a general tip, you shouldn't be "duplicating" inserted values when doing an ON DUPLICATE KEY. Mysql provides the VALUES() function for this purpose, e.g.

INSERT INTO foo (bar) VALUES (:baz)
ON DUPLICATE KEY UPDATE bar := :baz

can be better rewritten as

INSERT INTO foo (bar) VALUES (:baz)
ON DUPLICATE KEY UPDATE bar = VALUES(bar)
                              ^^^^^^^^^^^

VALUES() will re-use the value assigned to the specified field in the VALUES (...) section automatically, without requiring binding another variable into the query.

Upvotes: 6

Michael Berkowski
Michael Berkowski

Reputation: 270637

You have run into an unfortunate and misleading behavior of PDO's named parameters in a prepared statement. Despite assigning names, you cannot actually use a parameter more than once, as mentioned in the prepare() documentation:

You must include a unique parameter marker for each value you wish to pass in to the statement when you call PDOStatement::execute(). You cannot use a named parameter marker of the same name twice in a prepared statement. You cannot bind multiple values to a single named parameter in, for example, the IN() clause of an SQL statement.

This means you'll need to bind the parameter twice, with two different names, and consequently two different bindValue() calls:

$stmt = $pdo->prepare("
  INSERT INTO b (id, website...) 
  VALUES (:id, :website_insert...)
  ON DUPLICATE KEY UPDATE  
    website=:website_update ...
");

// Later, bind for each
$job_B->bindValue(':id', ...);
// Same value twice...
$job_B->bindValue(':website_insert', $website, PDO::PARAM_STR);
$job_B->bindValue(':website_update', $website, PDO::PARAM_STR);

Upvotes: 4

Related Questions