DataTables Example – Server-side Processing with PHP

Last Updated: | By: PHPFlow Team

In the previous post Data Table jQuery Plugin, We have learned what is jQuery datatable plugin and how to use jQuery datatable in your application.

in this tutorial, I will describe how to use data table with server-side scripting. I am using PHP and MySQL to get records from the server side.

Our aim is to get data from MySQL with help of PHP and passed data to the jQuery datatable constructor.

Also Checkout other tutorial of Datatable,

datatable-with-php

There are the following steps need to achieve over goal:

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

<!-- DataTables CSS -->
<link rel="stylesheet" type="text/css" href="http://ajax.aspnetcdn.com/ajax/jquery.dataTables/1.9.4/css/jquery.dataTables.css">
 
<!-- jQuery -->
<script type="text/javascript" charset="utf8" src="http://ajax.aspnetcdn.com/ajax/jQuery/jquery-1.8.2.min.js"></script>
 
<!-- DataTables -->
<script type="text/javascript" charset="utf8" src="http://ajax.aspnetcdn.com/ajax/jquery.dataTables/1.9.4/jquery.dataTables.min.js"></script>

Step 2: Created an HTML table into the your web page.

<div class="">
<table id="example" class="display" width="100%" cellspacing="0">
<thead>
<tr>
<th>Empid</th>
<th>Name</th>
<th>Salary</th>
</tr>
</thead>
<tfoot>
<tr>
<th>Empid</th>
<th>Name</th>
<th>Salary</th>
</tr>
</tfoot>
</table>
</div>

Step 3: Apply Datatable functionality on our HTML table.

$( document ).ready(function() {
      $('#example').dataTable({
				 "bProcessing": true,
                 "sAjaxSource": "response.php",
				 "aoColumns": [
						{ mData: 'Empid' } ,
                        { mData: 'Name' },
                        { mData: 'Salary' }
                ]
        });   
});

Step 4: Now we will create response.php file and write down the below code.

$data = array(
	array('Name'=>'parvez', 'Empid'=>11, 'Salary'=>101),
	array('Name'=>'alam', 'Empid'=>1, 'Salary'=>102),
	array('Name'=>'phpflow', 'Empid'=>21, 'Salary'=>103)							
	);
             

	$results = array(
			"sEcho" => 1,
        "iTotalRecords" => count($data),
        "iTotalDisplayRecords" => count($data),
          "aaData"=>$data);
/*while($row = $result->fetch_array(MYSQLI_ASSOC)){
  $results["data"][] = $row ;
}*/

echo json_encode($results);

Here we have taken constant array instead of MySQL records, I am assuming you will replace this constant array with your MySQL result set.

  Live Demo
 Download Source Code

26 thoughts on “DataTables Example – Server-side Processing with PHP”

  1. Nice Tutorial Parvez. I found it very simple than datatable website. I’m working on bootstrap 3 with datatables with huge dataset coming from DB, I just wanted it to be from server rather than sending the whole dataset to datatables and leave this to it. But having huge dataset (>10K records) taking little time. Can you please provide the code to get the data from server with pagination, sorting and search. Thanks a ton in advance..

    Reply

      • Hi,
        In step 4 i have mensioned static array,

        $data = array(
        array(‘Name’=>’parvez’, ‘Empid’=>11, ‘Salary’=>101),
        array(‘Name’=>’alam’, ‘Empid’=>1, ‘Salary’=>102),
        array(‘Name’=>’phpflow’, ‘Empid’=>21, ‘Salary’=>103) );

        below i am providing roughly idea:
        $sql =”select * from studens”;

        $data = mysql_fetch_assoc($sql),

        now you can get associative array form your database.

        Reply

  2. Hello Parvez, I am Satya a beginner in Datatables, I am working in such a situation that I need to generate datatable on button click and it should get the data from the database using Joins, I tired doind few cases but I get Invalid json format, so can you suggest me how to do that please ?

    Reply

  4. Hi! Thanks a lot, this is really helpfull for my project. I am a Computer Engineering student, still learning about this kind of things and I bookmarked this website for reference. Once again thanks a lot!!

    Reply

  5. Hi Parvez,

    Love your script much, i has same question with Rahul. There is almost 10K dataset in my database and took so long to load into dataset. I’m waiting your next tutorial based on this case.

    Cheers

    Reply

  7. Hi,

    I have used this method, it is working fine, but i have few questions:
    1- If i need to add static values to the last column, example: Delete icon, to delete the record
    2- Color the line based on a certain value
    3- Instead of Previous, Next to use <>
    4- Pass a certain value to filter the select in the server side
    5- Align the pagination with the Showing number of entries

    Thank you

    Reply

    • The point 2,3 and 5 can be handle by css and rest of them can be found in datatable dos easily,if you not find let us know,i ll share link or tutorial for them.

      Reply

  8. Hi, this is a good example thank you a lot,
    could you please give me and example with the Mysql records
    Regards

    Reply

  10. Hi this is good example of static value can you please give me example of Mysql record i had error
    json data from server cannot be parse…

    Reply

    • yes, datatable providing many pagination nav options, You can get more information from datatable official website.

      Reply

Leave a Reply

Your email address will not be published. Required fields are marked *