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,
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.
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
This Laravel tutorial helps to understand table Relationships using Elequonte ORM. We'll explore laravel table Relationships usage and best practices… Read More
We'll explore different join methods of Laravel eloquent with examples. The join helps to fetch the data from multiple database… Read More
in this Laravel tutorial, We'll explore valet, which is a development environment for macOS minimalists. It's a lightweight Laravel development… Read More
I'll go through how to use soft delete in Laravel 10 in this post. The soft deletes are a method… Read More
in this Laravel tutorial, I will explore common practices for using the Laravel Blade template with examples. Blade is a… Read More
View Comments
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..
Hi Rahul,
sure,I will write new artcile for this.
Hey , I have a similar problem , did you find the solution . Can you please send it to redlives26@gmail.com.
Thanks!
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.
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 ?
you can take reference from downloaded sorce code,otherwise you need to sent source code with mysql.
Excelente tutorial.
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!!
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
Thanks for your appreciation, you can use cache to load first time data and again use cache data for this.
i want to datatables with records form backend mysql thay why please post the data using mysql table
In this post you can set static records with your mysql array records set.
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
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.
Hi, this is a good example thank you a lot,
could you please give me and example with the Mysql records
Regards
the source code have used mysql datasource to get results and passed to datatable.
Hi, thank you for the example, i would like to have and example with Mysql.
Regards
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...
I hv posted using mysql as well