Invalid parameter number, PDO on duplicate update fails

mysql, pdo, php

Solution

A @CertaiN mentions, with real prepares, non emulated ones, you can't bind the same variable multiple times by the same name. You could opt for binding it with another name, but this query does not need it. The `VALUES()` function returns the value that would have been inserted, which would make this query:

    $sql = 'INSERT INTO users_meta
         (user_id,meta_key,meta_value)
       VALUES (:user_id,:meta_key,:meta_value)
       ON DUPLICATE KEY
       UPDATE meta_value = VALUES(meta_value)';

And can be used for more columns if needed, like so:

    $sql = 'INSERT INTO users_meta
         (user_id,meta_key,meta_value)
       VALUES (:user_id,:meta_key,:meta_value)
       ON DUPLICATE KEY
       UPDATE 
         meta_value = VALUES(meta_value),
         meta_key = VALUES(meta_key)';

Problem

I created following function in users class: ``` public function update_usermeta($user_id,$user_profile) { $sql = 'INSERT INTO users_meta (user_id,meta_key,meta_value) VALUES (:user_id,:meta_key,:meta_value) ON DUPLICATE KEY UPDATE meta_value = :meta_value'; foreach ($user_profile as $meta_key => $meta_value) { if ($meta_value == null OR $meta_value == "") {continue;} if ($meta_key == "identifier" OR $meta_key == "photoURL" OR $meta_key == "displayName" OR $meta_key == "email") {continue;} $params = array( ':meta_key' => $meta_key, ':meta_value' => $meta_value, ':user_id' => $user_id ); $this->mysql_execute_query($sql,$params); } } ``` Insert works fine, but I've problem with ON DUPLICATE part, it errors out with: ``` SQLSTATE[HY093]: Invalid parameter number ``` Obviously problem is that I've more parameters, than update requires. How do I resolve this situation?

Original source