If you have know Jquery Bootgrid Plugin is extremely accepted html table grid plugin and mainly developed for Bootstrap. So we can easily integrate this Bootgrid plugin with Bootstrap framework. In this post we will discussing on how can we do server side processing with Bootgrid plugin by using PHP Ajax. This post helps you to do server side operation like fetch or select of data from table with all Bootgrid plugin functionality like searching table data, pagination, column sorting and many more. We will also see how to add or insert data into mysql table by using PHP Ajax and load inserted data on Bootgrid plugin, how to edit or update Bootgrid table data by using PHP with Ajax and Bootstrap modal and lastly we will also learn how to remove or delete data from Bootgrid data and display available data on Web page under Bootgrid table.
Bootgrid is extremely well build jquery grid plugin that is used to change a simple HTML table into powerful grid with extra ordinary functionality like table column sorting, pagination, searching table data from server side, and insert, update and delete records features. We will learn following functionality of Bootgrid plugin by using PHP Mysql and Ajax.
- Listing data on Bootgrid using Ajax and PHP
- Bootgrid Searching Table data with Server side processing using PHP with Ajax
- Bootgrid Server Side Pagination
- Bootgrid table column sorting
- Add Edit and Delete Button under Bootgrid table
- Insert or Add data using Bootstrap modal with Ajax and PHP
- Edit or Update data using Bootstrap modal with Ajax and PHP
- Delete data with Confirmation message using Ajax with PHP
So, In this post we will simply combine two table data by using Inner join query and after this we have put certain condition if Bootgrid send request for searching of data then we have put search data query condition at server side, if Bootgrid plugin send request for sorting of data then at server side we have put condition for sorting of data and many more.
In Jquery Bootgrid plugin we can not fire jquery click event on button, so for this we have load Bootgrid constructor for this, after loading Bootgrid constructor we can fire click event on Edit and delete button and after this we have send ajax request for edit or delete of data at server side. So this way we have perform all Bootgrid operation at server side by using Ajax PHP.
Source Code
index.php
<?php
include("connection.php");
$query = "SELECT * FROM category";
$result = mysqli_query($connection, $query);
$output = '';
while($row = mysqli_fetch_array($result))
{
$output .= '<option value="'.$row["category_id"].'">'.$row["category_name"].'</option>';
}
?>
<html>
<head>
<title>Bootgrid Tutorial - Server Side Processing using Ajax PHP</title>
<link rel="stylesheet" href="https://maxcdn.bootstrapcdn.com/bootstrap/3.3.6/css/bootstrap.min.css" />
<link rel="stylesheet" href="https://cdnjs.cloudflare.com/ajax/libs/jquery-bootgrid/1.3.1/jquery.bootgrid.css" />
<script src="https://ajax.googleapis.com/ajax/libs/jquery/2.2.0/jquery.min.js"></script>
<script src="https://maxcdn.bootstrapcdn.com/bootstrap/3.3.6/js/bootstrap.min.js"></script>
<script src="https://cdnjs.cloudflare.com/ajax/libs/jquery-bootgrid/1.3.1/jquery.bootgrid.js"></script>
<style>
body
{
margin:0;
padding:0;
background-color:#f1f1f1;
}
.box
{
width:1270px;
padding:20px;
background-color:#fff;
border:1px solid #ccc;
border-radius:5px;
margin-top:25px;
}
</style>
</head>
<body>
<div class="container box">
<h1 align="center">Jquery Bootgrid Tutorial - Server Side Processing using Ajax PHP</h1>
<br />
<div align="right">
<button type="button" id="add_button" data-toggle="modal" data-target="#productModal" class="btn btn-info btn-lg">Add</button>
</div>
<div class="table-responsive">
<table id="product_data" class="table table-bordered table-striped">
<thead>
<tr>
<th data-column-id="product_id" data-type="numeric">ID</th>
<th data-column-id="product_name">Product Name</th>
<th data-column-id="category_name">Category</th>
<th data-column-id="product_price">Price</th>
<th data-column-id="commands" data-formatter="commands" data-sortable="false">Commands</th>
</tr>
</thead>
</table>
</div>
</body>
</html>
<script type="text/javascript" language="javascript" >
$(document).ready(function(){
$('#add_button').click(function(){
$('#product_form')[0].reset();
$('.modal-title').text("Add Product");
$('#action').val("Add");
$('#operation').val("Add");
});
var productTable = $('#product_data').bootgrid({
ajax: true,
rowSelect: true,
post: function()
{
return{
id: "b0df282a-0d67-40e5-8558-c9e93b7befed"
};
},
url: "fetch.php",
formatters: {
"commands": function(column, row)
{
return "<button type='button' class='btn btn-warning btn-xs update' data-row-id='"+row.product_id+"'>Edit</button>" +
" <button type='button' class='btn btn-danger btn-xs delete' data-row-id='"+row.product_id+"'>Delete</button>";
}
}
});
$(document).on('submit', '#product_form', function(event){
event.preventDefault();
var category_id = $('#category_id').val();
var product_name = $('#product_name').val();
var product_price = $('#product_price').val();
var form_data = $(this).serialize();
if(category_id != '' && product_name != '' && product_price != '')
{
$.ajax({
url:"insert.php",
method:"POST",
data:form_data,
success:function(data)
{
alert(data);
$('#product_form')[0].reset();
$('#productModal').modal('hide');
$('#product_data').bootgrid('reload');
}
});
}
else
{
alert("All Fields are Required");
}
});
$(document).on("loaded.rs.jquery.bootgrid", function()
{
productTable.find(".update").on("click", function(event)
{
var product_id = $(this).data("row-id");
$.ajax({
url:"fetch_single.php",
method:"POST",
data:{product_id:product_id},
dataType:"json",
success:function(data)
{
$('#productModal').modal('show');
$('#category_id').val(data.category_id);
$('#product_name').val(data.product_name);
$('#product_price').val(data.product_price);
$('.modal-title').text("Edit Product");
$('#product_id').val(product_id);
$('#action').val("Edit");
$('#operation').val("Edit");
}
});
});
});
$(document).on("loaded.rs.jquery.bootgrid", function()
{
productTable.find(".delete").on("click", function(event)
{
if(confirm("Are you sure you want to delete this?"))
{
var product_id = $(this).data("row-id");
$.ajax({
url:"delete.php",
method:"POST",
data:{product_id:product_id},
success:function(data)
{
alert(data);
$('#product_data').bootgrid('reload');
}
})
}
else{
return false;
}
});
});
});
</script>
<div id="productModal" class="modal fade">
<div class="modal-dialog">
<form method="post" id="product_form">
<div class="modal-content">
<div class="modal-header">
<button type="button" class="close" data-dismiss="modal">×</button>
<h4 class="modal-title">Add Product</h4>
</div>
<div class="modal-body">
<label>Select Category</label>
<select name="category_id" id="category_id" class="form-control">
<option value="">Select Category</option>
<?php echo $output; ?>
</select>
<br />
<label>Enter Product Name</label>
<input type="text" name="product_name" id="product_name" class="form-control" />
<br />
<label>Enter Product Price</label>
<input type="text" name="product_price" id="product_price" class="form-control" />
</div>
<div class="modal-footer">
<input type="hidden" name="product_id" id="product_id" />
<input type="hidden" name="operation" id="operation" />
<input type="submit" name="action" id="action" class="btn btn-success" value="Add" />
</div>
</div>
</form>
</div>
</div>
connection.php
<?php
//database.php
$connection = mysqli_connect("localhost", "root", "", "testing1");
?>
fetch.php
<?php
//fetch.php
include("connection.php");
$query = '';
$data = array();
$records_per_page = 10;
$start_from = 0;
$current_page_number = 0;
if(isset($_POST["rowCount"]))
{
$records_per_page = $_POST["rowCount"];
}
else
{
$records_per_page = 10;
}
if(isset($_POST["current"]))
{
$current_page_number = $_POST["current"];
}
else
{
$current_page_number = 1;
}
$start_from = ($current_page_number - 1) * $records_per_page;
$query .= "
SELECT
product.product_id,
category.category_name,
product.product_name,
product.product_price FROM product
INNER JOIN category
ON category.category_id = product.category_id ";
if(!empty($_POST["searchPhrase"]))
{
$query .= 'WHERE (product.product_id LIKE "%'.$_POST["searchPhrase"].'%" ';
$query .= 'OR category.category_name LIKE "%'.$_POST["searchPhrase"].'%" ';
$query .= 'OR product.product_name LIKE "%'.$_POST["searchPhrase"].'%" ';
$query .= 'OR product.product_price LIKE "%'.$_POST["searchPhrase"].'%" ) ';
}
$order_by = '';
if(isset($_POST["sort"]) && is_array($_POST["sort"]))
{
foreach($_POST["sort"] as $key => $value)
{
$order_by .= " $key $value, ";
}
}
else
{
$query .= 'ORDER BY product.product_id DESC ';
}
if($order_by != '')
{
$query .= ' ORDER BY ' . substr($order_by, 0, -2);
}
if($records_per_page != -1)
{
$query .= " LIMIT " . $start_from . ", " . $records_per_page;
}
//echo $query;
$result = mysqli_query($connection, $query);
while($row = mysqli_fetch_assoc($result))
{
$data[] = $row;
}
$query1 = "SELECT * FROM product";
$result1 = mysqli_query($connection, $query1);
$total_records = mysqli_num_rows($result1);
$output = array(
'current' => intval($_POST["current"]),
'rowCount' => 10,
'total' => intval($total_records),
'rows' => $data
);
echo json_encode($output);
?>
insert.php
<?php
//insert.php
include("connection.php");
if(isset($_POST["operation"]))
{
if($_POST["operation"] == "Add")
{
$category_id = mysqli_real_escape_string($connection, $_POST["category_id"]);
$product_name = mysqli_real_escape_string($connection, $_POST["product_name"]);
$product_price = mysqli_real_escape_string($connection, $_POST["product_price"]);
$query = "
INSERT INTO product(category_id, product_name, product_price)
VALUES ('".$category_id."', '".$product_name."', '".$product_price."')
";
if(mysqli_query($connection, $query))
{
echo 'Product Inserted';
}
}
if($_POST["operation"] == "Edit")
{
$category_id = mysqli_real_escape_string($connection, $_POST["category_id"]);
$product_name = mysqli_real_escape_string($connection, $_POST["product_name"]);
$product_price = mysqli_real_escape_string($connection, $_POST["product_price"]);
$query = "
UPDATE product
SET category_id = '".$category_id."',
product_name = '".$product_name."',
product_price = '".$product_price."'
WHERE product_id = '".$_POST["product_id"]."'
";
if(mysqli_query($connection, $query))
{
echo 'Product Updated';
}
}
}
?>
fetch_single.php
<?php
//fetch_single.php
include("connection.php");
if(isset($_POST["product_id"]))
{
//$output = array();
$query = "SELECT * FROM product WHERE product_id = '".$_POST["product_id"]."'";
$result = mysqli_query($connection, $query);
while($row = mysqli_fetch_array($result))
{
$output["category_id"] = $row["category_id"];
$output["product_name"] = $row["product_name"];
$output["product_price"] = $row["product_price"];
}
echo json_encode($output);
}
?>
delete.php
<?php
//delete.php
include("connection.php");
if(isset($_POST["product_id"]))
{
$query = "DELETE FROM product WHERE product_id = '".$_POST["product_id"]."'";
if(mysqli_query($connection, $query))
{
echo 'Data Deleted';
}
}
?>
Database
--
-- Database: `testing1`
--
-- --------------------------------------------------------
--
-- Table structure for table `category`
--
CREATE TABLE IF NOT EXISTS `category` (
`category_id` int(11) NOT NULL,
`category_name` varchar(250) NOT NULL
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=latin1;
--
-- Dumping data for table `category`
--
INSERT INTO `category` (`category_id`, `category_name`) VALUES
(1, 'Electronics & Computers'),
(2, 'Home, Garden & Tools'),
(3, 'Handmade');
-- --------------------------------------------------------
--
-- Table structure for table `product`
--
CREATE TABLE IF NOT EXISTS `product` (
`product_id` int(11) NOT NULL,
`category_id` int(11) NOT NULL,
`product_name` varchar(250) NOT NULL,
`product_price` varchar(30) NOT NULL,
`product_image` varchar(250) NOT NULL
) ENGINE=InnoDB AUTO_INCREMENT=14 DEFAULT CHARSET=latin1;
--
-- Dumping data for table `product`
--
INSERT INTO `product` (`product_id`, `category_id`, `product_name`, `product_price`, `product_image`) VALUES
(1, 1, 'ASUS Laptop 1500', '799.00', 'asus-laptop.jpg'),
(2, 1, 'Microsoft Surface Pro 3', '898.00', 'surface-pro.jpg'),
(3, 1, 'Samsung EVO 32GB', '12.00', 'samsung-sd-card.jpg'),
(4, 1, 'Desktop Hard Drive', '50.00', 'computer-hard-disk.jpg'),
(5, 1, 'External Hard Drive', '80.00', 'external-hard-disk.jpg'),
(6, 2, 'Crock-Pot Oval Slow Cooker', '34.00', 'crok-pot-cooker.jpg'),
(7, 2, 'Magic Blender System', '80.00', 'blender.jpg'),
(8, 2, 'Cordless Hand Vacuum', '40.00', 'vaccum-cleaner.jpg'),
(9, 2, 'Dishwasher Detergent', '15.00', 'detergent-powder.jpg'),
(10, 2, 'Essential Oil Diffuser', '20.00', 'unpower-difuser.jpg'),
(11, 3, 'Medical Personalized', '11.00', 'hand-bag.jpg'),
(12, 3, 'Best Bridle Leather Belt', '64.00', 'mens-belt.jpg'),
(13, 3, 'HANDMADE Bow set', '24.00', 'pastal-colors.jpg');
-- --------------------------------------------------------
--
-- Indexes for table `category`
--
ALTER TABLE `category`
ADD PRIMARY KEY (`category_id`);
--
-- Indexes for table `product`
--
ALTER TABLE `product`
ADD PRIMARY KEY (`product_id`);
--
-- AUTO_INCREMENT for dumped tables
--
--
-- AUTO_INCREMENT for table `category`
--
ALTER TABLE `category`
MODIFY `category_id` int(11) NOT NULL AUTO_INCREMENT,AUTO_INCREMENT=4;
--
-- AUTO_INCREMENT for table `product`
--
ALTER TABLE `product`
MODIFY `product_id` int(11) NOT NULL AUTO_INCREMENT,AUTO_INCREMENT=14;
