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.

CRUD Operations in PHP Using MySQLAssumption

  1. You must have basic knowledge of PHP, HTML, CSS and JavaScript. You can read below topics for more details:
    1. How to install xampp for Windows with setup for PHP development
    2. How to write PHP code with examples for beginners

CRUD application using PHP and MYSQLFolders and Files

Below is a screenshot of folder structure and files I am using:

CRUD operation in PHP

I have created a folder called 'crud' under 'htdocs' as I am using XAMPP. If you are using WAMP, create 'crud' folder under 'www'.

Folder 'cfg' - In this folder I have kept dbconnect.php which is used to connect to the MySQL database.
Folder 'css' - Custom stylesheet is in this folder
Folder 'js' - For custom JavaScripts

index.php is for the Registration form and it can display the list of students
edit_student.php is to update student details.
del_student.php is to delete the student 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:

Crud in php and mysql example

Table has 5 columns.

  1. id - it is the primary key and auto incremented
  2. first_name - First name of the student
  3. last_name - Last name of the student
  4. phone - Phone number of the student
  5. 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.

students.sql

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.

dbconnect.php

We are using mysqli_connect() function which needs 4 parameters.

  1. server - in our case it is localhost
  2. userid - we are using root user
  3. password - no password for the root user
  4. 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

The form has First Name, Last Name, Phone No and School and a submit button. Let's look at the code below:

validateForm() JavaScript function is called to validate phone number on onSubmit event. Below is the code for validateForm() function. It is in js/validation.js.

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:

index.php

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.

style.css

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

crud operation in php source code

Below is the final code for edit_student.php

edit_student.php

Delete Student

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

simple crud operations in php using mysqlTest 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:

php crud operation using mysqli

Enter some data and submit, details will be listed below in the html table as below:

crud operations in php code

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.

crud operations in php using mysqliImportant Note

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 for CRUD in PHP and MySQLDownload 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.

CRUD Operation in MySQL Using PHPConclusion

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.