-2

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.

Community
  • 1
  • 1
Birrel
  • 4,754
  • 6
  • 38
  • 74
  • are you trying to check if your query was successful here? – Funk Forty Niner Jun 06 '16 at 23:43
  • 2
    What's the point of trying to get count of updated rows if you're always updating only one row with `LIMIT 1` and `$stmt->execute()` will return true or false of operation. – TheDrot Jun 06 '16 at 23:54
  • @Fred-ii- Within the very first sentence of the question, "... and trying to return the updated row...". – Birrel Jun 07 '16 at 00:02
  • @TheDrot tIf the count is 1, then there would be a result to read, and I can proceed with that code. If the count is zero, then there is no result, and it can stop right there. – Birrel Jun 07 '16 at 00:03
  • answers are below then, take a peek ;-) – Funk Forty Niner Jun 07 '16 at 00:06

3 Answers3

6

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();
}
Barmar
  • 741,623
  • 53
  • 500
  • 612
  • If it's a "cannot be done" scenario, due to limitations of the PDO, then two queries will suffice. Much thanks. – Birrel Jun 07 '16 at 00:18
  • 1
    @Birrel Only mysqli has a method for doing multiple queries in a single call, `mysqli_multi_query`. But it's a PITA to use, and I've never seen a convincing argument for using it. – Barmar Jun 07 '16 at 00:20
  • Keep in mind that if this were part of another transaction, this would not work. MySQL doesn't support nested transactions. – AlxVallejo Jan 16 '17 at 03:13
  • @AlxVallejo That's true of any code that creates a transaction, it's not specific to this. His original code had the transaction, the question was just about how to execute two statements. – Barmar Jan 16 '17 at 04:29
4

You're right about getting the exception SQLSTATE[HY000] for $stmt->rowCount();

The problem is, you cannot fetch an UPDATE query because these queries simply don't return values. To circumvent this, use rowCount().

As written in the PHP documentation, PDOStatement::rowCount() returns the number of rows affected by a DELETE, INSERT, or UPDATE statement.

Check out this example.

<?php
/* Updating rows from the PICNIC table*/
$update = $dbh->prepare('UPDATE ... PICNIC');
$update->execute();

/* Return the number of rows affected */
   echo $updateCount = $update->rowCount();

?>
Harman
  • 346
  • 2
  • 6
  • Just noticed you have Limit set to 1. Do you intend to verify if the update query went through? – Harman Jun 07 '16 at 00:01
  • 1
    Review edited question. I am not concerned with the count, nor was that stated in the question. – Birrel Jun 07 '16 at 00:10
  • Just to add if you execute an update of an item with the value it already is, then rowcount will return 0 and not 1. Which was unexpected for me, I didn't expect it to compare before updating. – HeavyHead Mar 14 '22 at 22:56
1

It's failing on ->fetchAll() because an UPDATE query does not return any rows/data.

What you want to do, is check out PDO::rowCount(). This returns the count of how many rows have been affected by your query.

echo $stmt->rowCount();

This was posted assuming you're trying to check if your query was successful.

Darren
  • 13,050
  • 4
  • 41
  • 79