CRUD Application in PHP and Ajax without Page Reload
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. 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.
Folders and Files
We will be using the below folder structure and files:
We have a folder named 'product' under 'xampp/htdocs'.
Folder 'cfg' - This folder is for dbconnect.php which is used to connect to the MySQL database. Folder 'css' - Our custom stylesheet is in this folder Folder 'js' - In this folder we have our custom JavaScript file index.php is the main program that displays a list of products and options to add, update and delete products. 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
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.
Let us see how we can develop the entire application.
We will create the table named 'product' in MySQL database. This table will have product details, like name, price and stock, etc. The table structure is given below:
The table has 4 columns.
product_id - It is the unique product_id (primary key) and auto incremented
product_name - Name of the product
price - Price of the product
stock - Available stock
product.sql
CREATE TABLE `product` (
`product_id` int(11) NOT NULL,
`product_name` varchar(200) NOT NULL,
`price` decimal(12,2) NOT NULL,
`stock` int(11) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
INSERT INTO `product` (`product_id`, `product_name`, `price`, `stock`) VALUES
(1, 'Sofa', '10000.00', 34),
(2, 'Dining Table', '6000.00', 100),
(3, 'Dining Chair(set of 4)', '5000.00', 30),
(4, 'Mattress', '8000.00', 111);
ALTER TABLE `product`
ADD PRIMARY KEY (`product_id`);
ALTER TABLE `product`
MODIFY `product_id` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=5;
Connect to MySQL database (dbconnect.php)
We will use the below PHP code to connect to the database. We will include this in other programs to connect to the database.
In index.php, we have an add (Create)form, an update (Update) form and an html table to show the list of existing products (Read). Saving data in the database for add and update forms, is done by a separate PHP program executed using an Ajax request. An overview of index.php is given below:
Show a list of products in an html table.
For each product displayed, show edit and delete links. For updating the product, call the same program (index.php) with the product id and a flag for editing as parameters. This will show the Update form. Call a JavaScript function to delete the product using Ajax.
If the edit flag is set, show the Update Product form
When no flag is set, show add product form
Add, update and delete products are done using individual PHP programs executed using Ajax call.
Display Product List (Read operation)
Let me show the code for displaying list of products in index.php.
It is a simple html table where we display details of each product in a for loop after fetching the data from the database. Let's see the code.
In the last column, we are using an Action to update and delete a product. For delete, a JavaScript function delProduct() is used.
We will check in index.php, if the edit flag is set, we will display the Update form. If no flag is set, then we will show the Add form. See the code below:
Note the save button in line 44. On the Onclick event of this button, we call a JavaScript function updateProduct(). This function will use Ajax to update the product details in the database. It is defined in product.js and we will explain it soon.
When the Edit Flag is not available, we display the Add Product form, see line 48. We call addProduct() function to add a product using Ajax. Below is the complete code of index.php.
We have used three JavaScript functions - addProduct(), updateProduct() and delProduct(). For each of these functions, we have a corresponding PHP programs - add_product.php, update_product.php and delete_product.php. These PHP programs will be executed using an Ajax request.
Function addProduct()
This function is called when the user clicks on the Add button while adding a new product.
function addProduct() {
var p_name = $('#p_name').val();
var price = $('#price').val();
var stock = $('#stock').val();
if (!validateData(p_name,price,stock))
return false;
$.ajax({
url:"add_product.php",
method:"POST",
data:{p_name:p_name, price:price,stock:stock},
dataType:"text",
success:function(response) {
$("#products").html(response);
}
});
}
url: add_product.php - this will take the form values and insert into product table. Also, it will refresh the list of products.
type: "POST" - we are using post method.
data: {p_name:p_name, price:price,stock:stock} - send the form values.
dataType:"text" - response from Ajax 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 the product does not exist already, it will insert a row in the product table with the details of the product. It then selects all products from the product table and displays them in an html table.
This function is called when the user clicks on the Save button from the update form. It will validate the form and use Ajax to execute update_product.php. Let's see the code for this function.
function updateProduct() {
var product_id = $('#product_id').val();
var p_name = $('#p_name').val();
var price = $('#price').val();
var stock = $('#stock').val();
if (!validateData(p_name,price,stock))
return false;
if ($("#frm").data("changed")) {
$.ajax({
url:"update_product.php",
method:"POST",
data:{product_id:product_id, p_name:p_name, price:price,stock:stock},
dataType:"text",
success:function(response) {
$("#products").html(response);
}
});
}
else {
alert("No changes to save");
return false;
}
}
If form data is changed, we are executing Ajax in line 10.
url: update_product.php - this takes the form values and updates the product table.
type: "POST" - we are using post method.
data: {product_id:product_id, p_name:p_name, price:price,stock:stock} - we are sending the form data. Note that product_id is the hidden field in the update form.
dataType:"text" - Response from Ajax will be in text format.
Now, let's see the code of update_product.php. It gets the input values and checks if another product with the same name already exists in the database. If another product with the same name does not exist already, it will update the product table with the details of the product.
This function is called when the user clicks on the delete link from the list of products. This will take the input parameters product_id and product_name.
function delProduct(product_id, product_name){
if (confirm("Are you sure you want to delete product - "+product_name+"?")){
$.ajax({
url:"delete_product.php",
method:"POST",
data:{product_id:product_id},
dataType:"text",
success:function(response) {
$("#products").html(response);
}
});
}
}
Now, let's see the code of delete_product.php. It works the same way as adding and updating a product. It takes the input parameter product_id and deletes the product from the product table.
validateData() function is called by addProduct() and updateProduct() functions in product.js. This function is used to validate the form.
function validateData(p_name,p_price,p_stock){
if (p_name.trim() =="") {
$("#product_err").text("Product Name must have a value");
return false;
}
if (p_price.trim() == "") p_price = 0;
if(p_price <= 0)
{
$("#price_err").html("Enter a positive value for price");
$("#price").focus();
return false;
}
if (p_stock.trim() == "") {
$("#stock_err").html("Stock should be zero or positive");
$("#stock").focus();
return false;
}
if (p_stock <0) {
$("#stock_err").html("Stock should be zero or positive");
$("#stock").focus();
return false;
}
return true;
}
Add CSS (style.css)
Let us add below styles. We have already added style.css in index.php.
Make sure in your XAMPP control panel Apache and MySQL services are running. Run localhost/product in the browser. You will see the home page displayed below:
Add a new product, update an existing product and delete a product. Verify the data in the database table.
Download Source Code
Download the source code by clicking on the download button below:
Conclusion
In this example, we have shown how you can develop a CRUD application (add/update/delete) using PHP and Ajax so that you do not need to 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 web page. In such cases, Ajax can help us.
Folders and Files
Test the Application
Download Source Code
Conclusion
Post a Comment