Add paging sorting and search using jquery datatable

by Posted on

This is quick tutorial about adding paging, sorting and search feature in your table grid,
If you don’t have time to write code for paging, sorting and search feature then you can use jquery datatable plugin to add these feature instantly. You can also see tutorial to create paging in core php and If you are cakephp developer have a look, How to create paging and sorting in cakephp
datatable
So lets start the tutorial.

Here i have a std code database of india and i need to create table grid with paging sorting and search feature So i am going to use jquery datatable to make these feature quickly.




First of all make database connection and write query to fecth data from database.

<?php
$hostname = "localhost";
$username = "root";
$password = "root";
$dbname = "test";
	$con = mysqli_connect($hostname, $username, $password, $dbname);
    $query = "SELECT * FROM stdcodes";
    $result = mysqli_query($con, $query);
?>

After that create view page. Here i am going to use bootstrap datatable version so add required bootstrap and datatable css and js files on your view page.

<!-- CSS Library -->
<link rel="stylesheet" href="//maxcdn.bootstrapcdn.com/bootstrap/3.3.5/css/bootstrap.min.css">
<link rel="stylesheet" href="//cdn.datatables.net/1.10.10/css/jquery.dataTables.min.css">
 
<!-- JS Library -->
<script src="http://code.jquery.com/jquery-1.10.2.js"></script>
<script src="//cdn.datatables.net/1.10.10/js/jquery.dataTables.min.js"></script>
<script src="https://cdn.datatables.net/1.10.10/js/dataTables.bootstrap.min.js"></script>

After that create dynamic table grid using php

<table id="stdcode" class="table table-striped table-bordered dataTable" cellspacing="0" width="100%" role="grid" aria-describedby="example_info" style="width: 100%;">
<thead>
<tr >
 <th>S.No</th><th>Stdcode</th> <th>City</th><th>Circle</th>	
</tr>
</thead>
<tbody>
<?php $sn=1; foreach($result as $resultSet) { ?>
<tr>
 <th><?= $sn ?></th><th><?= $resultSet['stdcode'] ?></th> <th><?= $resultSet['city'] ?></th><th><?= $resultSet['circle'] ?></th>	
</tr>
<?php $sn++; } ?>
</tbody>
</table>

Now finally add datatable function in your page to make it action.

 <script>
 $(function(){
   $('#stdcode').DataTable();
 });
 </script>

Where #stdcode is table id.



Now your final index.php file will be..

index.php

<?php
$hostname = "localhost";
$username = "root";
$password = "root";
$dbname = "test";
	$con = mysqli_connect($hostname, $username, $password, $dbname);
    $query = "SELECT * FROM stdcodes";
    $result = mysqli_query($con, $query);
?>
 
<!doctype html>
<html lang="en">
<head>
  <meta charset="utf-8">
  <title>Jquery datatable demo</title>
  <link rel="stylesheet" href="//maxcdn.bootstrapcdn.com/bootstrap/3.3.5/css/bootstrap.min.css">
  <link rel="stylesheet" href="//cdn.datatables.net/1.10.10/css/jquery.dataTables.min.css">
 
  </head>
<body>
<div style="margin:0 auto; text-align:center; width:80%">
<h3>Jquery DataTable demo(PHP, MYSQL)</h3>
<table id="stdcode" class="table table-striped table-bordered dataTable" cellspacing="0" width="100%" role="grid" aria-describedby="example_info" style="width: 100%;">
<thead>
<tr >
 <th>S.No</th><th>Stdcode</th> <th>City</th><th>Circle</th>	
</tr>
</thead>
<tbody>
<?php $sn=1; foreach($result as $resultSet) { ?>
<tr>
 <th><?= $sn ?></th><th><?= $resultSet['stdcode'] ?></th> <th><?= $resultSet['city'] ?></th><th><?= $resultSet['circle'] ?></th>	
</tr>
<?php $sn++; } ?>
</tbody>
</table>
 <script src="http://code.jquery.com/jquery-1.10.2.js"></script>
  <script src="//cdn.datatables.net/1.10.10/js/jquery.dataTables.min.js"></script>
   <script src="https://cdn.datatables.net/1.10.10/js/dataTables.bootstrap.min.js"></script>
 
 <script>
 $(function(){
   $('#stdcode').DataTable();
 });
 </script>
</body>
</html>

If you have hug records in your database then i’ll not recommend above datatable function that was very basic function of datatable, You must use server processing function of datatable Please have a look.
https://www.datatables.net/examples/data_sources/server_side.html

DEMO

DOWNLOAD

If you like this post please don’t forget to subscribe my public note book for more useful stuff

Published by admin