Php

Datatable Pagination, Sorting and Search – Server Side (PHP/MySQl) Using Ajax

The Datatable 1.10.9 has been released now, Datatable is the most popular grid plugin in web development. The main benefit of Datatable is the open source and light-weighted jquery plugin. You can use this plugin with other CSS frameworks like bootstrap and foundation. There are a lot of datatable plugins are available which useful to improve UI experience.

I got huge response from reader on previous datatable tutorial which was DataTables Example – Server-side Processing with PHP.I have used JSON data in code instead of fetch data from the MySQL database, So I have got many requests to share code with PHP, MySQL using ajax.

Also Checkout other tutorial of Datatable,

Video Tutorial:

If you are more comfortable in watching a video that explains about Datatable Pagination, Sorting and Search – Server Side(PHP) Using Ajax, then you should watch this video tutorial.

Datatable Server Side Integration Using PHP

There are following files are participate in this Datatble tutorial:

Index.php - This file will responsible to create html and instance datatable using jquery code.
response.php - This file responsible to create database connection string and convert records into json string and returns data to Ajax method as response.
connection.php - This file responsible to create mysql database connection.

Datatable Example with PHP,MySql server side Using Ajax

We will create simple PHP application to fecth data from server using PHP.The php is using mysql to store and fetch data.

The jQuery datatable help to display records into html page with pagination sorting and searching feature.

How To Create MySQL Database Connection Using mysql_connect

We will Create db_connect.php file into root of the project path, Now we will put the below connection code in this file.

Related Post
$dbhost = 'localhost';  
$dbuser = 'root';  
$dbpass = "";  
$conn = mysql_connect($dbhost, $dbuser, $dbpass) or die ('Error connecting to mysql');  
$dbname = 'test';  
$connection = mysql_select_db($dbname);  

Step 1: we will include jquery datatable and jquery library.

 
  
 <script type="text/javascript" src="https://cdn.datatables.net/r/dt/dt-1.10.9/datatables.min.js"></script>

 <script type="text/javascript" charset="utf8" src="http://ajax.aspnetcdn.com/ajax/jQuery/jquery-1.8.2.min.js"></script>

Step 2: Create HTML layout in your web page (index.php).

<div class="container">
<div class="">
<h1>Data Table</h1>
<div class="">
<table id="employee_grid" class="display" width="100%" cellspacing="0">
<thead>
<tr>
<th>Empid</th>
<th>Name</th>
<th>Salary</th>
<th>Age</th>
</tr>
</thead>
<tfoot>
<tr>
<th>Empid</th>
<th>Name</th>
<th>Salary</th>
<th>Age</th>
</tr>
</tfoot>
</table>
</div>
</div>
</div>

Step 3: Instantiate datatable object on table(index.php).

$( document ).ready(function() {
 $('#employee_grid').DataTable({
   "bProcessing": true,
         "serverSide": true,
         "ajax":{
            url :"response.php", // json datasource
            type: "post",  // type of method  , by default would be get
            error: function(){  // error handling code
              $("#employee_grid_processing").css("display","none");
            }
          }
        });   
});

Above code – We have applied datatable on employee_grid table.The serverSide have the rest url path which will use to get data from backend server.

Step 4: Include connection file into top of the response.php file.

 //include connection file 
 include_once("db_connect.php");

Step 5: Using parameters to create sql.

  • $params[‘order’] : This array contains order information of clicked column
  • $params[‘search’] : This array contains serach value information datatable
  • $params[‘start’] : start limit of data
  • $params[‘length’] : end limit of data
$sql = "SELECT * FROM `employee` ORDER BY id  asc LIMIT ".$params['start']." ,".$params['length']." ";

Step 6: Send json data to the index.php file.

 $json_data = array(
   "draw"            => intval( $params['draw'] ),   
   "recordsTotal"    => intval( $totalRecords ),  
   "recordsFiltered" => intval($totalRecords),
   "data"            => $data   // total data array
   );

You can download source code and Demo from below link.

