mysqli->insert_id on update (PHP)

Question

Does it work for anyone? :P

I can properly get insert_id while inserting, but not on update. Of course contactsId column is AUTO_INCREMENT.

Whole code:

<?php
$mysqli = new mysqli('localhost', [USER], [PASSWORD], [DB]);
$mysqli->set_charset("utf8");

$query = 'INSERT INTO contacts (contactsName) VALUES ("Mariola")';
$result = $mysqli->query($query);
echo $mysqli->insert_id . '<br />';

$query = 'UPDATE contacts SET contactsName = "Mariola" WHERE contactsId = 289';
$result = $mysqli->query($query);
echo $mysqli->insert_id;

Output:

1514
0

I HAVE record with id 289, and update works fine.

Answer 1

From MySQL documentation on LAST_INSERT_ID():

---

If expr is given as an argument to LAST_INSERT_ID(), the value of the argument is returned by the function and is remembered as the next value to be returned by LAST_INSERT_ID(). This can be used to simulate sequences:

Create a table to hold the sequence counter and initialize it:

mysql> CREATE TABLE sequence (id INT NOT NULL);
mysql> INSERT INTO sequence VALUES (0);

Use the table to generate sequence numbers like this:

mysql> UPDATE sequence SET id=LAST_INSERT_ID(id+1);
mysql> SELECT LAST_INSERT_ID();

The UPDATE statement increments the sequence counter and causes the next call to LAST_INSERT_ID() to return the updated value. The SELECT statement retrieves that value. The mysql_insert_id() C API function can also be used to get the value. See Section 20.6.7.37, “mysql_insert_id()”.

---

Maybe something like this will work:

$query = 'UPDATE contacts SET id = LAST_INSERT_ID(id), contactsName = "Mariola" WHERE contactsId = 289';

Answer 2

This behavior is described very clear in the document.

mysqli::$insert_id -- mysqli_insert_id — Returns the auto generated id used in the last query

If the last query wasn't an INSERT or UPDATE statement or if the modified table does not have a column with the AUTO_INCREMENT attribute, this function will return zero.