Implement jQuery Bootgrid with PHP & MySQL

In our previous tutorial, we have explained how to implement Multiselect Dropdown with Checkbox using jQuery and PHP. In this tutorial, we will explain how to implement jQuery Bootgrid plugin to create grid based tables.

The grid tables are very user friendly and feature rich which are used in web application. The grid based tables can be easily created using jQuery plugins.

jQuery Bootgrid is a grid control plugin that convert simple Table into grid with functionality such as column sorting, searching, pagination etc. The plugin is designed specially for Bootstrap.

In this tutorial you will learn how to implement jQuery Bootgrid with server side using PHP and MySQL. The tutorial is explained in easy sreps with live demo and also can download source code.

Also, read:

• Autocomplete Search with Bootstrap, PHP & MySQL

So let’s start to implement jQuery Bootgrid with PHP & MySQL with live demo. The file structure for the demo is following.

• index.php
• data.php
• ajax_data.js

Steps1: Create Database Table with Record Insert

In this demo we will display developers records, so first we will create table developers using SQL query.

CREATE TABLE `developers` (
  `id` int(11) NOT NULL,
  `name` varchar(255) NOT NULL,
  `skills` varchar(255) NOT NULL,
  `address` varchar(255) NOT NULL,
  `gender` varchar(255) NOT NULL,
  `designation` varchar(255) NOT NULL,
  `age` int(11) NOT NULL,
  `image` varchar(255) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

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

INSERT INTO `developers` (`id`, `name`, `skills`, `address`, `gender`, `designation`, `age`, `image`) VALUES
(1, 'Smith', 'Java', 'Newyork', 'Male', 'Software Engineer', 34, 'image_1.jpg'),
(2, 'David', 'PHP', 'London', 'Male', 'Web Developer', 28, 'image_2.jpg'),
(3, 'Rhodes', 'jQuery', 'New Jersy', 'Male', 'Web Developer', 30, 'image_2.jpg'),
(4, 'Sara', 'JavaScript', 'Delhi', 'Female', 'Web Developer', 25, 'image_2.jpg'),
(5, 'Shyrlin', 'NodeJS', 'Tokiyo', 'Female', 'Programmer', 35, 'image_2.jpg'),
(6, 'Steve', 'Angular', 'London', 'Male', 'Web Developer', 28, 'image_2.jpg'),
(7, 'Cook', 'MySQL', 'Paris', 'Male', 'Web Developer', 26, 'image_2.jpg'),
(8, 'Root', 'HTML', 'Paris', 'Male', 'Web Developer', 28, 'image_2.jpg'),
(9, 'William', 'jQuery', 'Sydney', 'Male', 'Web Developer', 23, 'image_2.jpg'),
(10, 'Nathan', 'PHP', 'London', 'Male', 'Web Developer', 28, 'image_2.jpg'),
(11, 'Shri', 'PHP', 'Delhi', 'Male', 'Web Developer', 38, 'image_2.jpg'),
(12, 'Jay', 'PHP', 'Delhi, India', 'Male', 'Web Developer', 30, 'image_3.jpg');

Steps2: Include Bootstrap, jQuery and Bootgrid

As the jQuery 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 libraray 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_data.js"></script>

Steps3: Create Table HTML

First in index.php, we will create Table HTML to display developers records with jQuery Bootgrid. We use data-toggle=”bootgrid” with table to initialize jQuery Bootgrid and also Table id id=”developers” to handle Bootgrid functionality.

<div class="container">	
	<h2>Implement jQuery Bootgrid with PHP & MySQL</h2>	
	<table id="devlopers" 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: Make Ajax Request

Now in ajax_data.js, we will make Ajax request to server side script data.php to load records into Bootgrid table.

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

Steps5: Get Records from MySQL Databe Table

Now finally we will get developers records from MySQL database table developers with search, sorting and pagination options and finally return JSON data to render using Bootgrid table.

<?php
if(!empty($request['searchPhrase'])) {   
	$where_condition .=" WHERE ";
	$where_condition .=" ( name LIKE '".$request['searchPhrase']."%' ";    
	$where_condition .=" OR skills LIKE '".$request['searchPhrase']."%' ";
	$where_condition .=" OR gender LIKE '".$request['searchPhrase']."%' ";
	$where_condition .=" OR designation LIKE '".$request['searchPhrase']."%' )";
}
if( !empty($request['sort']) ) {  
	$where_condition .=" ORDER By ".key($request['sort']) .' '.current($request['sort'])." ";
}
$sql_query = "SELECT id, name, gender, skills, address, designation, age FROM `developers` ";
$total_developer_sql .= $sql_query;
$developer_sql .= $sql_query;
if(isset($where_condition) && $where_condition != '') {
	$total_developer_sql .= $where_condition;
	$developer_sql .= $where_condition;
}
if ($limit!=-1) {
	$developer_sql .= "LIMIT $start, $limit";
}
// Getting total number of developer record count
$result_total = mysqli_query($conn, $total_developer_sql) or die("database error:". mysqli_error($conn));
$total_developer = mysqli_num_rows($result_total);
// getting eployee records and store into an array
$resultset = mysqli_query($conn, $developer_sql) or die("database error:". mysqli_error($conn));
while( $developer = mysqli_fetch_assoc($resultset) ) { 
	$developer_records[] = $developer;       
}
// creating developer data array according to jQuery Bootgrid requirement to display records
$developer_json_data = array(
	"current"   => intval($request['current']), 
	'rowCount'  => 10,
	"total"     => intval($total_developer),
	"rows"      => $developer_records 
);
// return developer data array as JSON data
echo json_encode($developer_json_data);
?>

You may also like:

• Follow and Unfollow System with PHP & MySQL
• GST Billing System with PHP & MySQL
• Restaurant Management System with PHP & MySQL
• Visitor Management System with PHP & MySQL
• Student Attendance System with PHP & MySQL
• Like and Dislike System with Ajax, PHP & MySQL
• Build Star Rating System with jQuery, PHP & MySQL
• User Registration and Login System with PHP & MySQL
• Build Contact Form with PHP and Ajax
• To-do List Application with Ajax, PHP & MySQL
• Build Event Calendar with jQuery , PHP & MySQL
• Create Dynamic Bar Chart with JavaScript, PHP & MySQL
• Drag And Drop File Upload with jQuery and PHP
• Drag and Drop Reorder with jQuery, PHP & MySQL
• Data Export to Excel with PHP and MySQL
• Multiple Image Upload with jQuery, PHP and MySQL
• Create Ajax Pagination with PHP and MySQL
• jQuery Datatables Server Side Processing with PHP & MySQL
• Editable HTML Table with jQuery, PHP & MySQL
• Filter Search Result with Ajax, PHP & MySQL
• Consuming RESTful Web Services using PHP
• Image Crop and Upload using jQuery and PHP
• Live Datatables CRUD with Ajax, PHP & MySQL
• Create REST API with PHP & MySQL
• Build Dynamic Image Gallery with PHP & MySQL
• Multiselect Dropdown with Checkbox using jQuery and PHP

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