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.

You are not inserting a new row on an `UPDATE` query, so no insert_id — superphonic, Mar 12 '14 at 11:39
where are you seeing this output and can you be elaborate on ur question — user3004356, Mar 12 '14 at 11:39
I know that I'm not inserting row, but see documentation - http://pl1.php.net/mysqli_insert_id - this function should refer to UPDATE also. It's my own PHP script, I call it by browser and can see echoed values. — Line, Mar 12 '14 at 11:41
It's not logical problem, I can resolve it another way. It's technical. According to the documentation I want to use this function and I want it works as I expect. Or I want to know how should I use it with UPDATE statement, cause documentation suggest that I can. Or answer that I can't do this, which I will interpret as a bug - in docs or behavior. Because in my opinion they are not consistent. — Line, Mar 12 '14 at 12:12

score 1 · Answer 1 · answered Mar 12 '14 at 11:41

1

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.

answered Mar 12 '14 at 11:41

xdazz

158,678
38
247
274

1

OK, I read it. How should I use it with UPDATE? I wrote that I'm using UPDATE and column is AUTO_INCREMENT.OK, I read it. How should I use it with UPDATE? I wrote that I'm using UPDATE and column is AUTO_INCREMENT. Could You kindly explain whether You think that I'm not use INSERT/UPDATE or modified table doesn't have AUTO_INCREMENT column? Because they are the only documented cases when it should return 0. – Line Mar 12 '14 at 11:42
1

@Line You don't need to use it with UPDATE, you already know the id when you update, didn't you? – xdazz Mar 12 '14 at 11:44
1

No, because I'm updating by another field. I know that I can workaround this (e.g. by SELECT and then UPDATE) but I want to know if I can use it as described in documentation... And if this is a bug, or I don't understand something. – Line Mar 12 '14 at 11:48
2

"The mysqli_insert_id() function returns the ID generated by a query on a table with a column having the AUTO_INCREMENT attribute." - query, not only INSERT. Next sentence: "If the last query wasn't an INSERT or UPDATE statement..." - so it concerns UPDATEs. I want see exaple of this usage. – Line Mar 12 '14 at 12:09
1

@Line yes, it's a bug in documentation. As it was explained to you already, there is no sense in getting **insert** id from an update query. – Your Common Sense Mar 12 '14 at 12:14
1

If this is documented bug (where can we report such bugs?) I can accept in as answer, but I guess it's only Your opinion. I still see sense in such feature - I know that I have only one record with given value in column, I want to update it & know id. It's some kind of SELECT and UPDATE in one statement. – Line Mar 12 '14 at 12:28
1

Have a look at this: https://dev.mysql.com/doc/refman/5.0/en/information-functions.html#function_last-insert-id The part with the `UPDATE` statement isn't useful to you, but it might explain why the `mysqli` documentation says it's possible on `UPDATE` statements. – magnetronnie Mar 12 '14 at 12:34
1

I see that's mySQL problem/just behavior. Your suggestion helps me very much and now I understand how it works. Especially part from "If expr is given as an argument...", I tested example. @magnetronnie, please post You comment as answer to this question. – Line Mar 12 '14 at 13:31

score 0 · Accepted Answer · answered Mar 12 '14 at 14:19

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

Yeah! You rule :) I didn't thought about it (I resolved my problem it another way) after another's answers and comments, I just want to understand it. Thanks again. — Line, Mar 12 '14 at 14:38

mysqli->insert_id on update (PHP)

2 Answers2