Geek asks and answers

Invalid parameter number, PDO does not execute when duplicate updates

I created the following function in the user 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);
        }
}

The insert works fine, but I have a problem with the ON DUPLICATE part, it has an error:

SQLSTATE[HY093]: Invalid parameter number

Obviously, the problem is that I have more options than required for the update. How to resolve this situation?

+3
source share
2 answers

A @CertaiN mentions that it prepares non-emulated ones with real ones; you cannot bind the same variable several times with the same name. You can select a binding with a different name, but this request is not needed. The function VALUES()returns the value that would be inserted , which would make this request:

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

, , :

    $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)';
+2

More articles:


All Articles