PHP PDO准备语句查询未更新记录 [英] PHP PDO Prepared statement query not updating record

问题描述

使用PHP的PDO对象准备更新语句并更新记录时遇到问题.我已经接受了原始SQL查询，并在phpMyAdmin中运行了它，并将参数替换为传递给函数的参数值.哪个将按预期更新记录.但是，从脚本运行时不会更新.它抛出零个错误，并返回00000的errorInfo()答复，据我所知，这是PDO所说的一切都很好的方式.我知道PDO对象起作用是因为它成功地从数据库中插入并选择了记录，包括我要更新的记录.我了解此更新功能很丑陋，我只是在学习PDO.

I am having a problem using PHP's PDO object to prepare an update statement and updating the record. I have taken the raw SQL query and ran it in phpMyAdmin with the params replaced by their values that are passed to the function. Which updates the record as intended. However, when ran from the script it does not update. It throws zero errors and it returns an errorInfo() reply of 00000, which to my understanding is PDO's way of saying all is well. I know the PDO object works because it successfully inserts and selects records from the database, including the one I am trying to update. I understand this update function is ugly, I am just learning PDO.

很明显，这是使用PDO在PHP5中编码的.

Obviously, this is coded in PHP5, using PDO.

类功能:

public function update($tbl_name, $where = null, $what = null)
    {
        if(is_array($where))
        {
            $where_str = 'where ';
            foreach($where as $key => $val)
            {
                $where_str .= "{$key} = ':{$key}' and ";
            }
            $where_str = substr($where_str,0,-5);

            $what_str = 'set ';
            foreach($what as $key => $val)
            {
                $what_str .= "`{$key}` = ':{$key}', ";
            }
            $what_str = substr($what_str,0,-2);

            $query_str = "update {$tbl_name} {$what_str} {$where_str} LIMIT 1;";
            $stmt = $this->dbh->prepare($query_str);
            echo '<pre>'.print_r($stmt, true).'</pre>';
            foreach($what as $key => $val)
            {
                if('date_time' === $key) continue;
                $bind = $stmt->bindValue(":{$key}",$val);
                echo ($bind ? 'true' : 'false')." :{$key}=",$val,'<br/>';
            }
            foreach($where as $key => $val)
            {
                if('date_time' === $key) continue;
                $bind = $stmt->bindValue(":{$key}",$val);
                echo ($bind ? 'true' : 'false')." :{$key} ",$val,'<br/>';
            }
        }else{
            return false;
        }
        $this->dbh->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
        $exec = $stmt->execute();
        echo 'exec: '.($exec === true ? 'true:' : 'false:').':'.$exec.'<br/>';

        echo '<pre>';
        $stmt->debugDumpParams();
        echo '</pre>';

        return $stmt->errorInfo();
    }

通过会话更新/登录脚本调用:

Called from session update/login script:

$where = array(
    'id' => $user['id'],
    );
$what = array(
    'twitter_key'    => $oauth_token,
    'twitter_secret' => $oauth_token_secret
    );

$update = $db->update('users', $where, $what);

类函数和调用者中echos和print_r的输出:

Output from echos and print_r in class function and caller:

// print_r($stmt = $this->dbh->prepare($query_str)) output:
PDOStatement Object
(
    [queryString] => update users set `twitter_key` = ':twitter_key', `twitter_secret`     = ':twitter_secret' where id = ':id' LIMIT 1;
)

// output from the bing params and execution returns
true :twitter_key=XXXXXXXXXXXXXXXXXXXXXXXXXXXX
true :twitter_secret=XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX
true :id 20
exec: true:1

// $stmt->debugDumpParams() output: 
SQL: [111] update users set `twitter_key` = ':twitter_key', `twitter_secret` = ':twitter_secret' where id = ':id' LIMIT 1;
Params:  3
Key: Name: [12] :twitter_key
paramno=-1
name=[12] ":twitter_key"
is_param=1
param_type=2
Key: Name: [15] :twitter_secret
paramno=-1
name=[15] ":twitter_secret"
is_param=1
param_type=2
Key: Name: [3] :id
paramno=-1
name=[3] ":id"
is_param=1
param_type=2

// print_r($stmt->errorInfo()) output:
Array
(
    [0] => 00000
)

推荐答案

我对PDO不太了解，但是我感觉绑定参数的方式有问题.但是，确定的最简单方法是查看实际查询.

I don't know much about PDO, but my feeling is there is something wrong with the way you bind the parameters. However, the easiest way to tell for sure is to see the actual query.

根据 文档 ，您应该能够在$stmt->queryString中看到生成的查询到SQL时的情况.现在无法看到，因为您正在将参数绑定到语句 之后，然后输出$stmt.

According to the docs, you should be able to see the generated query as it went to SQL in $stmt->queryString. It's not possible to see right now because you are binding the parameters to the statement after you are outputting $stmt.

在绑定参数后执行print_r()(或者甚至在执行查询后，我也不知道).您应该获取真实的查询字符串，然后深入问题的底部.

Do a print_r() after you bind the parameters (or maybe even after execution of the query, I don't know). You should get the real query string, and get to the bottom of the problem.

这篇关于PHP PDO准备语句查询未更新记录的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！