Select the last affected rows

Question

I have updated my records based on specific condition after that I want to know the ids from the affected rows.

$sql = mysqli_query("update table set xxx='".$x."' where yyy='".$y."'");

Now after executing this query I want to know the affected rows.

The number is available in `mysqli_affected_rows`, the rows (and id's) themselves you would have to select in another query. — jeroen, Apr 21 '15 at 08:45
Side note: you seem to be fighting against syntactic sugar. How about `'UPDATE foo SET xxx=?, yyy=?'`? Even `"UPDATE foo SET xxx='$x', yyy='$y'"` is more readable. — Álvaro González, Apr 21 '15 at 08:51
if nothing else works, you can always send a `select * from table where yyy=...` before the update. — lp_, Apr 21 '15 at 08:55
As your where column is unchanged in update so you can use the same where clause to get the id's .`"Select id from table where yyy='".$y."'"` — Kamran, Apr 21 '15 at 08:55
@ÁlvaroG.Vicario: It is not a dupe of that question, because this query could update multiple records—whereas the question you have closed against asks only about a single record. — eggyal, Apr 21 '15 at 08:58
@eggyal - I think it's pointless to copy here all the answers that explain how to extend it to several IDs but I don't feel like arguing. — Álvaro González, Apr 21 '15 at 09:01
@ÁlvaroG.Vicario: I browsed the answers to that question but couldn't see a single one that explains how to do that :( — eggyal, Apr 21 '15 at 09:04
Please check [the second part of this answer](http://stackoverflow.com/a/1751282/13508) to a similar question (How to get ID of the last updated row in MySQL?). It's an ugly hack but... :) — Álvaro González, Apr 21 '15 at 09:09

score 1 · Answer 1 · answered Feb 13 '16 at 14:08

1

Simple yet effective

$last_id = mysqli_insert_id($conn);

http://www.w3schools.com/php/php_mysql_insert_lastid.asp

answered Feb 13 '16 at 14:08

kayleighsdaddy

670
5
15

score -1 · Answer 2 · answered Apr 21 '15 at 08:59

You must first fetch the IDs, and then perform the update. If concurrency is a concern, you can use a locking read (provided that your table is stored in a transactional engine, such as InnoDB):

$mysqli->autocommit(FALSE);

$select = $mysqli->prepare('SELECT id FROM table WHERE yyy = ? FOR UPDATE');
$select->bind_param('s', $y);
$select->execute();

$update = $mysqli->prepare('UPDATE table SET xxx = ? WHERE yyy = ?');
$update->bind_param('ss', $x, $y);
$update->execute();

$mysqli->commit();

// here are the IDs that were updated
$select->bind_result($id);
while ($select->fetch()) printf('Updated id: %s\n', $id);

score -1 · Answer 3 · answered Apr 21 '15 at 08:59

The only way I can think of is to first sleect rows that would be updated with the update statement, those are:

$updatableIds = mysqli_query("SELECT id FROM table WHERE xxx !='".$x."' AND yyy='".$y."'");

we add xxx !='".$x."' because if value of xxx already was $x those rows would not be affected.

Next you run the update

$sql = mysqli_query("update table set xxx='".$x."' where yyy='".$y."'");

Pankaj katiyar · Answer 4 · 2015-04-21T10:02:21.513

UPDATE users
SET type = '3'
WHERE type = '2';

To find out the last affected row right after the statement, it should be slightly updated as follows:

UPDATE users
SET type = '3',
    user_id=LAST_INSERT_ID(user_id)
WHERE type = '2';

// use function

 function updateAndGetId($value)
   {
       $query ="UPDATE users
    SET type = '$value',
        user_id=LAST_INSERT_ID(user_id)
    WHERE type = '2'";
      mysql_query($query)
    return mysql_insert_id();
   }
   $lastUpdatedRow = updateAndGetId(3);

In case you want to update only really changed row, add a conditional update of the user_id through the LAST_INSERT_ID and check if the data is going to change in the row.

Select the last affected rows

4 Answers4