jQuery Bootgrid Server Side Processing with PHP & MySQL

Bootgrid is a jQuery grid plugin just like DataTables jQuery plugin that convert HTMl table into grid with features like column sorting, search, pagination etc.

In this tutorial you will learn how to process jQuery Bootgrid plugin at server side with PHP and MySQL. The tutorial explained in easy steps with live demo and link to download source code.

Also, read:

• Load Dynamic Content in Bootstrap Popover with Ajax, PHP & MySQL
• Create Dynamic Bootstrap Tabs with PHP & MySQL
• Bootstrap Contact Form with Captcha using Ajax and PHP
• Multi Select Dropdown with Checkbox using Bootstrap, jQuery and PHP

As we have covered this tutorial with live demo to process jQuery Bootgrid plugin at server side with PHP and MySQL, so the file structure for the example is following.

• index.php
• fetch_data.php
• ajax.js

Steps1: Create Database Table with Record Insert
As in this Bootgrid example, we will display employee records, so first we will create table employee using below table create SQL query.

CREATE TABLE `employee` (
  `id` int(11) NOT NULL COMMENT 'primary key',
  `employee_name` varchar(255) NOT NULL COMMENT 'employee name',
  `employee_salary` double NOT NULL COMMENT 'employee salary',
  `employee_age` int(11) NOT NULL COMMENT 'employee age'
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

we will insert few records into created table using below insert statement.

INSERT INTO `employee` (`id`, `employee_name`, `employee_salary`, `employee_age`) VALUES
(2, 'Tim', 170750, 63),
(3, 'Smith ', 86000, 66),
(6, 'Williamsons', 372000, 61),
(7, 'Herrod', 137500, 59),
(8, 'Davidson', 327900, 55),
(9, 'Steve', 205500, 39),
(10, 'Frost', 103600, 23),
(11, 'Davis', 120000, 33),
(12, 'Ride', 14000, 32),
(13, 'Tony', 20000, 22),
(14, 'Frank', 23000, 18);

Steps2: Include Bootstrap, jQuery and Bootgrid plugin files
As the Bootgrid plguin is specially designed for Bootstrap, so in this example we have handled Bootgrid plugin with Bootstrap. So we will include Bootstrap, jQuery and Bootgrid plugin files in index.php

<link rel="stylesheet" href="https://maxcdn.bootstrapcdn.com/bootstrap/3.3.5/css/bootstrap.min.css">
<script src="https://ajax.googleapis.com/ajax/libs/jquery/2.1.3/jquery.min.js"></script>
<script src="https://maxcdn.bootstrapcdn.com/bootstrap/3.3.5/js/bootstrap.min.js"></script>
<link rel="stylesheet" href="https://cdnjs.cloudflare.com/ajax/libs/jquery-bootgrid/1.3.1/jquery.bootgrid.css" />
<script src="https://cdnjs.cloudflare.com/ajax/libs/jquery-bootgrid/1.3.1/jquery.bootgrid.js"></script>  
<script src="ajax.js"></script>

Steps3: Create Table Structure
We will create Table structure to display employee records with Bootgrid. We need to add data-toggle=”bootgrid” to Table to initialize jQuery Bootgrid and also Table id id=”employee” to implement Bootgrid functions to make ajax request to load data from server side.

<div class="container">	
	<h2>jQuery Bootgrid Server Side Processing with PHP & MySQL</h2>	
	<table id="employee" data-toggle="bootgrid" class="table table-condensed table-hover table-striped" width="100%" cellspacing="0">
		<thead>
			<tr>
				<th data-column-id="emp_id" data-type="numeric">Id</th>
				<th data-column-id="emp_name">Name</th>
				<th data-column-id="emp_salary">Salary</th>
				<th data-column-id="emp_age">Age</th>
			</tr>
		</thead>
	</table>	
</div>

Steps4: Load Bootgrid Data from Server
Now in ajax.js, we will make Ajax request to server side script fetch_data.php to load employee data into Bootgrid from MySQL database table. We will this with Table id on .bootgrid({});.

$( document ).ready(function() {
	$("#employee").bootgrid({	
		ajax: true,	
		url: "fetch_data.php"	
	});
});

Steps5: Get Data from MySQL Database Table
Now finally we will handle functionality to get data from MySQL database table employee with search, sorting and pagination functionality and finally return JSON data to render using Bootgrid.

<?php
include_once("db_connect.php");
$sql_query = "SELECT id as emp_id, employee_name as emp_name, employee_salary as emp_salary, employee_age as emp_age FROM `employee` ";
$total_employee_sql .= $sql_query;
$employee_sql .= $sql_query;
if(isset($where_condition) && $where_condition != '') {
	$total_employee_sql .= $where_condition;
	$employee_sql .= $where_condition;
}
// handling limit to get data 
if ($limit!=-1) {
	$employee_sql .= "LIMIT $start, $limit";
}
// Getting total number of employee record count
$result_total = mysqli_query($conn, $total_employee_sql) or die("database error:". mysqli_error($conn));
$total_employee = mysqli_num_rows($result_total);
// getting eployee records and store into an array
$resultset = mysqli_query($conn, $employee_sql) or die("database error:". mysqli_error($conn));
while( $employee = mysqli_fetch_assoc($resultset) ) { 
	$employee_records[] = $employee;       
}
// creating employee data array according to jQuery Bootgrid requirement to display records
$employee_json_data = array(
	"current"   => intval($post['current']), 
	'rowCount'  => 10,
	"total"     => intval($total_employee),
	"rows"      => $employee_records 
);
// return employee data array as JSON data
echo json_encode($employee_json_data);
?>

You may also like:

• Build Live Chat System with Ajax, PHP & MySQL
• Create Event Calendar with jQuery, PHP and MySQL
• Build Invoice System with PHP & MySQL
• Push Notification System with PHP & MySQL
• Create Bootstrap Cards with PHP and MySQL
• Build Content Management System with PHP & MySQL
• Convert Unix Timestamp To Readable Date Time in PHP
• Ajax Drop Down Selection Data Load with PHP & MySQL
• Inventory Management System with Ajax, PHP & MySQL
• Drag and Drop File Upload using jQuery and PHP
• Load Dynamic Content in Bootstrap Popover with Ajax, PHP & MySQL

You can view the live demo from the Demo link and can download the script from the Download link below.
Demo Download