問題描述
我在使用 PHP 的 PDO 對象準備更新語句和更新記錄時遇到問題.我已經獲取了原始 SQL 查詢并在 phpMyAdmin 中運行它，參數被傳遞給函數的值替換.它按預期更新記錄.但是，從腳本運行時，它不會更新.它拋出零錯誤并返回 00000 的 errorInfo() 回復，據我所知，這是 PDO 表示一切正常的方式.我知道 PDO 對象有效，因為它成功地從數據庫中插入和選擇記錄，包括我試圖更新的記錄.我知道這個更新功能很丑，我只是在學習 PDO.
顯然，這是用 PHP5 編碼的，使用 PDO.
類函數:
公共函數更新($tbl_name, $where = null, $what = null){if(is_array($w??here)){$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.
這篇關于PHP PDO 準備好的語句查詢不更新記錄的文章就介紹到這了，希望我們推薦的答案對大家有所幫助，也希望大家多多支持html5模板網！