Call Now

WhatsApp

Go back
to top

Arrow Icon
Site Logo

jQuery DataTables Ajax based Or Server Side Sorting, Pagination and Searching

Shape Image Layout Star Icon Star Icon

JQuery DataTables Ajax based Or Server Side Sorting, Pagination and Searching

jQuery DataTable server-side sorting, pagination, and searching using PHP and MySQL

In this PHP Javascript Tutorial, I'm attending to tell you the way to use jQuery datatable plugin with Server-side processes like looking sorting and page number with PHP and MySQL.

There area unit countless practicality in-build by default with jQuery DataTables, it'll save you countless time to implement looking sorting and page number manually from server facet victimization PHP and MySQL.

It will increase the performance of the application if you receive the chunk information from massive amounts of knowledge kept within the info tables.

jQuery DataTables is an open supply, it suggests that you'll be able to freely transfer the libraries for your internet application and you'll be able to simply customize this plugin because of extreme flexibilities.

Create Information Table


Run the subsequent MySQL question to form a table "Profile" in your info to start out with this instance.

CREATE TABLE `users` (
 `id` int(11) NOT NULL AUTO_INCREMENT,
 `name` varchar(255) NOT NULL,
 `phone` varchar(20) NOT NULL,
 `email` varchar(100) NOT NULL,
 `created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
 `updated_at` datetime DEFAULT NULL,
 PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=12 DEFAULT CHARSET=latin1


Create a User PHP file
In this step, I will create HTML file and include the DataTables libraries and then instantiate the DataTable on the table.

index.php

<!DOCTYPE html>
<html>
<head>
   <title>jQuery DataTable server side sorting,pagination and searching using PHP and MySQL</title>
   <script type="text/javascript" src="https://code.jquery.com/jquery-1.9.1.min.js"></script>
   <link rel="stylesheet" type="text/css" href="http://ajax.aspnetcdn.com/ajax/jquery.dataTables/1.9.4/css/jquery.dataTables.css">
   <script type="text/javascript" charset="utf8" src="https://ajax.aspnetcdn.com/ajax/jquery.dataTables/1.9.4/jquery.dataTables.min.js"></script>
</head>
<body>
<div class="container">
  <h2>jQuery DataTable server side sorting,pagination and searching using PHP and MySQL</h2>
  <table id="demo">
    <thead>
      <tr>
          <th>Id</th>
          <th>Name</th>
          <th>Age</th>
          <th>Salary</th>
      </tr>
    </thead>
  </table>
</div>
</body>
<script type="text/javascript">
  $(document).ready(function() {
      $('#demo').dataTable({
        "bProcessing": true,
        "bServerSide": true,
        "sAjaxSource": "server.php",
        "aoColumns": [
              { mData: 'id' } ,
              { mData: 'name' },
              { mData: 'email' },
              { mData: 'phone' }
            ]
      });  
  });
</script>
</html>

 

Create ajaxrequest.php File


In this step, I will create a PHP file "ajaxrequest.php" and write MySQL query for each ajax request from DataTable components.

$tableColumns = array('id','name', 'phone', 'email');
$primaryKey = "id";
  $mysqli = new mysqli(localhost, root, yourdbpassword, yourdbname);

$limit = "";
if (isset($_GET['iDisplayStart'] ) && $_GET['iDisplayLength'] != '-1' )
{
  $limit = "LIMIT ".mysqli_real_escape_string($mysqli,$_GET['iDisplayStart'] ).", ".
    mysqli_real_escape_string($mysqli,$_GET['iDisplayLength'] );
}
/*
 * Ordering
 */
if ( isset( $_GET['iSortCol_0'] ) )
{
  $orderBy = "ORDER BY  ";
  for ( $i=0 ; $i   {
    if ( $_GET[ 'bSortable_'.intval($_GET['iSortCol_'.$i]) ] == "true" )
    {
      $orderBy .= $tableColumns[ intval( $_GET['iSortCol_'.$i] ) ]."
        ".mysqli_real_escape_string($mysqli,$_GET['sSortDir_'.$i] ) .", ";
    }
  }
  

Tags :

jQuery DataTables server-side pagination example PHP

jQuery DataTables pagination example code

DataTables server-side processing example

ajax pagination with search and filter in php
jQuery DataTables ajax json example php

DataTables server-side number format

DataTables server-side parameters

Related Posts

jQuery DataTables Ajax based Or Server Side Sorting, Pagination and Searching

Fireworks vs Photoshop – Where to use what?

It’s over the years that we heard about the Adobe Photoshop. It was a new software that helped

Read More
jQuery DataTables Ajax based Or Server Side Sorting, Pagination and Searching

Top 10 Essential Tips for Website Design 2021: Reduce Your Website Bounce Rate?

Introduction: If you are getting consistent traffic on your site however neglecting to transform

Read More

Hire Web Developer & Web Developers Coimbatore

We are Experts from Coimbatore, India providing highly professional Web developer in Coimbatore loca

Read More