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

Last Updated On: | By: Parvez

Datatable 1.10.9 has been released now, Datatable is most popular grid system in web development.Main benefit of Data-table is open source and light weighted.You can use this plugin with other css framework like bootstrap and foundation .There are a lot of datatable plugin 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 mysql database, So i have got many request to share code with php,mysql using ajax.

Also Checkout other tutorial of Datatable,

We are using below Files,The details are:

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

Step 1: we will include jquery data-table and jquery library.

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

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

here we are passing which service/file url.

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

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

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

You can download source code and Demo from below link.

  Live Demo

  • kamlesh

    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?

    • i checked and found that your code link has been removed.

      • 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.

        • ok, i will try when i ll get time.

          • hassan taha

            the code for adding hyperlink in datatable cell doesnt work

          • i used in my table its work,hv u checked anchor tag html using firebug or other degugger?

        • 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”);
          }
          }

  • hassan taha

    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.

    • thanks, i ll send you code once ill get time

  • 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,

  • Hudu Adam

    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

    • ok,i ll share code with this

  • loai

    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;
      }
      }
      });

  • Urs Gürtler

    Nice, thx! Exactly what i was looking for

  • Kajal Barad

    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.

  • Chkout my latest tutorial

  • Abdullah

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

    • it was on source code link instead of demo link,that is typo error.

  • Jagdish Patel

    How to search data using multiple column or individual column server side and also wildcard search…..

  • if link is dynamic then you need to parse data as like this tutorial ‘https://www.phpflow.com/php/parse-json-data-jquery-datatable/’ and if static then you can pass a constant.

    • faizal

      The above page is not available

  • in js side,
    You need to unset those index fields in ajax
    “dataSrc”: function (jsonData) {
    for ( var i=0, len=jsonData.data.length ; i<len ; i++ ) {
    delete(jsonData.data)
    jsonData.splice( i, 1 );
    }

    Server side:
    You can send response of those col values/index which u need to show in table

    • Dion Fitzgerald

      thats a lot of work for a server especially if you have a large db. as it stands you have set the query to search the whole db by using the * (search all). to make life simpler not only for yourself but also for the server just search columns that you choose in the query. My code is slightly different because my db name is not the same as yours and neither are my column names but take a look at the code i have edited in response.php. //define index of column
      $columns = array(
      0 =>’memberID’,
      1 =>’username’,
      2 => ‘urank’,
      3 => ‘gender’,
      4 =>’datejoined’,
      5 =>’lastseen’,
      6 => ‘country’

      );

      $where = $sqlTot = $sqlRec = “”;

      // check search value exist
      if( !empty($params[‘search’][‘value’]) ) {
      $where .=” WHERE “;
      $where .=” ( memberID LIKE ‘”.$params[‘search’][‘value’].”%’ “;
      $where .=” OR username LIKE ‘”.$params[‘search’][‘value’].”%’ “;
      $where .=” OR urank LIKE ‘”.$params[‘search’][‘value’].”%’ “;
      $where .=” OR gender LIKE ‘”.$params[‘search’][‘value’].”%’ “;
      $where .=” OR datejoined LIKE ‘”.$params[‘search’][‘value’].”%’ “;
      $where .=” OR lastseen LIKE ‘”.$params[‘search’][‘value’].”%’ “;
      $where .=” OR country LIKE ‘”.$params[‘search’][‘value’].”%’ )”;
      }

      // getting total number records without any search
      $sql = “SELECT memberID, username, urank, gender, datejoined, lastseen, country FROM members “;

      • i assumed, developer at-least change column name,db name,label etc before use it.

  • Dion Fitzgerald

    where is the download link?

    • first need to share tutorial then u ll get enable download link

  • Bala

    I had 2 tables, t1 has id, name and t2 has cid, value. t1 id has foreignkey cid. In your code u fetch full row and assign it to data[]. i need to display t2. First i fetch cid, then search cid from t1 then fetch name. i need to display name, cid, value. how to display 3 values, what can i do… U cannot understand my question just send mail to me, i ill give brief explanation. Thanks advance….

  • bala16

    In this belowed code, u fetch the value as a row. i want to fetch id and compare with other table. fetch certain value and display that value. for example table Name t1 and t2.. t1 has id, name. t2 has id and value. i need fetch t1 id and compare the id with t2 id, and fetch t2 value and display in data tables. what can i do…

    • You can handle this on server side or create seperate js method which processed ur logic then send data to dt instance.

      • bala16

        Ok, no issue. But i fetch a value from table how to apply search for that value.. Great Response..

  • Nilesh Sutar

    Awsome one..really helpful

  • gun

    thanks dude, just little bit modify working great in codeigniter

  • Vikas Paliwal

    Hi

    Its grate table, but is there any way where we can perform search at client side for data which loaded in one chunk. Lest say if I loaded 100 data then search will be perform on this particular records just like old data-table do. Please confirm its very help full to us to user new table.

  • Vmac

    Could you pls tell how can add a new custom column? I want to add a radio button for each row to create a select event

    • You can get help from my datatable parse json tuts

  • faizal

    How can i add href link on any of the table data

    Eg: if want add a link on employee id to divert to employee detail page or something like that

    Please help me

  • Connection error, db string might be not set

  • Manish Prajapati

    How can I get resultant data in success event. I append success event there after that dataTable is not working. any help?

    • You can call datatable getlistdata on succes handler

  • Fernando Gabriel Rossi

    Hi, I’m from Argentina, could you help me if I ask you a question?

    • Ya sure, but i m working person so may be it could be bit late

  • Yes, plz go ahead

  • without code debug is too difficult

  • Bekir güler

    Hi
    My $json_data seems OK with print_r. but, json_encode doesnt return anything.

    • can u please share json_data, to verify error

  • Thank you for this tutorial, very helpful. 🙂