9.4.8     Reading auto-incrementing values: mysql_insert_id()

This is NOT the latest copy of this book; click here for the latest version.

In the table specification for dogbreeds, the ID field was an INT AUTO_INCREMENT PRIMARY KEY. This means that MySQL will automatically assign increasingly higher integers to the ID field for us as INSERT queries are sent - but how can we tell what numbers it is using?

There are two ways to read the last-used auto-increment value: using a query, or calling a function.

The query option relies on the special MAX() function of MySQL. As MySQL will assign increasingly higher numbers to the ID field, the way to find the most recently assigned number is to run code like this:

The alternative is to use the function mysql_insert_id(), which will return the last ID auto-inserted by the current connection. There is a subtle difference there, and one that makes it important enough for you to learn both methods of retrieving auto-incrementing values.

The difference lies in the fact that mysql_insert_id() returns the last ID number that MySQL issued for this connection, regardless of what other connections are doing. Furthermore, mysql_insert_id() only stores one value - the last ID number that MySQL issued for this connection on any table. On the other hand, using the SQL query allows you to check the very latest ID that has been inserted, even if you have not run any queries or if it is been 20 minutes since your last query. Furthermore, you can use the query on any table you like, which makes it even more useful.

As both do roughly the same thing, you have your choice. It is faster, and quite frankly easier to use mysql_insert_id() if you just want to know the ID number that MySQL used for your last INSERT operation. Generally speaking you will find mysql_insert_id() is what you will use the most, but do try to keep the other method at least somewhere in the back of your head, because it is much more flexible than using mysql_insert_id().