int mysqli_insert_id ( resource link_identifier)
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:
mysqli_query($db, "SELECT ID AS MAX(ID) FROM dogbreeds;");
The alternative is to use the function mysqli_insert_id(), which will return the last ID auto-inserted by the database connection you pass to it as its only parameter – i.e., the return value from mysqli_connect(). 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 mysqli_insert_id() returns the last ID number that MySQL issued for this connection, regardless of what other connections are doing. Furthermore, mysqli_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 mysqli_insert_id() if you just want to know the ID number that MySQL used for your last INSERT operation. Generally speaking you will find mysqli_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 mysqli_insert_id().
Want to learn PHP 7?
Hacking with PHP has been fully updated for PHP 7, and is now available as a downloadable PDF. Get over 1200 pages of hands-on PHP learning today!
If this was helpful, please take a moment to tell others about Hacking with PHP by tweeting about it!
Next chapter: Unbuffered queries for large data sets: MYSQLI_USE_RESULT >>
Previous chapter: Advanced formatting
Jump to:
Home: Table of Contents
Copyright ©2015 Paul Hudson. Follow me: @twostraws.