PHP，MySQL，PDO-从UPDATE查询中获取结果?

问题描述：

我正在更新表中的一行，并尝试根据

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();
}

但是我总是收到以下错误

But I always get the following error

SQLSTATE [HY000]:一般错误

SQLSTATE[HY000]: General error

根据

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

我不需要需要更新的行的 count .我需要该行的实际 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

看桌子，
找到第一个状态= 0，
更新行，
返回已更新行的ID

Look at the table,
find the first status=0,
update the row,
return the id of the row that was updated

该计数对我来说是零利息，因为该查询已将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查询中获取结果?

相关推荐