How to develop a CRUD application in PHP using Ajax without page refresh

Using the CRUD application we can Insert (Create), Select (Read), Update and Delete data from the database. If you do not want to refresh the page while doing CRUD operation, you can develop this using Ajax. Using Ajax makes it easier sometimes because you can do these operations without disturbing other elements on the page.

In this topic, we will develop an application using which users can view the list of products, add new products, update and delete an existing product using Ajax with PHP and MySQL.

You can use it for Admin who can enter products or any items in the system.

create CRUD application using AJAX in phpAssumption

  1. You must have basic knowledge of PHP, HTML, CSS, JavaScript/jQuery.
  2. You must have basic understanding of Ajax. 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
    3. How to build a simple CRUD application in PHP and MySQL

make CRUD operations with AJAXFolders and Files

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

write CRUD operations in php and ajax

I have created a folder named 'product' under 'xampp/htdocs' as I am using XAMPP. If you are using WAMP, create 'product' folder under 'www'.

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
Folder 'js' - In this folder I have my custom JavaScripts
index.php is my main program that displays a list of products and options to add, update and delete product.
add_product.php, update_product.php and delete_product.php are the php programs which are executed using Ajax call.

Below is the screenshot of the output of index.php

CRUD Operation in PHP Using Ajax, Jquery

In the above screen, you can add a new product, you can also update/delete and existing product. Once a product is added, it will be included in the list. Similarly, if a product is updated, it will be reflected in the product list and when a product is deleted, it will disappear from the list. So, all operations of CRUD are used here using Ajax.

crud operation using php, mysqli and ajax/jquery

Let us see how we can develop the entire application.

Watch YouTube Video

Step 1 - Create a MySQL table for products

Let us create a table named 'product' in MySQL database. This table will have product details, like name, price and stock etc. I have a database called 'demo'. So 'product' 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 name does not exist already. Table structure is given below:

PHP CRUD Operations without Page Refresh ajax jQuery

Table has 4 columns.

  1. product_id - It is the unique product_id (primary key) and auto incremented
  2. product_name - Name of the product
  3. price - Price of the product
  4. stock - Stock available
Create table script for this table is given below, you can use this code to create the table. You can also download and get the scripts from Download section later in this topic.

product.sql

After you create the table, make sure data is inserted correctly, it should have below data in it:

insert update and delete in php with ajax jquery

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 easy for maintenance and also will enable reusability of the code.

dbconnect.php

I am 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 user root
  4. database name - demo in our case.
If connection is successful, 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.

Step 3 - Create Add/Update product forms (index.php)

In index.php, I have an add form, an update form and a html table to show the list of existing products. Saving data in the database for add and update forms, are done by separate PHP program which are executed using Ajax Call. I will discuss them one by one. I have given an overview of index.php below:

  1. Show a list of products in a html table.
  2. For each product displayed, show an edit link and a delete link. For updating the product, call same program (index.php) with product id and a flag for edit as parameters. This will show the update form. To delete a product, call a JavaScript function to delete product using Ajax.
  3. If edit flag is set, show update product form
  4. When no flag is set, show add product form
  5. Add, update and delete product are done using individual php programs which are executed using Ajax call.

Display Product List

Let me show the code for displaying list of products in index.php. I have given a screenshot of the list and then the code is given for this list to get a better idea about it.

PHP AJAX and MySQL crud

It is a simple html table where I am displaying details of each product in a for loop after fetching from the database. Let's see the code.

In the last column I am using Action for update and delete product. I am adding two links in line 27 and 28 for update and delete product. For delete, a JavaScript function delProduct() is used. See the code below:

Now, I will just check in index.php, if edit flag is set, I will select details of the product and display update form. If no flag is set, then I will show Add Product form. See the code below:

Note the save button in line 44. Here I have defined a button, on Onclick event of this button, I have called a JavaScript function updateProduct(). This function will use Ajax to update the product details in the database. It is defined in product.js and I will explain it in next step.

When Edit Flag is not available, I am displaying Add Product form, see line 48. Here also, I have called addProduct() function to add product using Ajax. Below is the complete code of index.php.

