Is mysqli_insert_id() gets record from whole serve

Question

I have mysql question that mysqli_insert_id() returns record from overall server or from specific user who has inserted that id? Because if it returns from overall then it won't be secure to use this function

Answer 1

@ZainFarooq Before executing the function mysqli_insert_id() there is something called mysqli_connect() function in the php to connect to the server and then select the database to fetch records.

In case of mysqli_insert_id() function yes it's completely safe to use and it returns the ID generated by a query on a table with a column having the AUTO_INCREMENT attribute. 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.

Answer 2

The comment from @daan above is flat-out wrong. insert_id() returns the ID placed into an auto_increment field of the last insert query that the connection executing the insert_id() query performed.

It does not return the largest ID in the table, it doesn't return the id created by some OTHER connection, it doesn't return the id created by some OTHER user.

It is literally the last ID YOU created by the LAST insert YOU performed.

That means it is 100% reliable to perform an insert, get the created ID via last_insert_id() and then use that ID in other queries to create parent/child records.

But note that insert_id() only ever returns ONE id value. If you do a multi-value insert, you still only get the ID from the last value set, e.g.

INSERT INTO sometable (x, y) VALUES (1,2), (2,3)

still performs two inserts internally, and you'd only get the id for the 2,3 tuple, not the 1,2.

As well, there's no memory for all previous queries:

INSERT ...   // query #1
INSERT ...   // query #2
SET id = last_insert_id();

will only get the ID created by query #2, and the ID from query #1 is "lost".