0

11 Simple Steps to Create CRUD Application in PHP & MySQL

This is simple CRUD Application in PHP & MySQL. In this tutorial, you can learn CRUD operations in PHP & MySQL. CRUD stands for create, read, update and delete. Create means inserting data into database using INSERT SQL statement. Read means reading data from database using SELECT SQL statement. Update means updating records using UPDATE SQL query. Finally, Delete means deleting data from database using DELETE SQL statements.

Here I’m going to create four PHP files, each PHP file is for create, read, update, delete functions. And I’m creating a table with simple columns first_name, last_name, gender, age, email_id.

Using these four files, I’ll insert data into database (C), Reading data from database (R), Updating data in database (U), Deleting data from database (D).

And I’ll name these files in simple terminology

• C – Create : index.php – To insert data into database (INSERT SQL Query).
• R – Read : view.php – To read data from database (SELECT SQL Query).
• U – Update : upate.php – To update data in database (UPDATE SQL Query).
• D – Delete : delete.php – To delete data in database (DELETE SQL Query).

First of all, I’ll show you SQL queries. If you understand these queries, then it is easy for you to write CRUD application.

INSERT SQL Query

INSERT SQL Query is used to INSERT data into database simply by passing column names and data along with table name.

SELECT SQL Query

SELECT SQL Query is used to SELECT data from database by simply passing database table name. And we can filter results by using WHERE clause also. We can re-order them ascending, descending order.

UPDATE SQL Query

UPDATE SQL Query is used to UPDATE data in database by passing column names & values with WHERE clause identfying the row.

DELECT SQL Query

DELETE SQL Query is used to DELETE data from database by simply using WHERE clause to identify row.

1. Creating Database Table

2. Connecting to Database Table using connect.php file

3. Create index.php HTML Form

4. INSERT data into database

First include connect.php file in index.php file using require_once PHP function.

Then check POST super global using if condition and isset, empty PHP functions. Then assign form values to variables, while assigning these values to variables use mysqli_real_escape_string function to strip data from input fields.

Then after assigning these values into variables, write SQL query to insert data into database.

Execute this INSERT SQL Query using mysqli_query PHP Function. Then assign this to variable res.

Check this res variables with if condition, if the query is succesfully inserted then display success message in body sections or else display a failure message in body section. I’m using two variable fmsg (failure message) & smsg (success message).

Display these error messages inside body section, add this code above form opening tag.

5. Create view.php HTML File

6. Fetch data from datbase in view.php file

Include connect.php file in view.php file for connecting to database.

Next to read data from the database, we have to write SQL query that is SELECT SQL Query. Here I’m selecting all the rows from database using table name, I’m not limiting the number of rows to fetch. If you want you can use, but here I don’t have many rows.

Execute this SELECT SQL Query using mysqli_query PHP function, store this data res variable.

Again using while loop, we will fetch the data and echo this data in our HTML data table.

7. Create update.php HTML file

For update.php file, I’m going to use same HTML code as index.php file. Because, in UPDATE operation, we will get the data from database for specific row based on id. Then user will edits the value and submits the form, after that script will update the values in database.

In previous file view.php, I’ve already created links for edit.php & delete.php files also passing id values from the database. If anyone clicks on the links, these id’s will be passed on to next file that is edit.php or delete.php files.

8. Fetch data into HTML form from database

Next to fetch data into HTML form, we have to write SELECT SQL Query to get data from database based on the id that we are passing from view.php file.

And also to echo data from database, it is easy for input text fields. But, it is little bit tricky for radio buttons and also select list. I’m going to cover them in this step.

First select the data using id, that we got from GET super global.

Assign id to id variable using GET superglobal.

Write SELECT SQL Query based on id value.

Execute this SELECT SQL Query using mysqli_query PHP function. And assign it to res variable.

Covert this result to associative array using mysqli_fetch_assoc PHP function.

Print this data in input fields, below you can see the code of displaying first_name, last_name & email values in input fields.

To display First name input field value.

To display Last name input field value.

To display Email input field value.

To display Gender Radio Input Field value.
To check radio buttons based on value, we need to check the value using if condition. If the value is true, then we will select the radio button. Here is the code.

To display Age Select List Field value.
To select the select list value, we will use code same like radio buttons.

9. Update data in database

Next if user submits the form, after editing values. Then the data should be inserted into database. I’m going to write code for this purpose.

Check if post superglobal is set and not empty. If it’s true, then assign the values to variables.

Then write SQL query to update the data in database. Below is the UPDATE SQL Query.

Then execute this SQL query using mysqli_query PHP function. And store it in res variable.

Using res variable, we can check the UPDATE Query is successful or not. If the successfully updated then we will redirect user to view.php file. Otherwise, we will display an error message to user.

After that, we have to display this error message inside body section. Add this below line of code above form opening tag.

10. Adding Delete Popup window in view.php file

Before deleting record, I want to display a popup message after clicking on delete button. This popup message contains two buttons with some text. If the user clicks delete button record will be deleted from database.

Otherwise, if user clicks on cancel button, delete operation will be canceled.

We have to add this code in view.php file to display popup window.

Add this jQuery file in head section.

Then add this code in side our while loop after edit icon. Here I’m removing delete icon and making it as button.

11. Delete records from database using delete.php file

Next, after clicking delete button user will be recdirected to delete.php file with id in URL. We will delete recored in delete.php file.

If the record is successfully deleted then we will redirect user to view.php file. Otherwise, we will display an error message to user.

Connect to database using connect.php file, just include this file using require_once PHP function.

Assign id value to id variable from get superglobal.

Write SQL Query to delte data from database.

Execute this query using mysqli_query PHP function, store this res variable.

If the result is successful then we will redirect user to view.php file. Otherwise, we will display an error message to user.

Till now, we have successfully created CRUD application in PHP & MySQL. If you have any issues let me know through comment form below.

Complete Code of All Files

If you have any problem arranging above pieces of code, you can use this below complete code.
[sociallocker]

SQL code to create Database Table

index.php

view.php

update.php

delete.php

[/sociallocker] If you are getting any issues with above code, let me know through the comment form below.