問題描述
我正在更新表中的一行,并嘗試按照 這個(gè)答案.
I am updating a row in a table, and trying to return the updated row, as per this SO answer.
我的代碼如下:
$sql = "SET @update_id := '';
UPDATE testing SET status='1', id=(SELECT @update_id:=id)
WHERE status='0' LIMIT 1;
SELECT @update_id;";
$db->beginTransaction();
try{
$stmt = $db->prepare($sql);
$stmt->execute();
echo count($stmt->fetchAll());
$db->commit();
}catch(Exception $e){
echo $e->getMessage();
exit();
}
但我總是收到以下錯(cuò)誤
SQLSTATE[HY000]:一般錯(cuò)誤
SQLSTATE[HY000]: General error
根據(jù) $stmt->fetchAll()-general-error-when-updating-database?#answer-12980031">這個(gè)答案.如果我去掉那行,該行會(huì)適當(dāng)更新.
Which seems to be due to the $stmt->fetchAll()
, according to this SO answer. If I take that line out, the row is updated appropriately.
那么,如何使用 PDO 運(yùn)行多查詢語句(多語句查詢!?),并從 SELECT
中獲取結(jié)果?
So, how do I run the multi-query statement (multi-statement query!?) using PDO, and obtain the results from the SELECT
?
編輯 1
我不需要需要更新行的計(jì)數(shù).我需要該行的實(shí)際 ID.
I DO NOT need the count of the rows updated. I need the actual ID of the row.
表架構(gòu)
id | someCol | status
----- | ------- | ------
1 | 123 | 0
2 | 456 | 0
3 | 789 | 0
4 | 012 | 0
- 看看桌子,
- 找到第一個(gè)狀態(tài)=0,
- 更新行,
- 返回更新行的id
我對(duì)計(jì)數(shù)的興趣為零,因?yàn)椴樵円褜?LIMIT 1
硬編碼到其中.
The count is of zero interest to me, as the query has LIMIT 1
hard-coded into it.
直線的整個(gè)點(diǎn)
count($stmt->fetchAll());
是通過/失敗條件.
if(count ==1){
... do something with the returned id ...
}else{
... do something else ...
}
編輯 2
顯然,這個(gè)問題很容易通過兩個(gè)單獨(dú)的查詢來解決.我更愿意在一個(gè)查詢中使用它.既是一種偏好,也是學(xué)習(xí)的機(jī)會(huì).
Obviously this issue is simple to get around with two separate queries. I would prefer to have this in one single query. Both a preference, as well as an opportunity to learn.
推薦答案
您需要將 SELECT @update_id
作為單獨(dú)的查詢進(jìn)行 -- 您不能將多個(gè)查詢放在一個(gè)語句中.這樣做:
You need to do the SELECT @update_id
as a separate query -- you can't put multiple queries in a single statement. So do:
$sql = "SET @update_id := '';
UPDATE testing SET status='1', id=(SELECT @update_id:=id)
WHERE status='0' LIMIT 1";
try{
$db->beginTransaction();
$db->query($sql); // no need for prepare/execute since there are no parameters
$stmt = $db->query("SELECT @update_id");
$row = $stmt->fetch(PDO::FETCH_ASSOC);
$id = $row['@update_id'];
$db->commit();
} catch (Exception $e) {
echo $e->getMessage();
$db->rollBack();
exit();
}
這篇關(guān)于PHP、MySQL、PDO - 從 UPDATE 查詢中獲取結(jié)果?的文章就介紹到這了,希望我們推薦的答案對(duì)大家有所幫助,也希望大家多多支持html5模板網(wǎng)!