我正在更新表中的一行,并尝试按照 这个答案.
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();
}
但我总是收到以下错误
SQLSTATE[HY000]:一般错误
SQLSTATE[HY000]: General error
根据 $stmt->fetchAll()-general-error-when-updating-database?#answer-12980031">这个答案.如果我去掉那行,该行会适当更新.
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 运行多查询语句(多语句查询!?),并从 SELECT
中获取结果?
So, how do I run the multi-query statement (multi-statement query!?) using PDO, and obtain the results from the SELECT
?
编辑 1
我不需要需要更新行的计数.我需要该行的实际 ID.
I DO NOT need the count of the rows updated. I need the actual ID of the row.
表架构
id | someCol | status
----- | ------- | ------
1 | 123 | 0
2 | 456 | 0
3 | 789 | 0
4 | 012 | 0
我对计数的兴趣为零,因为查询已将 LIMIT 1
硬编码到其中.
The count is of zero interest to me, as the query has LIMIT 1
hard-coded into it.
直线的整个点
count($stmt->fetchAll());
是通过/失败条件.
if(count ==1){
... do something with the returned id ...
}else{
... do something else ...
}
编辑 2
显然,这个问题很容易通过两个单独的查询来解决.我更愿意在一个查询中使用它.既是一种偏好,也是学习的机会.
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
作为单独的查询进行 -- 您不能将多个查询放在一个语句中.这样做:
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();
}
这篇关于PHP、MySQL、PDO - 从 UPDATE 查询中获取结果?的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持跟版网!