php 判断update返回为0_PHP，MySQL，PDO-从UPDATE查询中获取结果？

I am updating a row in a table, and trying to return the updated row, as per this SO answer.

My code is the following:

$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]: 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.

So, how do I run the multi-query statement (multi-statement query!?) using PDO, and obtain the results from the SELECT?

EDIT 1

I DO NOT need the count of the rows updated. I need the actual ID of the row.

Table Schema

id | someCol | status

----- | ------- | ------

1 | 123 | 0

2 | 456 | 0

3 | 789 | 0

4 | 012 | 0

Look at the table,

find the first status=0,

update the row,

return the id of the row that was updated

The count is of zero interest to me, as the query has LIMIT 1 hard-coded into it.

The whole point of the line

count($stmt->fetchAll());

Is a pass/fail condition.

if(count ==1){

... do something with the returned id ...

}else{

... do something else ...

}

EDIT 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.

解决方案

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

}