I use CodeIgniter 4 a great deal for learning, personal projects, and application development. In this post, I cover 2 different methods you can use and retrieve the MySQL Last Insert ID value after executing an INSERT statement in CodeIgniter. Continue reading for more information…
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!
Model Setup
Starting out, I use the CLI Generator feature and create a Model by executing this code in the terminal in the project root:
php spark make:model user --suffix
I have removed much of the boilerplate code, keeping this bare-bones UserModel. I will fill in more functionality as needed:
Creating a Home Controller users() method
Visiting the URL /cicrud/home/users, executes this users() method in the default Home Controller file:
Important! Assume the $data associative array has been validated and sanitized prior to being passed into the Model insert() method. Always validate and sanitize your data!
Are you a Medium member? If so, receive an email notification each time I publish a blog post if you prefer the Medium platform. Not a member? No worries! Use my sign-up link (I will get a commission at no extra cost to you) and join. I really enjoy reading all the great content there and I know you will too!!!
Checking the data in the user’s table, we can see the row has been added:
There are times as a Developer you need to capture the Last Insert ID value from a MySQL table column defined with the AUTO_INCREMENT attribute. MySQL has a native LAST_INSERT_ID() information function you can use in queries and retrieve this value.
If you are using CodeIgniter 4, there are a couple of different methods you can leverage, depending on how you are inserting the data, allowing you to retrieve the Last Insert ID value. I’ll cover them both in this post.
CodeIgniter 4 Model getInsertID() method
CodeIgniter 4 Models have built-in methods to handle most of the CRUD operations you need in an application.
I’ve written about the CodeIgniter 4 Model insert() method in this Medium post, CodeIgniter 4 CRUD Series with MySQL: Create.
If you have inserted a new row using the Model insert() method and need the Last Insert ID value, how can you get it?
You can call the Model getInsertID() method on your current Model instance and retrieve the value.
As of the time of writing, I haven’t found the official documentation on this method. For the purposes of this blog post, I am piggybacking off of this post I found on Stackoverflow. Feel free to provide relevant resources or links in the comments below for this CodeIgniter 4 method.
See the updated users() method below and the code:
Revisiting the user’s table shows the value as well:
The Newsletter for PHP and MySQL Developers
Receive a copy of my ebook, “10 MySQL Tips For Everyone”, absolutely free when you subscribe to the OpenLampTech newsletter.
CodeIgniter 4 Query Builder insertID()
In the UserModel Model file, I have created this dataInsert() method:
Using the Query Builder Class, we can INSERT and retrieve the Last Insert ID value, leveraging the built-in $builder insertID() method, which is called on the $db connection instance.
In the Home Controller, I have edited the users() method to use the UserModel dataInsert() method shown in this code snippet
| public function users() { | |
| $user_model = new UserModel(); | |
| $data = [ | |
| 'first_name' => 'Jane', | |
| 'last_name' => 'Doe', | |
| 'dob' => '2021-01-01' | |
| ]; | |
| $last_insert_id = $user_model->dataInsert($data); | |
| echo 'Last Insert ID is: '. $last_insert_id; | |
| } |
Again, visiting the URL, /cicrud/home/users, executes the users() Home Controller method. However, in this instance, the Query Builder insertID() method retrieves the Last Insert ID value instead of the Model getInsertID() method.
And there you have it. Two ways to retrieve the Last Insert ID value in CodeIgniter 4. If you know of any others, please share them in the comments below so that I and other readers know of them as well. Thank you!
Similar Last Insert ID blog posts
I have written several blog posts about MySQL Last Insert ID, covering a couple of different environments/languages, and want to share them below with any readers who are interested. Please share them along as well! Thank you!
- MySQL’s AUTO_INCREMENT Attribute
- MySQL Shell get_auto_increment_value() method – Python mode
- PHP PDO lastInsertId() method
- The AUTO_INCREMENT column attribute in MySQL – A beginning perspective
If you have any questions or see any mistakes in the code, please let me know via the comments. Constructive comments help me provide accurate blog posts and are much appreciated. Thank you for reading!
Thank you for reading this post. Please share it with someone else who would enjoy it as well.
Josh Otwell has a passion to grow as a PHP Developer, SQL expert, and technical blogger/writer.
Disclaimer: The majority of examples in this post, are performed in a personal development/learning workstation environment and should not be considered production quality or ready. Your particular goals and needs may vary. Like always, just because you can do something doesn’t mean you should. My opinions are my own.
2 thoughts on “How to Retrieve MySQL Last Insert ID in CodeIgniter 4”