index.php

Step 4 - Write JavaScript/jQuery for Ajax call(product.js)

You have seen, I have used three JavaScript functions - addProduct(), updateProduct() and delProduct(). For each of these functions, I have written php programs - add_product.php, update_product.php and delete_product.php. These php programs will be executed using Ajax.

Function addProduct()

This function is called when user clicks on Add button while adding a new product.

  1. url: add_product.php - this will take the form values and insert into product table. Then gives a message successful or failure. Also, it will refresh the list of products.
  2. method: "POST" - I am using post method.
  3. data: {p_name:p_name, price:price,stock:stock} - here I am sending all the form field values.
  4. dataType:"text" - Response from Ajax call will be in text format.

Now, let's see the code of add_product.php. It simply takes the input values and checks if product name already exists in database. If product does not exist already, it will insert a row in product table with the details of the product. It will give success message if insert statements executes successfully or error message in case of failure. It then selects all products from product table and displays them in a html table.

add_product.php

Function updateProduct()

This function is called when user clicks on Save button in update form. This will take the values from the form fields- name, price and stock. It will validate them and use Ajax to execute update_product.php. Output of update_product.php will be displayed on the screen. Let's see the code for function updateProduct()

If form data is changed, I am executing Ajax in line 10.

  1. url: update_product.php - this takes the form values and updates the product table. Then gives a message for successful update or failure. Also, it will refresh the list of products.
  2. method: "POST" - I am using post method.
  3. data: {product_id:product_id, p_name:p_name, price:price,stock:stock} - here I am sending all the form field values. Note that product_id is the hidden field in the update form.
  4. dataType:"text" - Response from Ajax call will be in text format.

Now, let's see the code of update_product.php. It simply takes the input values and checks if another product with same name already exists in database. If another product with same name does not exists already, it will update product table with the details of the product.

update_product.php

Function delProduct(product_id, product_name)

This function is called when user clicks on delete link from the list of products. This will take the input parameters product_id and product_name. It will ask for confirmation before running Ajax to execute delete_product.php. A message will appear on the screen and product list will be refreshed. Below is the function delProduct()

Now, let's see the code of delete_product.php. It works the same way as add and update product. It takes the input parameter product_id and deletes the product from product table. Then it refreshes the product list. Below is the code for delete_product.php

delete_product.php

Function validateData()

Now, I need to show you the validateData() function which is called by addProduct() and updateProduct() functions in product.js. I have already mentioned that this function is used to validate the form field values, so here is the code:

So, here is my entire product.js file. You do not need to copy and paste, if you download, you will get all the program codes. See download section later in this topic.

product.js

Step 5 - Add CSS (style.css)

Let us add below styles. I have already added style.css in index.php.

style.css

These are simple styles. You can always add or change to make the screen look better. Keep this style.css file in your css folder.

insert, update and delete in php with ajax jqueryTest the Application

Make sure in your XAMPP control panel Apache and MySQL services are running. Open the browser and run localhost/product. You will see the home page displayed as below:

insert, update, delete and view data without refresh using php, mysql and ajax

Test add new product, update existing product and delete a product. Also test if form field validation is working as expected. Try to add a product which already exists and see if you get the error message. Also, verify if data is properly added, updated or deleted in the database table.

Hope you could understand all the steps and you could test it successfully.

crud operation using php, mysqli and ajax/jqueryImportant Note

I have used Fontawesome icons for INR (currency symbol), edit and delete, that is why I used Fontawesome library from CDN in index.php. If you do not want to use icons, you can remove Fontawesome library from index.php.

Watch YouTube Video

download source code for crud form without refreshing in webpage using the jquery and ajaxDownload 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 php and ajaxConclusion

In this example I have showed how you can develop a CRUD application (add/update/delete) using PHP and Ajax so that you do not refresh the entire web page. There could be cases where you do not want to submit a form, but you want to refresh a part of the screen. In such cases Ajax gives an opportunity to refresh a section of the page. Example used is for products, you can use it for any other application as well.