PHP PDO 准备好的语句查询不更新记录
我在使用 PHP 的 PDO 对象准备更新语句和更新记录时遇到问题.我已经获取了原始 SQL 查询并在 phpMyAdmin 中运行它，参数被传递给函数的值替换.它按预期更新记录.但是，从脚本运行时，它不会更新.它抛出零错误并返回 00000 的 errorInfo() 回复，据我所知，这是 PDO 表示一切正常的方式.我知道 PDO 对象有效，因为它成功地从数据库中插入和选择记录，包括我试图更新的记录.我知道这个更新功能很丑，我只是在学习 PDO.
显然，这是用 PHP5 编码的，使用 PDO.
类函数:
公共函数更新($tbl_name, $where = null, $what = null){if(is_array($where)){$where_str = '哪里';foreach($where as $key => $val){$where_str .= "{$key} = ':{$key}' 和 ";}$where_str = substr($where_str,0,-5);$what_str = '设置';foreach($what as $key => $val){$what_str .= "`{$key}` = ':{$key}', ";}$what_str = substr($what_str,0,-2);$query_str = "更新 {$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) 继续；$bind = $stmt->bindValue(":{$key}",$val);echo ($bind ? 'true' : 'false')." :{$key}=",$val,'<br/>';}foreach($where as $key => $val){if('date_time' === $key) 继续；$bind = $stmt->bindValue(":{$key}",$val);echo ($bind ? 'true' : 'false')." :{$key} ",$val,'<br/>';}}别的{返回假；}$this->dbh->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);$exec = $stmt->execute();echo 'exec: '.($exec === true ? 'true:' : 'false:').':'.$exec.'<br/>';echo '';$stmt->debugDumpParams();echo '</pre>';返回 $stmt->errorInfo();}
从会话更新/登录脚本调用:
$where = array('id' =>$user['id'],);$what = 数组('twitter_key' =>$oauth_token,'twitter_secret' =>$oauth_token_secret);$update = $db->update('users', $where, $what);类函数和调用者中 echos 和 print_r 的输出:
//print_r($stmt = $this->dbh->prepare($query_str)) 输出:PDO 语句对象([查询字符串] =>更新用户设置`twitter_key` = ':twitter_key', `twitter_secret` = ':twitter_secret' where id = ':id' LIMIT 1;)//bing 参数的输出和执行返回真:twitter_key=XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX真:twitter_secret=XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX真:ID 20执行:真:1//$stmt->debugDumpParams() 输出:SQL: [111] 更新用户设置`twitter_key` = ':twitter_key', `twitter_secret` = ':twitter_secret' where id = ':id' LIMIT 1;参数:3密钥:名称:[12] :twitter_key参数=-1name=[12] ":twitter_key"is_param=1参数类型=2键: 名称: [15] :twitter_secret参数=-1name=[15] ":twitter_secret"is_param=1参数类型=2键:名称:[3]:id参数=-1名称=[3] ":id"is_param=1参数类型=2//print_r($stmt->errorInfo()) 输出:大批([0] =>00000)我对 PDO 了解不多，但我的感觉是你绑定参数的方式有问题.但是，确定的最简单方法是查看实际查询.
根据文档，您应该能够在 $stmt->queryString 中看到生成的查询，因为它转到 SQL.现在无法看到，因为您正在将参数绑定到语句 after 输出 $stmt.
在绑定参数之后(或者甚至在执行查询之后，我不知道)执行 print_r() .你应该得到真正的查询字符串，并找到问题的根源.
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.
Obviously, this is coded in PHP5, using PDO.
Class Function:
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);
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
)
解决方案
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.
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.
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.
相关文章