How to get ID of updated row?

Question

I would like to know the ID of the updated row and I tried this:

$sql = $db->prepare("UPDATE `timeslots` SET `service` = ?  WHERE `status` = ?");
$sql->bind_param("is", 0, "open");
$sql->execute();
        
if ($sql->execute()) {
   echo "ID: ".$db->insert_id."<br />";
}

But the result is everytime this instead of the ID:

ID: 0
ID: 0

The last inserted id works only for inserts as far as I know, so it has a default value when no `INSERT` has been made. Maybe you should do a `SELECT` query before the update but you may expect to get multiple results so multiple ids — AymDev, Aug 06 '21 at 07:55
https://www.w3schools.com/php/php_mysql_insert_lastid.asp It should also work for update statements :/ — Trombone0904, Aug 06 '21 at 07:56
Maybe you should call `$sql->execute()` only once if that's true ? But I wouldn't trust w3schools, the [documentation](https://www.php.net/manual/en/mysqli-stmt.insert-id.php) (which is a bit short, yes) only talks about `INSERT` statements. — AymDev, Aug 06 '21 at 08:05
Which database are you using? In SQL Server you can do the following - ```UPDATE timeslots SET service = ? OUTPUT INSERTED.id WHERE status = ?``` and then fetch the ids using ```$result = $sql->fetchAll(\PDO::FETCH_COLUMN);``` — Sibi Kandathil, Aug 06 '21 at 08:11

score 1 · Accepted Answer · answered Aug 06 '21 at 21:36

The documentation for insert_id clearly states:

Returns the ID generated by an INSERT or UPDATE query on a table with a column having the AUTO_INCREMENT attribute.

Your query does not generate a new ID. You can't use $db->insert_id as there was no new ID reported by MySQL server.

You can trick MySQL into providing this value. Just reset the ID to the value that it had previously by regenerating it again.

$sql = $db->prepare("UPDATE `timeslots` 
    SET `service` = ?, Id=LAST_INSERT_ID(Id) 
    WHERE `status` = ?");

See How to get ID of the last updated row in MySQL?

How to get ID of updated row?

1 Answers1