0

Need an extra set of eyes - I have the following database tables:

id - Primary, Auto-Increment

user_id - Unique

Query:

$db->executeQuery("
     INSERT INTO unsubcontact 
     (user_id,date_added) 

              VALUES

     (:userId,:dateAdded)

     ON DUPLICATE KEY UPDATE                                            
     date_added = :dateAdded
");

Gets this error:

Fatal error: Uncaught PDOException: SQLSTATE[23000]: Integrity constraint violation: 1062 Duplicate entry '1' for key 'UNIQ_xxxx

And '1' is the userId / unique key.

Why isn't the row being updated opposed to generating this error?

Michael Berkowski
  • 267,341
  • 46
  • 444
  • 390
necrodeus
  • 134
  • 1
  • 12
  • 1
    The query, in isolation, should work fine. But can you edit this to include the definition of the executeQuery()` method and the real context in which it is called? I would assume it wraps `prepare()/execute()` but I notice as you have it here, it doesn't pass & bind any parameters so that may be incorrect. – Michael Berkowski Feb 14 '17 at 00:50
  • Also, does the query work when you run it outside of PHP, directly in a MySQL client? – Michael Berkowski Feb 14 '17 at 00:50
  • Can you provide the full error? (especially the `UNIQ_xxxx` part) – Koala Yeung Feb 14 '17 at 01:10
  • Also, what is the input binded to `:userId`? – Koala Yeung Feb 14 '17 at 01:12
  • Side-remark: with PDO, you can't have two parameters with the same name. In your particular case, you should use [VALUES()](https://dev.mysql.com/doc/refman/5.7/en/miscellaneous-functions.html#function_values) such as `INSERT INTO unsubcontact (user_id, date_added) VALUES (:userId, :dateAdded) ON DUPLICATE KEY UPDATE date_added = VALUES(dateAdded)`. – Mikey Feb 14 '17 at 01:13
  • Why is that? Even if the data is the same values? Thank you @Michael Berkowski - operator error. – necrodeus Feb 14 '17 at 16:03
  • @necrodeus If you have PDO's `ATTR_EMULATE_PREPARES` enabled (which is not usually desirable) it will allow you to use the same param name more than once, but if it's disabled, you must provide a unique name for each parameter. See also http://stackoverflow.com/questions/10113562/pdo-mysql-use-pdoattr-emulate-prepares-or-not – Michael Berkowski Feb 14 '17 at 16:09
  • Also, you said "operator error". Does that mean you've solved your issue? – Michael Berkowski Feb 14 '17 at 16:11
  • Correct, thank you Michael. I don't know how to accept it as solved without an answer. If you can repost I can mark it? – necrodeus Feb 14 '17 at 16:25

0 Answers0