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

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:

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.

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

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.

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 and PDO.

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.

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.

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.

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.

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

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.

Step 5 - Add CSS (style.css)

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

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.