How to build a simple CRUD application in PHP and MySQL
CRUD operations are very common in any web application. It basically inserts, updates and deletes rows from database. CRUD stands for Create, Read, Update and Delete.
In this topic, we will develop a simple CRUD application in PHP and MySQL. It will display a list of registered students in a HTML table. Using this application, student details can be created, updated and even students can be deleted from the database.
Step 1 - Create a MySQL table to store student details
Let us create a table named 'students' in MySQL database. This table will have student details. I have a database called 'demo'. So 'students' table will be created in this 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 5 columns.
id - it is the primary key and auto incremented
first_name - First name of the student
last_name - Last name of the student
phone - Phone number of the student
school - School name of the student
Create table script for this table is given below, you can use this code to create the table. You can also download from the download section later in this topic.
After you create the table, verify the structure of the table, if everything is correct .
Step 2 - 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 easier for maintenance and also will enable us to reuse the code.
We are using mysqli_connect() function which needs 4 parameters.
server - in our case it is localhost
userid - we are using root user
password - no password for the root user
database name - demo in our case.
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 using PHP.
Step 3 - Develop a student registration form
A simple form called Student Registration Form is ctreated with only four fields and a submit button. We are keeping this form very simple using minimum styles. You can always add more fields and add more styles to make it look better.
The form has First Name, Last Name, Phone No and School and a submit button. Let's look at the code below:
A regular expression is used to check if entered phone number matches with the allowed expression. It must contain all digits and length 10.
PHP code to process submitted data
See below code:
If phone number is already registered, it should not allow same phone number for another student. If it is a new phone number a row is inserted into 'students' table.
So, this was our C (Create or Insert) part of CRUD. Let us now see the R (Read or Select) part of it.
Display Registered Students
Once student is registered successfully, we want to display the student details in a HTML table just below the form. So, using a for loop we can display details in a simple html table. See below code:
Action column is to edit and delete students. For edit, we are calling edit_student.php with id as parameter. Similarly, for delete del_student.php is called with id as parameter. We will add the above code in index.php just after <form> tag is closed. So, the 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.
Step 4 - PHP Code to Update and Delete Student
Here we will see U (Update) and D (Delete) part of CRUD operation. For updating student details, you can click on Edit and for deleting student record you can click on Delete link given against each student row in the html table.
Edit Student details
Display the selected student details in a form after user clicks on edit button. User can edit any fields and submit. Let's see below code:
We are taking the input parameter value using $_REQUEST['id'] and then selecting the details from 'students' table. These values are displayed in the form.
When the form is submitted, below PHP code is used to process or update the student details.
Below is the update student form
Below is the final code for edit_student.php
For deleting a student, we will simply take the input parameter - id and then delete the row for the that id from 'students' table. Then redirect user to home page. See below code for del_student.php
Test the Application
Make sure in XAMPP control panel, Apache and MySQL services are running. In the browser, run localhost/crud. Registration page will be displayed. As no students are registered yet, it will show the screen as below:
Enter some data and submit, details will be listed below in the html table as below:
Test for Edit and Delete, you should be able to update student details and delete student also. You can also test by entering same phone no which is already registered and see error message is displayed.
I have created this simple form with minimum number of fields. Purpose was to explain how insert, update and delete work in PHP with MySQL. You can always add additional fields and modify accordingly.
Scope of Enhancement:
You can add email id of the student in the registration form and add validation for the email id. You have to check correct email id format and also check that email id does not exist already. You have to modify table structure, add email id field in the forms and modify PHP code accordingly.
Download Source Code
I have put all codes in a zip file. You can download it by clicking on the Download button below. You do not need to register yourself to download it. You can directly use the code or you can modify them as per your requirements.
In this example we have developed CRUD application in PHP and MySQL. This is a very simplae and basic PHP form I could create as I wanted to show some validation as well. You can always improve it. Hope it will be useful for you.