If a database table has an auto-incremented unique id column, then after inserting a row in the table, you can get the id of last inserted row using
mysqli_insert_id() in PHP. See PHP code example below:
Here, a row is inserted in a table called 'application' and after that we are using
mysqli_insert_id() to get the id of the last inserted row.
In this topic, we will develop a simple application using which user can submit an application and after successful submission, application id will be displayed, this is nothing but the id of the last submitted application.
I have given a screenshot of the page below:
User can enter Name, Email id, Gender and Address and submit the application. Once Application is submitted, Application Id will be generated and displayed on the screen as below:
Folders and Files
I have created a folder named 'application' under 'xampp/htdocs' as I am using XAMPP. Below is a screenshot of folder structure and files I am using:
Folder 'cfg' - In this folder I have kept dbconnect.php which is used to connect to the MySQL database.
Folder 'css' - My custom stylesheet is in this folder
index.php - it is the Application form along with the PHP code to process the application.
Create a MySQL table
Let us create a table named 'application' in MySQL database. This table will have details of the application. I have created this table in 'demo' database. If you have an existing database other than 'demo' you can also use it. Just make sure same table does not exist already. Table structure is given below:
Table has 6 columns.
- id - it is the primary key and auto incremented
- name - Name of the Applicant
- email_id - Email Id of the Applicant
- gender - Applicant's Gender
- address -Address of the Applicant
- submit_dt - Date of submission
Create table script for this table is given below, you can use this code to create the table. You can also download it from download section later in this topic.
Connect to MySQL database (dbconnect.php)
Use below script to connect to the database. Note that we have this database connection php program in 'cfg' folder. This is written once and used in every program where database connection is needed. This will be easy for maintenance and also will enable reusability of the code.
If connection is successful then it will return true and false otherwise. This Boolean value is returned and stored in the $conn variable. We will include this
dbconnect.php in other php programs so that we do not need to write it again in the program. For detail database connection understanding please read topic How to Connect to MySQL database in PHP using mysqli_connect.
Develop a data entry form
We have a simple application form with 4 fields and a submit button.
Write PHP code to process submitted data and display Application No:
Now, we will write PHP code to capture form data and also store them in database. Finally, we will display the Application No of the submitted application. See below code:
If insert is successful, using
mysqli_insert_id($conn) to get the id of the inserted row and displaying it as the Application No. So user can see the Application Id of his/her submitted application for future reference.
So, my final index.php is given below:
I have added some simple styles which I have written in
style.css and kept under css folder. It is given below. You can always change the styles to make the page look better.
Test the Application
Make sure in XAMPP control panel Apache and MySQL services are running. Since all my codes are in xampp/htdocs/application, in the browser I will run localhost/application. Registration page will be displayed. it will show the screen as below:
After data is submitted, it will display the application No. (Id) as below: