mysqli_insert_id: What if someone inserts another row just before I call this?

Question

My question is a rather simple one. I've read that the recommended method of retrieving the auto_increment/id value of a row I've inserted in mysqli is the mysqli_insert_id() function. However, I'm not too familiar and had a question: (This is all theoretical at this point)

For these purposes (hence the mysqli bit) this is all going to be from a PHP web application. Say multiple users are on the application at once and another row is inserted from a different page between the time that I insert my row and the time that I call mysqli_insert_id()?

Would that return an incorrect value, or does MySQL have some sort of feature to prevent such a thing? Or am I simply overestimating the possibility of such a scenario?

Thanks for your time.

Answer 1

mysqli_insert_id() is specific to the database connection -- it returns the ID of the row that this script invocation inserted most recently, not any other MySQL client. So there's no conflict if multiple applications are inserting into the database at the same time.

Answer 2

There are two ways in which you can invoke this method:

1. Object Oriented - $mysqli->insert_id;
2. Procedural (as you have specified) - mysqli_insert_id(mysqli $link)

In both cases the last insert id relates to the connection you have established with the database. For the purpose of explanation though I believe the Object Oriented approach provides greater clarity. Consider the following code:

$mysqli = new mysqli('host','username','password','database');
// Insert into table with an AUTO INCREMENT field defined`
if ($result = $mysqli->query("INSERT INTO table (somefield) VALUES ('somevalue');")) {
   echo 'The ID is: '.$mysqli->insert_id;
}

The $insert_id instance variable is updated whenever the query function is called. Therefore, the value held is only within the scope of your code and not relative to other interactions with the database by some other process.

There are some interesting caveats to consider though. For example, if you decide to do bulk inserts e.g.

$mysqli->query("INSERT INTO table (somefield) VALUES ('somevalue'), ('anothervalue');")

It will return the ID of the first row inserted and not the last.

For further reading PHP Doc provides more clarification.