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

Question

My question is pretty simple. I read that the recommended method to get the auto_increment / id value of the row that I inserted in mysqli is the mysqli_insert_id() function. However, I am not very familiar and asked the question: (This is all theoretically at the moment)

For these purposes (hence the mysqli bit) it will all be from a PHP web application. Say that several users are in the application at the same time, and another row is inserted from another page between the time I insert my row and the time that I call mysqli_insert_id() ?

Could this return the wrong value, or does MySQL have some peculiarity to prevent such a thing? Or am I just overestimating the possibility of such a scenario?

Thank you for your time.

Answer 1

mysqli_insert_id() specific to connecting to the database - it returns the identifier of the row that this script call inserted recently, and not any other MySQL client. Thus, there is no conflict if several applications are inserted into the database at the same time.

Answer 2

There are two ways to call this method:

• Object Oriented - $mysqli->insert_id ;
• Procedural (as you indicated) - mysqli_insert_id(mysqli $link)

In both cases, the last insertion identifier refers to the connection you established with the database. For explanation, although I believe that the object-oriented approach provides more 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 instance variable $insert_id updated whenever the request function is called. Thus, the retained value is only within your code, and not in relation to other interactions with the database by any other process.

There are some interesting points to keep in mind. For example, if you decide to do bulk inserts, for example.

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

It will return the identifier of the first row inserted, not the last.

For further reading, PHP Doc provides more clarification.