phpmysqlmysqliinsert-id

mysqli->insert_id on update (PHP)

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.

Solution

  • 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';