問題描述

我正在更新表中的一行，并嘗試按照 這個答案.

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

• 看看桌子，
• 找到第一個狀態=0，
• 更新行，
• 返回更新行的id

我對計數的興趣為零，因為查詢已將 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 查詢中獲取結果?的文章就介紹到這了，希望我們推薦的答案對大家有所幫助，也希望大家多多支持html5模板網！

【網站聲明】本站部分內容來源于互聯網,旨在幫助大家更快的解決問題，如果有圖片或者內容侵犯了您的權益，請聯系我們刪除處理，感謝您的支持！