MySQL has an AUTO_INCREMENT column attribute which automatically generates a consistent integer value upon a successful (or not) INSERT. Oftentimes, this attribute is set on a column deemed the PRIMARY KEY. Therefore, providing a unique value for the column and ensuring each rows’ individual identity. The LAST_INSERT_ID() function returns the most recently generated value for a column with the AUTO_INCREMENT attribute. Many times, you use this value further in query processing (E.g., link a newly signed-on customers’ information to a joining table of orders, ensure referential integrity between parent and child tables using a FOREIGN KEY and PRIMARY KEY, INSERT into another related one-to-one table, etc…). The PDO PHP library has a like-named class method, lastInsertId(), which provides the same functionality as LAST_INSERT_ID() in a PHP context. In this post, I’ll visit lastInsertId() with a simple example. Continue reading to learn more…
Self-Promotion:
If you enjoy the content written here, by all means, share this blog and your favorite post(s) with others who may benefit from or like it as well. Since coffee is my favorite drink, you can even buy me one if you would like!
Vendor-specific Auto Incrementing Types
Most RDBMS support some form of an auto-incrementing column attribute, type, or keyword. Below are many of the common implementations at the time of writing:
- SQL Server – IDENTITY
- MySQL – AUTO_INCREMENT
- Oracle SQL – IDENTITY
- PostgreSQL – SERIAL
- MariaDB – AUTO_INCREMENT
What is PDO?
PDO is an abbreviation for PHP Data Objects and is an interface for working with Databases in PHP. PDO is database-agnostic, unlike mysqli which only works with the MySQL Database, and can interact with a number of databases.
Table Structure
Suppose we have this ‘user_details’ table with 3 columns: ‘id’, ‘first_name’, and ‘last_name’.
Let’s add some data to the ‘user_details’ table with this INSERT statement:
1 2 3 | INSERT INTO `user_details`(`id`, `first_name`, `last_name`) VALUES (NULL,'John','Doe'), (NULL, 'Jane', 'Doe'); |
Table ‘user_details’ now has the data we just inserted:
The next value for the ‘id’ column should be 3, so let’s use PDO and lastInsertId() to capture it.
Connection Information
In order to interact with the database, we need a connection to it in PHP. Here we use PDO and establish a connection for query operations:
1 2 3 | // connection information $pdo = new PDO('mysql:host=localhost;dbname=some_db_name;charset=utf8', 'some_user_name', 'some_password'); $pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION); |
Data to store
For the purpose of this post, we will assume we have collected this information from a form or other source:
1 2 3 | // Assume the 'user input' values have been sanitized and validated. $first_name = 'Josh'; $last_name = 'Otwell'; |
Note: Always be sure user-supplied values are validated and sanitized (not shown in the example above).
Prepared Statement and INSERT
Since we are introducing user-captured data into the database, we need to use prepared statement(s) to mitigate potential SQL Injection attacks:
1 2 3 4 5 6 7 | // prepare insert statement $insert_sql = "INSERT INTO `user_details`(`first_name`, `last_name`) VALUES(:first_name, :last_name)"; $insert_stmt = $pdo->prepare($insert_sql); $insert_stmt->bindValue(':first_name', $first_name); $insert_stmt->bindValue(':last_name', $last_name); $insert_stmt->execute(); |
Use lastInsertId() and retrieve recent AUTO_INCREMENT value
1 2 | $last_insert_id = $pdo->lastInsertId(); echo 'Last Insert ID: '.$last_insert_id; |
Complete Code using general best practices
I have attached the completed code of the last_insert_id.php script below with some best practices using:
try/catchblock.beginTransaction(),commit(), androllbackJ()PDO methods for transaction isolation.if/elseblock for conditional checking.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 | <!--?php try { // connection information $pdo = new PDO('mysql:host=localhost;dbname=some_db_name;charset=utf8', 'some_user_name', 'some_password'); $pdo--->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION); // begin a transaction $pdo->beginTransaction(); // Assume the 'user input' values have been sanitized and validated. $first_name = 'Josh'; $last_name = 'Otwell'; // prepare insert statement $insert_sql = "INSERT INTO `user_details`(`first_name`, `last_name`) VALUES(:first_name, :last_name)"; $insert_stmt = $pdo->prepare($insert_sql); $insert_stmt->bindValue(':first_name', $first_name); $insert_stmt->bindValue(':last_name', $last_name); // try the insert, if something goes wrong, rollback. if ($insert_stmt->execute() === FALSE) { $pdo->rollback(); echo 'Unable to insert data'; } else { $last_insert_id = $pdo->lastInsertId(); echo 'Last Insert ID: '.$last_insert_id; $pdo->commit(); } } catch (PDOException $e) { echo 'Database Error '.$e->getMessage().' in '.$e->getFile(). ': '.$e->getLine(); } |
Important: It is generally best practice to log any errors to log file and not echo these errors to the browser. I have done so in the code above only for the purpose of this post should we get an error during execution.
Once executing the last_insert_id.php script, the browser echo‘s the lastInsertId() value:
1 | Last Insert ID: 3 |
We can also see the recently added row of data in the screen shot below.
I hope this high-level introduction to lastInsertId() is helpful and informative for you. If you have any questions or suggestions for best practices, please let me know in the comments below and thanks for reading!
Recommended Reading
Visit these informational resources for more information on the topic(s) covered in this post:
- The AUTO_INCREMENT column attribute in MySQL – A beginning perspective.
- MySQL Improved Extension
- PDO
- Oracle and PostgreSQL SQL Cheat Sheet
- PDO lastInsertId()
Like what you have read? See anything incorrect? Please comment below and thanks for reading!!!
A Call To Action!
Thank you for taking the time to read this post. I truly hope you discovered something interesting and enlightening. Please share your findings here, with someone else you know who would get the same value out of it as well.
Visit the Portfolio-Projects page to see blog post/technical writing I have completed for clients.
Have I mentioned how much I love a cup of coffee?!?!
To receive email notifications (Never Spam) from this blog (“Digital Owl’s Prose”) for the latest blog posts as they are published, please subscribe (of your own volition) by clicking the ‘Click To Subscribe!’ button in the sidebar on the homepage! (Feel free at any time to review the Digital Owl’s Prose Privacy Policy Page for any questions you may have about: email updates, opt-in, opt-out, contact forms, etc…)
Be sure and visit the “Best Of” page for a collection of my best blog posts.
Josh Otwell has a passion to study and grow as a SQL Developer and blogger. Other favorite activities find him with his nose buried in a good book, article, or the Linux command line. Among those, he shares a love of tabletop RPG games, reading fantasy novels, and spending time with his wife and two daughters.
Disclaimer: The examples presented in this post are hypothetical ideas of how to achieve similar types of results. They are not the utmost best solution(s). The majority, if not all, of the examples provided, is performed on a personal development/learning workstation-environment and should not be considered production quality or ready. Your particular goals and needs may vary. Use those practices that best benefit your needs and goals. Opinions are my own.
5 thoughts on “PHP PDO lastInsertId() method with examples in MySQL”