title: PHP, MySQL, PDO - Get result from UPDATE query?
body: <p>I am updating a row in a table, and trying to return the updated row, as per <a href="https://stackoverflow.com/questions/1388025/how-to-get-id-of-the-last-updated-row-in-mysql?#answer-1751282">this SO answer</a>.</p>
  
  <p>My code is the following:</p>
  
  <pre><code>$sql = "SET @update_id := '';
          UPDATE testing SET status='1', id=(SELECT @update_id:=id)
          WHERE status='0' LIMIT 1; 
          SELECT @update_id;";
  
  $db-&gt;beginTransaction();
  try{
      $stmt = $db-&gt;prepare($sql);
      $stmt-&gt;execute();
  
      echo count($stmt-&gt;fetchAll());
  
      $db-&gt;commit();
  }catch(Exception $e){
      echo $e-&gt;getMessage();
      exit();
  }
  </code></pre>
  
  <p>But I always get the following error</p>
  
  <blockquote>
    <p>SQLSTATE[HY000]: General error</p>
  </blockquote>
  
  <p>Which seems to be due to the <code>$stmt-&gt;fetchAll()</code>, according to <a href="https://stackoverflow.com/questions/12979510/pdo-error-sqlstatehy000-general-error-when-updating-database?#answer-12980031">this SO answer</a>. If I take that line out, the row is updated appropriately.</p>
  
  <p>So, how do I run the multi-query statement (multi-statement query!?) using PDO, and obtain the results from the <code>SELECT</code>?</p>
  
  <p><strong>EDIT 1</strong></p>
  
  <p>I <em>DO NOT</em> need the <em>count</em> of the rows updated. I need the actual <em>ID</em> of the row.</p>
  
  <p>Table Schema</p>
  
  <pre><code>  id   |   someCol  |  status
   ----- |   -------  |  ------
     1   |     123    |    0
     2   |     456    |    0
     3   |     789    |    0
     4   |     012    |    0
  </code></pre>
  
  <ul>
  <li>Look at the table, </li>
  <li>find the first status=0, </li>
  <li>update the row, </li>
  <li>return the id of the row that was updated</li>
  </ul>
  
  <p>The count is of zero interest to me, as the query has <code>LIMIT 1</code> hard-coded into it.</p>
  
  <p>The whole point of the line </p>
  
  <pre><code>count($stmt-&gt;fetchAll());
  </code></pre>
  
  <p>Is a pass/fail condition. </p>
  
  <pre><code>if(count ==1){
      ... do something with the returned id ...
  }else{
      ... do something else ...
  }
  </code></pre>
  
  <p><strong>EDIT 2</strong></p>
  
  <p>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.</p>