DataTable demo (Server side) in Php,Mysql and Ajax Part:1

This post is first part of a series called Getting Started with Datatable 1.10 .

  1. DataTable demo (Server side) in Php,Mysql and Ajax
  2. jQuery Datatable with Custom Json format in Php, Mysql
  3. DataTable (Server side) Custom Column Search
  4. Datatable Scroller (Server Side)
  5. DataTable Responsive (Server side)
  6. Datatable Custom Loader
  7. Datatable Colvis (Column Visibility Customization)
  8. DataTable Table tools
  9. DataTable Custom Search By Tags Input (Server side)
  10. DataTable Search By Datepicker (Server side)
  11. Datatable Bulk Delete (Server Side)
  12. Datatable Page Resize (Server Side)

Grid view is a very important web component in modern web. Sorting, searching, pagination is not a easy job in HTML tables. So many grid view framework out there, DataTable.js is the most popular among them. It is open source, light weighted, highly flexible and customizable, features like AutoFill, inline editor, sticky header, responsive, Supports bootstrap, foundation. In version 1.10 DataTable has changed and improved over version 1.9. An entirely new API is available in DataTables 1.10.
In my blog I will try to explore datatable 1.10. So lets get started with most popular gridview framework in the planet.

In basic initialization datatable provides pagination, sorting, instant searching by loading whole data records at once. It can be a performance issue fetching large amount of data from server side. It will be better if you integrate server side pagination, searching and sorting, so we can break large amount data in chunk, So performance will increase significantly. Before proceed please take a look of the demo.

First i have created a index.php, where I have written html markup and basic initialization. created a table wth id “employee-grid” containing three column “Employee name”, “Salary”, “Age”.

<table id="employee-grid"  cellpadding="0" cellspacing="0" border="0" class="display" width="100%">
        <thead>
            <tr>
                <th>Employee name</th>
                <th>Salary</th>
                <th>Age</th>
            </tr>
        </thead>
</table>

Then add required assets in head which are jquery library, datatable js library and css . You can download them here. You can add directly cdn link of these files from here

<link rel="stylesheet" type="text/css" href="css/jquery.dataTables.css">
<script type="text/javascript" language="javascript" src="js/jquery.js"></script>
<script type="text/javascript" language="javascript" src="js/jquery.dataTables.js"></script>

Now its time to initialize the datatable with your table “employee-grid” . By ajax api we can fetch json the data from employee-grid-data.php and datatble will automatically handle fetched data and append after “thead”.

<script type="text/javascript" language="javascript" >
    $(document).ready(function() {
        var dataTable = $('#employee-grid').DataTable( {
            "processing": true,
            "serverSide": true,
            "ajax":{
                url :"employee-grid-data.php", // json datasource
                type: "post",  // method  , by default get
                error: function(){  // error handling
                    $(".employee-grid-error").html("");
                    $("#employee-grid").append('<tbody class="employee-grid-error"><tr><th colspan="3">No data found in the server</th></tr></tbody>');
                    $("#employee-grid_processing").css("display","none");

                }
            }
        } );
    } );
</script>

Datatable send a request to “employee-grid-data.php” with so many parameters like sorting column index, order, search value, start and length/limit and draw number.
So what is draw ? sending request for any operation by datatable is called draw. it send a random number and recieve same same number as draw to ensure that the Ajax returns from server-side requests are drawn in sequence by DataTables.

In firebug or any developers tools you can check sending parameters.
datatable_param

In employee-grid-data.php you can see the parameter by print the Array $_REQUEST, like.

<?php
    echo "<pre>";
    print_r($_REQUEST);
    echo "</pre>";
 ?>

Output:

Array
(
    [draw] => 2
    [columns] => Array
        (
            [0] => Array
                (
                    [data] => 0
                    [name] => 
                    [searchable] => true
                    [orderable] => true
                    [search] => Array
                        (
                            [value] => 
                            [regex] => false
                        )

                )

            [1] => Array
                (
                    [data] => 1
                    [name] => 
                    [searchable] => true
                    [orderable] => true
                    [search] => Array
                        (
                            [value] => 
                            [regex] => false
                        )

                )

            [2] => Array
                (
                    [data] => 2
                    [name] => 
                    [searchable] => true
                    [orderable] => true
                    [search] => Array
                        (
                            [value] => 
                            [regex] => false
                        )

                )

        )

    [order] => Array
        (
            [0] => Array
                (
                    [column] => 0
                    [dir] => asc
                )

        )

    [start] => 0
    [length] => 10
    [search] => Array
        (
            [value] => b
            [regex] => false
        )

)

