How to Get the Unique ID for the Last Inserted Row in MySQL/PHP

How to Get the Unique ID for the Last Inserted Row in MySQL/PHP

If you insert a new row in a table that contains AUTO_INCREMENT field, oftentimes you may be interested in getting the value of the auto incremented field. This is a very common scenario if the value of the auto incremented field acts as a foreign key in other tables. There are plenty examples. For example, if you have Orders table, it most likely contains auto incremeted Id field and OrderItems table that contains OrderId field. A common scenrario is to insert a row in the Orders table, obtain the value of the Id field and then use it for the OrderId field in the OrderItems table.

In MySQL and PHP this is extremely easy. All you need to do is to call the mysql_insert_id() function.

Many developers are worried about concurency issues. Suppose the current value of the auto incremented field is 1001. In other words, the next order id should be 1002 and so on. Further, suppose that User A and User B simultaneously place orders and thus insert records in the Orders table. You may wonder what mysql_insert_id() will return.

According to the MySQL documentation, the mysql_insert_id() function works on a per-connection basis. In other words, User A will get 1002 and User B will get 1003. So, in contrary to the false assumption everything will be fine.

For completeness of the tip, please note that you can also obtain the value of the auto incremented field by executing a SELECT LAST_INSERT_ID() statement with mysql_query() function and retrieving the value from the result set returned by the statement. However, in our opinion the mysql_insert_id() function is easier.

For a detailed description of the above mentioned function, please visit the MySQL documention page: http://cut.io/ELY5