DataTables is a powerful jQuery plugin that offers advanced features for tabular data display on a web page. It has pagination, sorting, and filtering features that make managing large amounts of data simple.
It can help you present your data in a way that is both informative and accessible, whether you’re working with simple spreadsheets or complex datasets.
DataTable will automatically adjust data and create pagination with a search and sort feature after you add a data list to the HTML table and initialize it.
There are options available to implement AJAX pagination.
In this tutorial, I show how you can use DataTables with AJAX to implement pagination with search and sort using PHP.
Table of Content
- Create a Table
- Database Configuration
- Download & Include DataTables Library
- HTML Layout for DataTables
- jQuery – Configuring DataTables with AJAX
- PHP – Fetch DataTables Data
- Demo
- Conclusion
1. Create a Table
Create employee table.
CREATE TABLE `employee` ( `id` int(11) NOT NULL PRIMARY KEY AUTO_INCREMENT, `emp_name` varchar(80) NOT NULL, `salary` varchar(20) NOT NULL, `gender` varchar(10) NOT NULL, `city` varchar(80) NOT NULL, `email` varchar(80) NOT NULL );
2. Database Configuration
Create a config.php for the database connection.
<?php
$host = "localhost"; /* Host name */
$user = "root"; /* User */
$password = ""; /* Password */
$dbname = "tutorial"; /* Database name */
$con = mysqli_connect($host, $user, $password,$dbname);
// Check connection
if (!$con) {
die("Connection failed: " . mysqli_connect_error());
}
3. Download & Include DataTables Library
- Download Datatables from Official website.
- Include
datatables.min.cssanddatatables.min.jsin<head>section and also include the jQuery Library. - You can also use CDN.
<!-- Datatable CSS --> <link href='//cdn.datatables.net/1.10.19/css/jquery.dataTables.min.css' rel='stylesheet' type='text/css'> <!-- jQuery Library --> <script src="https://ajax.googleapis.com/ajax/libs/jquery/3.5.1/jquery.min.js"></script> <!-- Datatable JS --> <script src="//cdn.datatables.net/1.10.19/js/jquery.dataTables.min.js"></script>
4. HTML Layout for DataTables
Create a <table id='empTable' class='display dataTable'> and add column name in <thead>.
Completed Code
<!-- Datatable CSS -->
<link href='https://cdn.datatables.net/1.11.5/css/jquery.dataTables.min.css' rel='stylesheet' type='text/css'>
<!-- jQuery Library -->
<script src="https://ajax.googleapis.com/ajax/libs/jquery/3.6.0/jquery.min.js"></script>
<!-- Datatable JS -->
<script src="https://cdn.datatables.net/1.11.5/js/jquery.dataTables.min.js"></script>
<!-- Table -->
<table id='empTable' class='display dataTable'>
<thead>
<tr>
<th>Employee name</th>
<th>Email</th>
<th>Gender</th>
<th>Salary</th>
<th>City</th>
</tr>
</thead>
</table>
5. jQuery – Configuring DataTables with AJAX
Initialize DataTable on <table id='empTable'>.
Within the method pass options to enable server-side processing and send AJAX post request to ajaxfile.php.
In the columns option pass field names that get read when AJAX successfully callback.
Completed Code
$(document).ready(function(){
$('#empTable').DataTable({
'processing': true,
'serverSide': true,
'serverMethod': 'post',
'ajax': {
'url':'ajaxfile.php'
},
'columns': [
{ data: 'emp_name' },
{ data: 'email' },
{ data: 'gender' },
{ data: 'salary' },
{ data: 'city' },
]
});
});
6. PHP – Fetch DataTables Data
Create a ajaxfile.php.
Read the $_POST values and store in variables that are passed by DataTable during AJAX request – draw, start, length, order,columnIndex, column name, order, and search.
Prepare search query if $searchValue is not empty.
Count the total number of records in the employee table.
Count the total number of records with the Search filter from the employee table.
Both the count returns same value on the first time. The difference is when the search value from DataTable then the record count with filter will use to show – number of filtered record from total records in DataTable.
Fetch records from employee table.
Loop on the records and initialize $data Array with an associative array. In the Array, the key will the same as defined in columns option during DataTable initialization.
Prepare $response An array that has a draw, iTotalRecords, iTotalDisplayRecords, and aaData keys.
Return $response JSON format.
Completed Code
<?php
## Database configuration
include 'config.php';
## Read value
$draw = $_POST['draw'];
$row = $_POST['start'];
$rowperpage = $_POST['length']; // Rows display per page
$columnIndex = $_POST['order'][0]['column']; // Column index
$columnName = $_POST['columns'][$columnIndex]['data']; // Column name
$columnSortOrder = $_POST['order'][0]['dir']; // asc or desc
$searchValue = mysqli_real_escape_string($con,$_POST['search']['value']); // Search value
## Search
$searchQuery = " ";
if($searchValue != ''){
$searchQuery = " and (emp_name like '%".$searchValue."%' or
email like '%".$searchValue."%' or
city like'%".$searchValue."%' ) ";
}
## Total number of records without filtering
$sel = mysqli_query($con,"select count(*) as allcount from employee");
$records = mysqli_fetch_assoc($sel);
$totalRecords = $records['allcount'];
## Total number of record with filtering
$sel = mysqli_query($con,"select count(*) as allcount from employee WHERE 1 ".$searchQuery);
$records = mysqli_fetch_assoc($sel);
$totalRecordwithFilter = $records['allcount'];
## Fetch records
$empQuery = "select * from employee WHERE 1 ".$searchQuery." order by ".$columnName." ".$columnSortOrder." limit ".$row.",".$rowperpage;
$empRecords = mysqli_query($con, $empQuery);
$data = array();
while ($row = mysqli_fetch_assoc($empRecords)) {
$data[] = array(
"emp_name"=>$row['emp_name'],
"email"=>$row['email'],
"gender"=>$row['gender'],
"salary"=>$row['salary'],
"city"=>$row['city']
);
}
## Response
$response = array(
"draw" => intval($draw),
"iTotalRecords" => $totalRecords,
"iTotalDisplayRecords" => $totalRecordwithFilter,
"aaData" => $data
);
echo json_encode($response);
7. Demo
8. Conclusion
Make sure that field names should be the same in AJAX response data as defined in columns data during DataTable initialization otherwise field value not be read.
Remove serverMethod option if you want to send GET type AJAX request then you also need to update the AJAX file.
You can also view the PDO version of this tutorial.
If you found this tutorial helpful then don't forget to share.NOTE – If data is not loading and Invalid JSON response message showing then use the browser network tab to debug. Check SQL queries again and return response. If still issue is not resolved then you either comment or mail me your code with SQL file at makitweb@gmail.com so I can check it.
Thank you for the post! It’s the only way I could make AJAX pagination work in DataTables.
Beautiful and handcrafted script. Yogesh wonderful man.
I am in process of complying to PDO, would please advise how i should be able to convert the Fetch records $data = array(); using bindparam or bindvalue for pdo statement.
All is working,
except this line, which i would like to replace with
stmt = con->prepare(select * from employees WHERE 1 ? order by ? ? limit ?, ?);
than bind the values.
Hi Yogesh,
Would you be able to post your original query using PDO and email me please.
if i try i am getting errors. and I am unable to post my query.
The website is not letting us to type the php/pdo code in the reply section of this website
and If you type ‘ single quote or double quote at the beginning, i am getting json error. Please can you advise what can we do to escape or do nothing when ‘ ” are typed.
secondly, would you be able to convert your original server side using PDO please?
Hi Yogesh,
Email sent.
Please have a look at advise.
I get an “invalid JSON response” when tested with +10,000 rows.
With few records works fine.
Any clue?
Very Nice Article , one of the best pagination code on internet,thanks
hi sir ,
Thanks this help me a lot in my project.
but i am having a small problem with this whin i retrive large data i mean data above 5000+ it takes a lot of time to display in the web page . is their any solution on this,
please help.
And Thanks a lot for this Content.
Very Helpful Tutorial
Hy Yogesh hell of script.. I Just want to ask that can we customise the pagination style in datatables???
Hello sir,
how we add this code column filter ? and i want make inner join how can i do these
Thanks for sharing your knowledge!
In Brazil there is nothing like this. Congratulations!
However I am trying to adapt this code to my table without success.
The pager works but the records are not displayed.
What could it be?
thankful
Eu consegui após colocar os mesmos campos em $searchQuery =, porém a primeira página não exibe os registros.
Os registros são exibidos da segunda página em diante.
A busca não está funcionando.
Seria porque minha tabela tem mais de 30 mil registros?
Gostaria de limitar a busca pelos registros da tabela pelo campo DATA_VENDA = curdate(), no entanto não estou conseguindo
Como resolveria isto?
Gratidão
I got it after putting the same fields in $ searchQuery =, but the first page doesn’t display the records.
The records are displayed from the second page onwards.
The search is not working.
Is it because my table has over 30,000 records?
I would like to limit the search for table records by the field DATA_VENDA = curdate (), however I am unable to
How would you solve this?
Gratitude
346/5000
sorry for my inattention
when changing fields in Search
$ searchQuery = “”;
if ($ searchValue! = ”) {
$ searchQuery =
I ended up removing the like from sql.
now everything is working very well
my question now would be just how to limit fetch records
$ empQuery = with field DATA_VENDA = curdate ()
Thanks so much for the quick reply previous
Now it is as I wish.
Thank you!
As an agent says here: only gold!
Hello yogesh, How are you, Am having problem with the search part
$searchQuery = ” and (entry_no like ‘%”.$searchValue.”%’ or recept_no like ‘%”.$searchValue.”%’ or names like ‘%”.$searchValue.”%’ or
middle_name like ‘%”.$searchValue.”%’ or email like ‘%”.$searchValue.”%’ or
surname like’%”.$searchValue.”%’)”;
when i add email on the list to be searched like on the script , it does not work, what could be the issue
In my case the search stopped working when I used inner join in Fetch records.
So I had to use table_name.column_name in Search and it all flowed
Hello yogesh,
How can i use where clause on the query
Greetings!
fellow in ajaxfile.php I put:
“radio” => ” Products ”
How can I put an event like this?
$ (document) .ready (function () {
// define radio field event “onclick” with name prod
$ (“input [name = prod]”). click (function () {
// capture the value of fields from the form
var cod = $ (“input [name = prod]: checked”). val ();
// use the jquery library’s ajax method to post the data to insert.php
$ .ajax ({
“url”: “page.html”,
“dataType”: “html”,
“data”: {
“prod”: prod
},
“success”: function (response) {
// on success, div ID = output gets post response
$ (“div # products2”). html (response);
}
});
$ .ajax ({
“url”: “customers29999.php”,
“dataType”: “html”,
“data”: {
},
“success”: function (response) {
// on success, div ID = output gets post response
$ (“div # customers999”). html (response);
}
});
});
});
Hi Mr. Yogesh,
I want to directly display only male gender. I don’t know where to put the condition.
Can you help me please.
Hi!!
Great script!
i want one of field, like employee name , have a hyperlink to another page … Could you help me?
Thank you!
This post saved my time ..
You’re perfect man.
I love you :))
Thanks
hey , how to add any link colnum to the page so that when we click on that link it redirect to specified page.?
How to make additional column and add button to them
Hi, how to format the response, for example if I want to put text-center on some cell?
Great explanation.
I wish to know how could I pass a variable to the scipt.
For example, I have a dropdown with some channels names and wish to have the returned data from mysql query be about this channel.
I already have the code working but it only gets all the records (wich are big).
I can call a php script when the dropdown changes, but I can’t make it work with server side paging because of the variable channel name.
Could you kindly help me?
Thank you very much
Hi bro thank you so much for this code, i’m learning from you
can you help to fix this problem Warning: mysqli_fetch_assoc() expects parameter 1 to be mysqli_result, bool given. Here :
$empQuery = “select * from sub_events WHERE 1 “.$searchQuery.” order by “.$columnName.” “.$columnSortOrder.” limit “.$row.”,”.$rowperpage;
$empRecords = mysqli_query($dbhandle, $empQuery);
$data = array();
while ($row = mysqli_fetch_assoc($empRecords)) {
The script seems wrong.
“iTotalRecords” => $totalRecordwithFilter,
“iTotalDisplayRecords” => $totalRecords,
these must be inverted
iTotalRecord is total record, iTotalDisplayRecords is filtered record.
Hi, thanks for a great post. Quick question, do you know if it would be possible to add a 2nd search box? In my table, I have a column that has dates. My other columns have names, phone numbers etc. I want to be able to select a date first to show results from that day only and then use the search function to search for other fields. So that I am only looking at data from a particular day. Hope you can give me some ideas how to do that.
DataTables warning: table id=empTable – Invalid JSON response. For more information about this error, please see http://datatables.net/tn/1
This error occurs whenever 50 or 100 records are selected per page. Also, when performing a search and there is more than 50/100 records per page. This error does not occur if 25 or 100 records per page is selected.
code is identical to what you have on this page and is as follows:
$(document).ready(function(){
$(‘#user_data’).DataTable({
‘processing’: true,
‘serverSide’: true,
‘serverMethod’: ‘post’,
‘ajax’: {
‘url’:’fetch.php’
},
‘columns’: [
{ data: ‘invQty’ },
{ data: ‘invManufac’ },
{ data: ‘InvPartNum’ },
{ data: ‘invDescr’ },
{ data: ‘invCost’ },
{ data: ‘invLocation’ },
]
});
//php script
$con = mysqli_connect( ###,###,###,###);
## Read value
$draw = $_POST[‘draw’];
$row = $_POST[‘start’];
$rowperpage = $_POST[‘length’]; // Rows display per page
$columnIndex = $_POST[‘order’][0][‘column’]; // Column index
$columnName = $_POST[‘columns’][$columnIndex][‘data’]; // Column name
$columnSortOrder = $_POST[‘order’][0][‘dir’]; // asc or desc
$searchValue = $_POST[‘search’][‘value’]; // Search value
## Search
$searchQuery = ” “;
if($searchValue != ”){
$searchQuery = ” and (InvPartNum like ‘%”.$searchValue.”%’ or
invDescr like ‘%”.$searchValue.”%’ or
invLocation like’%”.$searchValue.”%’ ) “;
}
## Total number of records without filtering
$sel = mysqli_query($con,”select count(*) as allcount from inventoryComponents”);
$records = mysqli_fetch_assoc($sel);
$totalRecords = $records[‘allcount’];
## Total number of record with filtering
$sel = mysqli_query($con,”select count(*) as allcount from inventoryComponents WHERE 1 “.$searchQuery);
$records = mysqli_fetch_assoc($sel);
$totalRecordwithFilter = $records[‘allcount’];
## Fetch records
$empQuery = “select * from inventoryComponents WHERE 1 “.$searchQuery.” order by “.$columnName.” “.$columnSortOrder.” limit “.$row.”,”.$rowperpage;
$empRecords = mysqli_query($con, $empQuery);
$data = array();
while ($row = mysqli_fetch_assoc($empRecords)) {
$data[] = array(
“invQty”=>$row[‘invQty’],
“invManufac”=>$row[‘invManufac’],
“InvPartNum”=>$row[‘InvPartNum’],
“invDescr”=>$row[‘invDescr’],
“invCost”=>$row[‘invCost’],
“invLocation”=>$row[‘invLocation’]
);
}
## Response
$response = array(
“draw” => intval($draw),
“iTotalRecords” => $totalRecords,
“iTotalDisplayRecords” => $totalRecordwithFilter,
“aaData” => $data
);
echo json_encode($response);
?>
there was a typo in my comment
This error occurs whenever 50 or 100 records are selected per page. Also, when performing a search and there is more than 50/100 records per page. This error does not occur if 25 or 100 records per page is selected.
should read:
This error occurs whenever 50 or 100 records are selected per page. Also, when performing a search and there is more than 50/100 records per page. This error does not occur if “25 or 10” records per page is selected.
I am encountering the same problem now. Were you able to resolve this?
Hello I am testing out the pagination scripts you have available on the website.
$sql2 = “select * from staffdetails JOIN staff on staff.idNum=staffdetails.idNum WHERE 1 “.$searchQuery.” order by “.$columnName.” “.$columnSortOrder.”limit “.$row.”,”.$rowperpage;
For some reason when I join in another table I get a JSON error. I believe the error has something to do with the $columnname and $columnsortorder variable because when I remove it the JSON loads fine. Any help you could give me would be appreciated.
Hi, how enable multiordering on colums?
Thank you very much for the code. Works great, For employee table I have an Employee class , I return an array of Employee objects.
Is there a way to get this array ‘into’ the data table? Thanks Siva
any help me how to fetch particular id in this ajax method???
for example in the table there are 1000 entry available and 3 company data like admin 1 and admin 2 and last admin 3 i want the admin 2 data then how can i get particular admin id data ???
and sorry for the bad English grammar
Wonderful script!!!
My $data Array in ajaxfile.php has this:
“field_70″=>$row[‘field_70’],
It is a URL. Instead of the URL written in the cell, how can I make it say, “Website” and post to that URL when clicked?
Also, how can I post a thumbnail photo (‘m_photo_url’ in my database) inside a column?
Thanks so much!!!!!
Thank you so much Yogesh!
I decided to use your “How to add Custom Filter in DataTable – AJAX and PHP” script, which adds the ability to search columns. Brilliant!
I got it to work, including adding the Website link. Thank you!
I am still unable to add the image. Here’s the problem. All the other information on the page comes from one table (f_member_fields) . The image link (photo_url) is in a different table (f_members).
f_members has (id) as the primary key.
f_member_fields has (member_id) as the primary key.
I tried to combine the 2 tables in a query. I still couldn’t get a thumbnail image to be displayed in the row.
I then tried to get the thumbnail image to popup onmouseover the emp_name in your example. I thought this might even be a better solution, as the size of the image would be bigger. Again, no luck.
I appreciate any help you may offer, Yogesh!
I almost have it, Yogesh!!!
I added the table to $empQuery, adding to the WHERE that the id and member_id were equal to each other. I added the column to the table, and IT WORKS…almost!!! The photo is a tiny box because it has the wrong URL.
The only thing I have to figure out is how to change my data array, img src:
“photo_url”=>””,
I need ” ../folder/ ” added in front of .$row[‘photo_url’] to make it display the photo correctly. It seems so easy, yet I am not formatting it correctly. Any ideas?
It looks like this form does not allow the information beyond the => to display.
Here’s what I have to the right of the arrow:
“”,
Got it! It works perfectly!!!
Excellent script, Yogesh!!! Thank you so much!!!
Thank you
Hi,
This is a nice topic.
I modified the code and am able to fetch data as my responce is having the coreect data.
However, my first page is displaying no data. data for the first page not found.
Then when in subsequent pages show s data for the previous data.
What am doing wrong?
1.I want to display view (not table) on the page in order of ic_id which is in the view but I dont want to display ic_id on the page. How can I achieve these?
2. And also how to customize Specific column and Search bar?
index code:-
$(document).ready(function(){
$(‘#item’).DataTable({
‘processing’: true,
‘serverSide’: true,
‘order’: [[ 0, ‘desc’ ]],
‘serverMethod’: ‘post’,
‘ajax’: {
‘url’:’ajaxfile.php’
},
‘columns’: [
{ data: ‘ic_id’ },
{ data: ‘item_name’ },
{ data: ‘cost’ },
{ data: ‘retail’ },
{ data: ‘quantity’ },
]
});
});
ajaxfile.php code:-
while ($row = mysqli_fetch_assoc($empRecords)) {
$data[] = array(
“ic_id”=>$row[‘ic_id’],
“item_name”=>$row[‘item_name’],
“cost”=>$row[‘cost’],
“retail”=>$row[‘retail’],
“quantity”=>$row[‘quantity’],
);
Hello,
this post helps me a lot.
I have a question regarding the “SearchPane” extension, I have tried a little bit but I haven’t found the right solution in order to show this extension.
The basic setup says:
$(‘#myTable’).DataTable( {
dom: ‘Pfrtip’
} );
But exactly how this code need to be added in your code?
$(document).ready(function(){
$(‘#empTable’).DataTable({
‘processing’: true,
‘serverSide’: true,
‘serverMethod’: ‘post’,
‘searching’: true,
‘ajax’: {
‘url’:’ajaxfile.php’,
‘data’: function(data){
var service_id = $(‘#searchByServiceID’).val();
data.searchByServiceID = service_id;
}
},
‘columns’: [
{ data: ‘id’ },
{ data: ‘phone’ },
{ data: ‘day’ },
…
Thanks a lot.
I got this error – DataTables warning: table id=empTable – Invalid JSON response. For more information about this error, please see http://datatables.net/tn/1
Hi yogesh, how can we add td with some action for each td javascript actions
Receiving DataTables warning: table id=empTable – Invalid JSON response error but see no json_encode($response) in the files provided.
Thanks for sharing this. It is by far the easiest example of DataTables+AJAX that just works right away!
Such a great piece of Code !!! … Thanks
Thank you very much for your helpful article.
Can you please show me how to use an image for each row in this script?
Hi Yogesh,
So when I initially load the page I get “No matching records found” but also shows, “Showing 1 to 10 of 1,619 entries”
When I hit a column header the data then shows and everything works. Any reason nothing would load on the initial page load?
I won’t add my dir structure but checking the processing page alone throws this.
Notice: Undefined index: draw in
Notice: Undefined index: start in
Notice: Undefined index: length in
Notice: Undefined index: order in
Notice: Trying to access array offset on value of type null in
Notice: Trying to access array offset on value of type null in
Notice: Undefined index: columns in
Notice: Trying to access array offset on value of type null in
Notice: Trying to access array offset on value of type null in
Notice: Undefined index: order in
Notice: Trying to access array offset on value of type null in
Notice: Trying to access array offset on value of type null in
Notice: Undefined index: search in
Notice: Trying to access array offset on value of type null in
Warning: mysqli_fetch_assoc() expects parameter 1 to be mysqli_result, bool given in
Hello, I’d like no results to be shown in the table until I start typing in the search box and then the relevant results to be displayed. How would I achieve this please?
Many Thanks in advance for your help
Thanks a lot, buddy.
Yogesh Bhai your script is running successfully. and I got to know how datatables works in core PHP.
Once again Thanks A Lot 🙂
Please help!
How to sort encrypted columns?
Hello,
unfortunately the script does not work with german umlauts, e.g. äöü.
Is there a possibility for this?
Thank you very much.
Bernd
Thank u so much
how to make individual search on every columns ?
I want to pass extra parameter with following jQuery datatable request, how could I pass ?
In my case I select one state and then data for that state come in table, so I want to send state code with following request.
$(document).ready(function(){
$(‘#empTable’).DataTable({
‘processing’: true,
‘serverSide’: true,
‘serverMethod’: ‘post’,
‘ajax’: {
‘url’:’ajaxfile.php’
},
‘columns’: [
{ data: ’emp_name’ },
{ data: ’email’ },
{ data: ‘gender’ },
{ data: ‘salary’ },
{ data: ‘city’ },
]
});
});