参数编号无效，重复更新时PDO失败 [英] Invalid parameter number, PDO on duplicate update fails

问题描述

我在用户类中创建了以下函数:

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

插入工作正常，但是我对ON DUPLICATE部分有问题，它的错误信息如下:

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?

推荐答案

一个@CertaiN提到，使用 real 编写非仿真变量，您不能通过相同的绑定多次绑定同一变量姓名.您可以选择将其与其他名称绑定，但是此查询不需要它. VALUES()函数返回将被插入的值，它将进行以下查询:

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

这篇关于参数编号无效，重复更新时PDO失败的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！