Here I have created a columns array which contains database columns according to datatable column index. Because datatable sends only column index.

<?php
$columns = array(
// datatable column index  => database column name
    0 =>'employee_name',
    1 => 'employee_salary',
    2=> 'employee_age'
);
?>

Now we have to create json object which will contain .

  • draw: we send same number which has been send by datatable at time of draw/request.
  • recordsTotal: Total numbers of records in your table.
  • recordsFiltered: Total numbers of filtered records after searching in your table. If you do not perform any search then recordsFiltered will be same as recordsTotal.
  • data: Your fetched records data. You have to fetched the data as per start, length, search value, colomn name and sorting orders parameters. you can download dummy database table from here
$json_data = array(
                "draw"            => intval( $_REQUEST['draw'] ),
                "recordsTotal"    => intval( $totaldata ),
                "recordsFiltered" => intval( $totalfiltered ),
                "data"            => $data
            );
echo json_encode($json_data);

You can download full code from below or improve my code by forking at Github.

DownloadDemoGithub


  • Cody Franklinn

    Very helpful. Now I want to make this table responsive, can please help me out.

    • Hi Cody thank you. Keep in touch, I will post it shortly.

      • can you upload the css and the js files? i cant download it from the sourch

  • Hello Sailor.Thank you for your comment.

    By “Custom HTTP variables http://datatables.net/examples/server_side/custom_vars.html” , you’ll get json data on success, then using jQuery.each you can bind your data to customized dom/html.

    Let me know, your feeback. I will post on my blog about this topic.

    • sailor

      Hi again.. now i have worked a lot with your fine script , but when i add more columns in datatable and make them OR LIKE in search part , it still dont work.. what is the best way to make all fields searchable? Thanks for you example / guides 😀

      • Have you done like this?

        if( !empty($requestData['search']['value']) ) {
        // if there is a search parameter, $requestData['search']['value'] contains search parameter

        $sql.=" AND ( employee_name LIKE '".$requestData['search']['value']."%' ";
        $sql.=" OR employee_salary LIKE '".$requestData['search']['value']."%' ";
        $sql.=" OR employee_age LIKE '".$requestData['search']['value']."%' )";

        }

        this is best way to do. make sure brackets and quotations are exact.

        • sailor

          stupid error from my side 🙂 i forgort this sign ) after the new fields .. thanks a lot

  • Marina

    Hi, Thanks for sharing this code.
    Could you please add an exemple with $query.= JOIN
    and explain how to put 2 values into 1 column : ex : LastName + FirstName,
    I’m stuck with either the 1st point or the 2nd one, or both…
    Thank you

    • Hi Marina Thanks for your comment.

      You can use any join method , but column array have to arrange according join condition.

      Suppose you have 2 tables, employee (Alias e, has column:employee_name) and employee_meta (Alias em, has column: employee_salary, employee_age )

      $columns = array (
      0 =>'e.employee_name',
      1 => 'em.employee_salary',
      2=> 'em.employee_age'
      );

      in answer to 2nd query. you can use mysql concat() function to achieve this.

      • Marina

        Hi Arkaprava.
        Ok thanks for your answer

  • Hi, How can we add hyper link for any column

  • DZZ

    Nice tutorial, but I have a question
    how can I add form inputs in each row of the table? I tried in the part of success of the ajax but the table only shows the label “processing”

  • mksingh

    This tutorial is good. you find another example with search, filter with download and demo on http://www.discussdesk.com/bootstrap-datatable-with-php-mysql-server-side-script.htm

    • In your site download button is not working. you mentioned that, php in server sude, but in demo you fetched from a .txt file.

  • Thank you for this amazing code. It was very easy to understand and modify. Better than the given one in the DataTables documentation.

  • st-claude

    Hi
    this tutorial is amazing ! 🙂
    Would it be possible to add a chapter about inserting data in this mysql table ?

    (I am a complete noob in ajax but i know some html/php/mysql… )

    thanks a lot !

    • Glad you liked it.

      For “inserting data in this mysql table” you need to learn “how to google anything”.

  • Kavita Sonia

    hey could u please give example how to use fancybox pop out for editing data based on id?

  • Markus Philipp

    Great Tutorial. Unfortunately I get an error running the script: ’employee-grid-data.php: get employees’ It looks like its the first one. I also found out that I needed to use ‘mysqli_connect($servername, $username, $password, $dbname)’ to connect to my database. $mysqli = new mysqli(‘host’, ‘user’, ‘passwort’, ‘datenbank’);

    doesn’t work. Do you have an idea why I get this error: ’employee-grid-data.php: get employees’ ? https://datatables.net/development/server-side/php_mysqli is working if I exchange the db connection to mysqli_connect …

    • May be you are using older version of php-mysql or using incorrect credentials.

      • Markus Philipp

        I got the database connection now working and the data is in the table if I don’t use your If-Statement. If I use it I get thrown back the last error message ’employee-grid-data.php: get employees’

  • Markus Philipp

    Great Tutorial. Unfortunately I get an error running the script:
    ’employee-grid-data.php: get employees’ It looks like its the first one.
    I also found out that I needed to use ‘mysqli_connect($servername,
    $username, $password, $dbname)’ to connect to my database. $mysqli = new
    mysqli(‘host’, ‘user’, ‘passwort’, ‘datenbank’);

    doesn’t work. Do you have an idea why I get this error: ’employee-grid-data.php: get employees’ ? https://datatables.net/development/server-side/php_mysqli is working if I exchange the db connection to mysqli_connect …

    • shivani chintawar

      Same here :(. I have tweaked the query to include ORDER BY and LIMIT parameters only if the REQUEST obj has relevant data. but now it shows all the records and pagination, sorting are not working as expected. I am still working on this to understand Datatables better

  • Maneerat Thongchunit

    Thank you

  • Maneerat Thongchunit

    help plz. I just search all keyword but this search the first character what do I do

    • Use like that : employee_name LIKE ‘%”.$requestData[‘search’][‘value’].”%’ “;

  • Maneerat Thongchunit

    I put hyperlink error show “No data found in the server”

    • It may db connection problem or your query does not written properly.

  • Abdelmoula Nami

    Hi,
    Very helpful topic ,can you please show me how minimise number of result ,I have 75582 Article so i need to display only 500 from my table
    thank you

  • Nick

    Any clue how to make a date range? I love the date picker but I would love it even more if there could be a min and max date.

  • kenosee

    Hi. Thanks for this great server side script. I am new to php/java/css/MySQL/datatables so found your code invaluable. I need to show row-details on the client side but all examples I have seen require returned results as name:value pairs with DT_RowId:row_99999. Can you please help with some code to display row details using results returned from your script eg.{“draw”:2,”recordsTotal”:14002,”recordsFiltered”:338,”data”:[[“ARMSTRONG”,”AL5 1NF”,”RINGWOOD”,”BH24
    2PE”,”9.0000″,”19-08-2015″,”179″,”150″,”705″],[“BROWN”,”B10 9LR”,”LONDON”,”SE5
    0SU”,”19.0000″,”19-08-2015″,”193″,”161″,”11672″]]}
    Thankyou.

  • FionaKTH

    help me please.

  • Markus Philipp

    Your example is working great, thanks! Yet, if there are Non-ASCII characters in my table (like e.g. http://terpconnect.umd.edu/~zben/Web/CharSet/htmlchars.html ) the rows with those characters don’t display or messes up the whole datatable (sorting, filtering and not displaying anything). Do you know what might be the issue here?!

  • vipul kumar

    I am sending data with data: jQuery(“#formid”).serializeArray() but on server side it is not working properly . please check .

  • Prasanth

    How do i make this one as editable data table

  • rana

    how i add edit delet and update option in this table

  • rana

    how can i add edit button using popup menu in this table

  • Lyhong Pon

    I got problem with multi sorting. When I tried printing the data passed to php, I see index order has always one length. What is the problem here? Did I make something wrong?

    • Can you please post your datatable initialization code. May be you have disabled sorting there.

  • Harris Pacpaco

    I have a problem, it says No data found in the server. But already imported the database and no error was occured. I’m using your code. Can you help me to figure what went wrong?

    • Check couple of things.
      1) database connection.
      2) query to fetch the data.
      3) in developers tolls, like firebug, check the request/response.

      • Harris Pacpaco

        Thank you sir, can i add a feature that when i click a row or an item it will give me a form where i can choose if i can edit or update the whole row data?

  • Harris Pacpaco

    I’ve got an error in sorting. when a value is 1 digit its ok but if there are 2digits, it will sort but it will show the 2digit value first. sort like this.. .11 12 13 1 2 3 4 5, or 5 4 3 2 1 13 12 11 10.

  • Muhammad Muddassar

    when i add more than 3 fields in colums

    database column name
    0 =>’employee_name’,
    1 => ’employee_salary’,
    2=> ’employee_age’
    );
    ?>

    than searching query is not working for fourth field . my q is why?and how i can enable more fields searchables

  • i cant download the css and js files.
    can you send it to me? i am need it , please

  • wade1003

    Hello! It is very good project. I just want to ask you, why I can’t add character in second row, just integer, how can I edit that? Thank you

  • souravmondal45

    Hey there! This is such a great explanation about implement jQuery DataTable with server-side (PHP) processing. Really appreciate author’s great work. But if anyone wants the same with asp.net MVC as server-side just follow this link http://www.dotnetawesome.com/2015/11/implement-jquery-datatable-in-aspnet-mvc.html . Thanks.

    • Hey Sourav thanks for compliment. I reviewed your article. you have explained a natural way.

  • wFire

    Thank you for this example! But I have an issue: when I use English (Latin) letters in search field, it’s work fine. But when I try to use Russian letters (my table have the records in Russian language(UTF-8)), filter doesn’t work. Can anybody help me please?

  • Hi, I want to have hyperlink on the ID field and want to pass the ID variable to the URL. How can I do it ? I have somewhat modified code then yours. Presently the hyperlink text is displayed as “undefined” and the url variable is not passed. Here is my index.php file (http://pastebin.com/ijg6qfBy) and here is my response.php (http://pastebin.com/C0wuu5Vn). Secondly, my search don’t work, please also let me know where I have missed. Can you please guide me? Thanks.

  • Sudhir Pandey

    Thanks for article. I have followed according to your blog. I have found java script error

  • jc0966

    How does this example work for page 2, 3, 4… onward?
    If each page contained only 10 rows, but there was a total of 100, I don’t understand how page 2 specifies a query which starts from employee number 11.
    – How do you specify / set the “start” field when page 2 is clicked?
    – How does this part of the query work:
    “LIMIT , “?
    I have only seen SQL clauses with “LIMIT ”

  • Chris Manos

    Hi there, I would like to ask you if you have an example for not a json_data method. For example to send back html formatted content, so to modify the data coming from the database.
    Thanks

  • Luiz Veloso Rodrigues

    Hi great tutorial your friend, but I have a problem when doing an INNER JOIN, the query is coming right, but the returns datable No data found in the server could help me?

  • Rayhan Uddin

    thanks a lot.

  • FireFoxII

    Very thanks! But is there a possibility to use also child rows function? https://datatables.net/examples/api/row_details.html

  • Would you like to use OnceBuilder CRUD to mange data tables?

  • Ajay Babu

    HI , first of all i would like to say thanks for your great work on datatables.
    Could you please share here data table row grouping by using php?This would be a great help
    Thanks in advance.

  • Kevin

    if i do a search in your demo https://coderexample.com/demo/datatable-demo-server-side-in-phpmysql-and-ajax/ for `ou` nothing will appear(No matching records found) OI would like the following row at least to be displayed `Airi Satou`. Cna you plese advise how this could be done? Do I have to alter the `employee-grid-data.php` code much?

  • Kevin

    I would to be able to show all rows but
    if i add the following `”lengthMenu” : [[ 10, 25, 50, -1 ],[ ’10 rows’, ’25 rows’, ’50 rows’, ‘Show all’ ]],` it does not work can you advise?

    • Sumit

      Handle in ’employee-grid-data.php’ file..
      if($requestData[‘length’]== -1)
      $requestData[‘length’]=$totalFiltered;

  • Imran Shah

    Hello Mr. Majumder
    Thank you for very useful code. I need one help.
    How can I send a value to employee-grid-data.php from index.php directly without using search box. I need this value to use in SELECT query.
    Please help.

  • Karim Belkhiria

    THANK YOU Very much, for this example, which is REALLY simple and extremely good explained. With the download files to have a better look as well. After 2 days of searching for a solution, and posting plenty questions and comments on stackoverflow, I found your example, and it was the only thing that helped to get it. Thank you a lot for sharing this :-))))

  • Nawa Jt Samas

    Thank you for the demo. I was kindly asking if you could also post a simple tutorial and demo code on how to implement a JOIN query , for example an INNER JOIN query involving 3 tables…. Would really appreciate your help or if you have a sample code and dont mind sharing my email address is nawajoel@gmail.com , thanks again

  • Nawa Jt Samas

    Thank you for the demo. I was kindly asking if you could also post a simple tutorial and demo code on how to implement a JOIN query , for example an INNER JOIN query involving 3 tables…. Would really appreciate your help or if you have a sample code and dont mind sharing my email address is nawajoel@gmail.com , thanks again
    EXAMPLE query

    SELECT

    L.NUMERO_LAUDO,
    L.ANO_LAUDO,
    L.DATA_LAUDO,
    S.NOME AS NOME_LEGISTA,
    D.NOME AS NOME_DESTINATARIO,
    L.LAUDO,
    L.DESTINATARIO

    FROM IML.LAUDOS L
    INNER JOIN SSPJ.SERVIDORES S
    ON S.SERVIDOR = L.LEGISTA
    INNER JOIN SSPJ.DEPARTAMENTOS D
    ON D.DEPARTAMENTO = L.DESTINATARIO
    WHERE L.DESTINATARIO = $destinatario

    ORDER BY L.NUMERO_LAUDO DESC ;

    • Ugy Astro

      maybe you can use temporary table to store your join result, then do select like example above.

  • Willy Tedyanto

    superb. This is what I looking for. Thanks a lot

  • Superb. Sir, can you also suggest the datatable tools or gird like datatables.net

  • Gunsu Koksal

    thank you, this is really helpful.

  • aligenie

    Thank you very much for the nice demo. I was able to use your template to make my own SCRUD database with datatables. However, I have a problem that I was hoping you could help me with. I can’t get the buttons extention to work with AJAX. I can do it without but not with it. There is something on the internet about using initComplete function but I’m stuck. Any advice? I have the appropriate supporting files (js, css, etc) and I’m using the newer version of Datatables. Many thanks for your help!

  • Praveen Kumar R

    Hi i tried the code, i am able to see the json data when i load employee-grid-data.php but data is not visible in index.php its always showing data “No data found in server”

    Also only if i comment the below line in employee-grid-data.php code is working else its not working
    $sql.=” ORDER BY “. $columns[$requestData[‘order’][0][‘column’]].” “.$requestData[‘order’][0][‘dir’].” LIMIT “.$requestData[‘start’].” ,”.$requestData[‘length’].” “;

    without commenting if i try to print the query its showing

    SELECT * FROM test_table where 1=1 ORDER BY LIMIT

    Please help resolve this

  • error

    http://prntscr.com/dnruhj I am gettings this error

    • According to screenshot you have provided, seems the json data are not proper. Check every row, make sure every row has same numbers of property.

  • john anthony

    hi,

    thanks, this script very useful for me. but this only working 4 column.

    i have need for add more column in data-tables, how can changes to this script..?

  • Brammy Welang

    thank you, greetings from Indonesia!

  • Mobydick

    thank you

  • remmar moroscallo

    Hi, great tutorial! but i am having an issue in using sql join, whn i am just selecting * it shows but when i use join it says “no data found in the server” can ou help me out pls

  • Ibeneme Chidumebi Vitalis

    nice one

  • Lavy Ya

    How to use join !

  • Sanooj T
  • Vicky Raje

    Hi i have one doubt first if all it’s working gud for single table. i am using more than 3 tables inner join method use how to access that. it’s shows all content but sorting and searching can’t work. how to use that.

  • Fernando Gabriel Rossi

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

  • Manish Prajapati

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

  • great
    watch live cricket score with http://www.livecricketscorer.com

  • hnikol hn

    thanks!

  • hades unseen

    Thanks for this awesome tutorial man.

  • Krushio Vida

    How do i add link

  • Ramiro A. Siciliano

    Very simple, great !! Much better than the class of ssp.class.php 🙂

  • Rhalp10

    how can i modified this code and change the search style ?