How safe is to use mysqli_insert_id() and if not what should I use?

I have a very simple question I want to know the id of the last query that I inserted in my MySql DB.

It’s safe and efficient to use mysqli_insert_id() and if not what should I use?

Answer

Yes, it’s the recommended way to retrieve an ID value generated by an insert when using auto-increment. But it has limitations, which are documented. Did you read the documentation?

The mysqli_insert_id() function returns the same value that would be returned by the builtin SQL function LAST_INSERT_ID().

Note that it only works in the same session after you insert and generate an ID. You can’t get the last ID generated in a previous session.

So for example it doesn’t work at all in phpMyAdmin, because the “shared nothing” nature of PHP is that every query executes in a different session.

It also only works if your primary key is an auto-incrementing key, and if your last insert used the auto-increment mechanism. Even if your table has an auto-increment, you can override that by specifying a value. Then the last insert id is meaningless. Demonstration:

mysql> create table t (id serial primary key);

mysql> insert into t set id = 42;

mysql> select last_insert_id();
+------------------+
| last_insert_id() |
+------------------+
|                0 |
+------------------+

Why does this return 0? Because my insert did not generate an ID, it just used the ID value I specified in the insert.

It only returns the latest ID generated. If you do another subsequent insert that generates an ID, it’ll return the more recent generated ID.

It doesn’t tell you which table the ID was generated by. It’s up to you to know which table you most recently inserted into, which used auto-increment to generate an ID.

So in conclusion, yes, it’s the right function to use, but you do need to understand how it works and you need to know how to use it.