ON DUPLICATE KEY UPDATE not working in PDO

Issue

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

Solution

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

Answered By – Michael Berkowski

This Answer collected from stackoverflow, is licensed under cc by-sa 2.5 , cc by-sa 3.0 and cc by-sa 4.0