CODEWITHSUNDEEP
htmlservletsdatatablesdatatables-1.10
Parag Jadhav
Parag Jadhav

Reputation: 1899

JQuery: Serverside Pagination Does Not Work (Datatable)

I want to display roughly 20K (and counting) rows in a table. I am using Datatable to achieve this. Now to display so many data fetched from the server, I'll have to send the data in batches.

So to suite my need I decided to use server side processing provided by Datatables.

I'm using:

Datatables version: 1.10.10

below is the gist of my client side and server side JSON response format.

HTML Code

<html>
<head>
<meta http-equiv="Content-Type" content="text/html; charset=ISO-8859-1">
<link rel="stylesheet" href="//cdn.datatables.net/1.10.10/css/jquery.dataTables.min.css"/>
<script src="//code.jquery.com/jquery-1.11.3.min.js"></script>
<script src="//cdn.datatables.net/1.10.10/js/jquery.dataTables.min.js">
<title>View Shop</title>
<style type="text/css">
th, td { white-space: nowrap; }
div.dataTables_wrapper {
/* width: 600px; */
margin: 0 auto;
}

th, td {
padding-left: 40px !important;
padding-right: 40px !important;
}
</style>
</head>
<body>
<a id="append" href="#">Refresh</a><hr/>
<table class="stripe row-border order-column" id="example" cellspacing="0">
<thead>
<tr>
<td>Shop ID </td>
<td>Shop Owner Name </td>
<td>Shop Name </td>
<td>Category of Shop </td>
<td>Type of Shop </td>
<td>Contact Number </td>
<td>Shop Email Id </td>
<td>Shop Address </td>
<td>Shop Postal Code </td>
<td>Shop Drug License Number </td>
<td>Shop VAT TIN Number </td>
<td>Shop CST Number </td>
<td>Shop PAN Card Number </td>
<td>Preferred for Inshop </td>
<td>Route Name </td>
<td>City Name </td>
<td>District Name </td>
<td>Headquarter Name </td>
<td>Region Name </td>
<td>State Name </td>
<td>Country Name </td>
<td>Shop Master is Deleted </td>
<td>User Name </td>
<td>Created Date </td>
<td>Created Time </td>
</tr>
</thead>
</table>
</body>
<script type="text/javascript">
$( document ).ready(function() {
$('#append').click(function(){
$('#example').DataTable({
"processing": true,
"scrollY" : "450px",
"scrollX": true,
"scrollCollapse": true,
"serverSide": true,
"ajax": {
"url": "/ViewShopsPoc?row_limit=0",
"type": "POST"
},
"columns": [
{ mData: 'shop_id' } ,
{ mData: 'shop_owner_name' },
{ mData: 'shop_name' },
{ mData: 'category_of_shop' },
{ mData: 'type_of_shop' },
{ mData: 'contact_number' },
{ mData: 'shop_email_id' },
{ mData: 'shop_address' },
{ mData: 'shop_postal_code' },
{ mData: 'shop_drug_license_no' },
{ mData: 'shop_vat_tin_number' },
{ mData: 'shop_cst_number' },
{ mData: 'shop_pan_card_number' },
{ mData: 'preferred_for_inshop' },
{ mData: 'route_name' },
{ mData: 'city_name' },
{ mData: 'district_name' },
{ mData: 'head_quarter_name' },
{ mData: 'region_name' },
{ mData: 'state_name' },
{ mData: 'country_name' },
{ mData: 'shop_is_deleted' },
{ mData: 'user_employee_name' },
{ mData: 'shop_created_date' },
{ mData: 'shop_created_time' }
]
});
});
});
</script>
</html>

Server Side JSON Response (For testing purpose I'm only returning 100 rows)

{
"draw":1,
"recordsFiltered":100,
"recordsTotal":100,
"data":[
{
"shop_created_time":"No Time",
"city_name":"city1",
"shop_created_date":"2016-02-24",
"shop_pan_card_number":"PAN",
"head_quarter_name":"hq1",
"state_name":"state1",
"country_name":"country1",
"shop_cst_number":"CST",
"region_name":"region1",
"route_name":"route1",
"shop_vat_tin_number":"VATTIN",
"shop_is_deleted":"Yes",
"shop_id":"153",
"type_of_shop":"Medical",
"preferred_for_inshop":"Yes",
"shop_email_id":"example@xyz.com",
"user_employee_name":"Test User",
"shop_address":"Address1",
"shop_owner_name":"New Shop Owner",
"shop_drug_license_no":"DRUGLISC",
"shop_name":"New Test 2 Shop - Deleted",
"contact_number":"123456789",
"district_name":"district1",
"shop_postal_code":"123456",
"category_of_shop":"A"
}
]
}

I am able to load data in tables, but the problem is all the data is loaded on single screen as opposed to it should have been loaded in pages. Below the screenshot of the table which shows how the data is loaded into tables.

Table Image

Question 1: How do I enable the paging in a table?

Question 2: I want to load the all the data from MySQL to a datatable, so do I need to do multiple ajax calls or is there another way to load data in parts using datatable.

I have also referred the following links but no luck:

https://datatables.net/forums/discussion/32031/pagination-with-server-side-processing#Comment_86438

https://datatables.net/manual/server-side

https://datatables.net/faqs/#Server-side-processing

https://datatables.net/manual/server-side#top

https://datatables.net/examples/server_side/simple.html

http://refreshmymind.com/datatables-dom-php-ajax-mysql-datasources/

http://phpflow.com/jquery/data-table-table-plug-in-for-jquery/

Can anyone please help me? Any help is appreciated.

Upvotes: 2

Views: 4692

Answers (2)

Chris H.
Chris H.

Reputation: 2594

Basically, you want to have 10 records on each page, with a total of 100 records (so 10 pages). When you use server side processing, however, DataTables will display every record in the JSON on the current page; in server side processing mode DataTables is just a dumb display; all of the features (including pagination) have to be handled on your server.

This means that instead of sending 100 rows to DataTables and expecting it to page, what should be happening is you send 10 rows of data (while still leaving recordsTotal and recordsFiltered at the total number of rows (100)), and then whenever the user clicks the [next page] button, a new request is sent to your server, where the start parameter would be increased (see the documentation for SSP for more details on the JSON sent to your server). Then you would send 10 more rows starting at that start point.

As an example, lets say that you wanted to click on page 4. DataTables will send JSON to your server that includes a start parameter of 40. Your serverside code gets the 10 rows starting at index 40, and returns them to DataTables (with all the other server side parameters necessary in the documentation).

This means that your first data sent to DataTables should have recordsTotal and recordsFiltered of 100, but that you should only send the first 10 rows of data.

Currently you're sending all the rows and expecting pagination to be handled client side, where you need to ONLY send exactly what is needed for the current page of the DataTable.

Upvotes: 3

Brian Kates
Brian Kates

Reputation: 488

Question 1: Paging is there. At the bottom of the table on the right, I see buttons to go from page 1 to 10. The problem might be your server side response. I see that you're returning 100:

"recordsFiltered":100,
"recordsTotal":100,

Datatables thinks that there are 100 total records. Try setting that to the actual total number of records (20000)

Question 2: With server side processing you would need to make multiple calls to your database. So e.g. if 10 records are diplayed per page and you're on page 5, you would want to get 10 records starting at 50.

Upvotes: 2

Related Questions