View Comments

  • Hi Parvez,

    Thanks for this wonderful post.

    I followed your tutorial in one of my project. Its working fine, just for 2 things I need your help. (1) The search is not functioning. And (2) I want to add a hyperlink to a field. On clicking it, the details view page of the record will be displayed. I don't know how to acomplish this. I have tried some code which creates the desired hyperlink but the url valiable is not passed. Even the text of hyperlink is displaying "Undefined". If you need to see my code,

    Can you suggest me what's wrong?

      • sorry, the code is there but the hyperlink at phpflow is appending ")". Just remove the last ")" in the url and check it. Thank you for your prompt response.

        • This is code to add hyperlink in datatable cell,

          "serverSide": true,
          "aoColumnDefs": [
          {
          "aTargets":[1],

          "mData": null,
          "mRender": function( data, type, full) {
          console.log(data);
          return +data[1];
          }
          }
          ],
          "ajax":{
          url :"response.php", // json datasource
          type: "post", // type of method , by default would be get
          error: function(){ // error handling code
          $("#employee_grid_processing").css("display","none");
          }
          }

    • Hi kamlesh
      Thank you very much.
      By this project "Datatable CRUD Operation Using Ajax with Bootstrap 5, PHP and MySQL" I have always this warning:
      DataTables warning: table id=dt-employee - Invalid JSON response. For more information about this error, please see http://datatables.net/tn/1
      Can you help me?

  • Hi,
    Thanks a lot for this tutorial, it really help me.

    i need help in something .. i have a DATE as a fields so how i can arrange your code to make the sorting works.

    thks again.

  • Hello.
    I'm new to AJAX and libraries in general when it comes to web development, so I have a few questions.
    Let me start off with telling you that I have a CSGO server up and running, and I want to display data to my visitors from this database. Configuring the connection and SQL queries was ok, but now I've run into some problems.

    1. I want to display a column "Rank", which is not in the database, but given to the players depending on their 'score'. EG: The player with the highest score gets rank 1, second highest rank 2 etc.
    With PHP this was easy enough, I just set the $rank to $totalPages * $perPage and incremented the $rank per row. But so far I havent figured out how to do this in AJAX.

    2. In my database I can see kills, deaths, shots, hits and headshots. I want to display to my visitors the amount of % headshots and their kill/death-ratio. Do you have any idea how to make this query with AJAX, and also how to add an extra column to display this? Thus far I just get errors when adding more columns than expected.

    Thank you so much for the code and continously help. It is very appreciated!

    EDIT: I forgot to ask about the ORDER BY. I can see that this is set to order the first column by an ascending order. Is there any way to customize this? I would like to have it to order by score in an descending order instead!
    Again, thank you so much for your help!

  • As per my understanding about your questions,
    You can do same thing using mysql script very easily or you can create mysql views and get records from mysql views instead of table.

    ORDER BY - You can set using data table parameter,

  • Hello,
    Please how do i make one of the fiels clickable say the name field so that user can click to open or download a file

  • Hello,
    Thanks for sharing this nice article , I would like to know how i send data (as object) to the server side , for example i would like to send employee data (first name,last name..ect ) and do the search at the database based on this values .
    Here is my code but it doesn't work for me

    function SearchEmps(empData) { // javaScript object with employee data(first name,last name..ect )
    var DTO = { 'empData': empData };
    $('#employee_grid').DataTable({
    "bProcessing": true,
    "serverSide": true,
    "ajax":{
    url :"response.php", // json datasource
    type: "post", // type of method , by default would be get
    data: JSON.stringify(DTO),
    error: function(){ // error handling code
    $("#employee_grid_processing").css("display","none");
    }
    });
    }

    // Server side Get data and perform search....

    • this is sample code to send custom parameters to serverside datatable:
      var data = { 'name': 'parvez', 'lname':'alam'};
      $('#employee_grid').DataTable({
      "bProcessing": true,
      "serverSide": true,
      "responsive": true,
      "ajax":{
      url :"response.php", // json datasource
      type: "post", // type of method ,GET/POST/DELETE
      error: function(){
      $("#employee_grid_processing").css("display","none");
      },
      "data": function ( d ) {
      d.cParms = data;
      }
      }
      });

  • Thankx its work fine,
    but i have some queries,

    I need to put tag in b'coz i want to show image in table from database.
    I also need a link for some action like Update & Delete.

    please help me to solve out this.

    Thank You.

  • As you have mentioned that you have the source code on the demo link. But there is no source code.

Recent Posts

Configure and Retrieve S3 Information Using Laravel PHP-AWS-SDK

This tutorial helps integrate a PHP SDK with Laravel. We'll install aws-php-sdk into laravel application and access all aws services… Read More

2 months ago

What is the Purpose of php_eol in PHP?

in this quick PHP tutorial, We'll discuss php_eol with examples. PHP_EOL is a predefined constant in PHP and represents an… Read More

8 months ago

Laravel Table Relationship Methods With Example

This Laravel tutorial helps to understand table Relationships using Elequonte ORM. We'll explore laravel table Relationships usage and best practices… Read More

8 months ago

Exploring the Power of Laravel Eloquent Join?

We'll explore different join methods of Laravel eloquent with examples. The join helps to fetch the data from multiple database… Read More

9 months ago

Quick and Easy Installation of Laravel Valet

in this Laravel tutorial, We'll explore valet, which is a development environment for macOS minimalists. It's a lightweight Laravel development… Read More

9 months ago

What is Laravel Soft Delete and How Does it Work?

I'll go through how to use soft delete in Laravel 10 in this post. The soft deletes are a method… Read More

9 months ago

Categories