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 coloumn sorting, search, pagination etc. In this tutorial you will learn how to process jQuery Bootgrid plugin at server side with PHP and MySQL. The tuorial expalined in easy steps with live demo and link to download source code.


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 specialy designed for Bootstrap, so in this example we have handled Bootgrid plguin 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 can view the live demo from the Demo link and can download the script from the Download link below.
Demo Download