How can i load a set number of rows from a table on pageload and only load further rows when the user loads them?
I have a table using DataTables , it contains a large number of rows and so this causes the page to load very slowly as i assume the browser waits till the table is filled before displaying the page.
I would like to only load one page of the table (10 rows), and only show further data when the user browses through the table, showing a loading sign would be great too.
I have researched and heard of a DataTables function called 'deferRender' which is supposed to do what i need, but i can't get it to work with my table.
My table has 8 columns + the html is generated using PHP that builds the table from data in a text file:
<?php
$tdcount = 1; $numtd = 8; // number of cells per row
$str = "<table id=\"table1\" class=\"table1 table table-striped table-bordered\">
<thead>
<th>1</th>
<th>2</th>
<th>3</th>
<th>4</th>
<th>5</th>
<th>6</th>
<th>7</th>
<th>8</th>
</thead>
<tbody>
";
$f = fopen("tabledata.txt", "r");
if ( $f === FALSE ) {
exit;
}
while (!feof($f)) {
$arrM = explode(",",fgets($f));
$row = current ( $arrM );
if ($tdcount == 1)
$str .= "<tr>"; $str .= "<td>$row </td>";
if ($tdcount == $numtd) {
$str .= "</tr>";
$tdcount = 1;
} else {
$tdcount++;
}
}
if ($tdcount!= 1) {
while ($tdcount <= $numtd) {
$str .= "<td> </td>"; $tdcount++;
} $str .= "</tr>";
}
$str .= "</tbody></table>";
echo $str;
I then use the following code to turn it into a datatable:
<script>
$(document).ready(function() {
$('#table1').basictable({
forceResponsive: false
});
$('#table1').DataTable( { "order": [[ 0, "desc" ]] } );
});
</script>
I have read the instructions here: https://datatables.net/examples/server_side/defer_loading.html and know i need to add parameters to the JS:
"processing": true,
"serverSide": true,
"ajax": "scripts/server_processing.php",
"deferLoading": 57
and use a server_processing script, however the example only shows how to use it when connecting to a DB, and not when the data is loaded from a text file with php.
How can i acheive this?
Solution 1:
This will focus purely on the DataTables aspects of a "server-side" solution. How you write the server-side logic needed to support it is out of scope for this answer. But I hope these notes will at leasst clarify what that logic needs to be, and how you can approach it.
Assumptions
Assume you have a text file containing 1,000 rows of data like this (or a million - but too many rows to send to the browser and to DataTables, all at once). The text file is a simple pipe-delimited file, with three fields:
id|name|description
1|widget_1|This is a description for widget 1
2|widget_2|This is a description for widget 2
3|widget_3|This is a description for widget 3
...
1000|widget_1000|This is a description for widget 1000
You want to send 10 items at a time to DataTables, using server-side processing.
Your data maps to a simple JSON structure, like this - an array of objects (each object is one record):
[
{
"id": 1,
"name": "widget_1",
"description": "This is a description for widget 1"
},
{
"id": 2,
"name": "widget_2",
"description": "This is a description for widget 2"
},
... // more records...
]
The DataTable Definition
Your datatable definition looks like this - it is deliberately very simple, at this stage:
<body>
<div style="margin: 20px;">
<table id="demo" class="display dataTable cell-border" style="width:100%">
</table>
</div>
<script type="text/javascript">
$(document).ready(function() {
$('#demo').DataTable({
serverSide: true,
ajax: {
url: 'http://localhost:7000/data',
type: 'POST'
},
columns: [
{ title: 'ID',
data: 'id' },
{ title: 'Name',
data: 'name' },
{ title: 'Description',
data: 'description' }
]
});
});
</script>
</body>
Initial Response
When the web page is first displayed, it will send an initial POST request to the URL (http://localhost:7000/data), and it will expect to receive a JSON response from the web server, containing the data to be displayed.
Because DataTables is using serverSide: true, DataTables will expect the JSON to have a specific structure, as described here.
Specifically, the server has to add all of the mandatory fields (draw, recordsTotal, recordsFiltered, and data) to the JSON it sends to DataTables.
In our case it would look like this - note that it is just our previously mentioned JSON structure, with a few extra metadata fields added:
{
"draw": 1,
"recordsTotal": 1000,
"recordsFiltered": 1000,
"data": [{
"id": 1,
"name": "widget_1",
"description": "This is a description for widget 1"
}, {
"id": 2,
"name": "widget_2",
"description": "This is a description for widget 2"
}, {
"id": 3,
"name": "widget_3",
"description": "This is a description for widget 3"
}, {
"id": 4,
"name": "widget_4",
"description": "This is a description for widget 4"
}, {
"id": 5,
"name": "widget_5",
"description": "This is a description for widget 5"
}, {
"id": 6,
"name": "widget_6",
"description": "This is a description for widget 6"
}, {
"id": 7,
"name": "widget_7",
"description": "This is a description for widget 7"
}, {
"id": 8,
"name": "widget_8",
"description": "This is a description for widget 8"
}, {
"id": 9,
"name": "widget_9",
"description": "This is a description for widget 9"
}, {
"id": 10,
"name": "widget_10",
"description": "This is a description for widget 10"
}]
}
It is the server's responsibility to build this JSON - the first 10 records of the server's data set. The server also tells DataTables that it has a total of 1,000 records, and that it has not filtered out any data (yet) - hence there are also a total of 1,000 records after filtering.
DataTables needs all of this information, so it knows how many pagination buttons to display, and what pagination data to show.
Note that it is entirely the server's responsibility to do all this work - that's why it's called "server-side" processing.
The client (browser) only has 10 records to render - so that happens quickly.
(I just noticed that the screenshot mentions "500 records" - that's a mistake in my server-side code - there is no filter, so I need to fix that).
Subsequent Requests
When a user clicks on a page navigation button (e.g. page "4"), that triggers a new request from DataTables to the server. DataTables builds this request automatically, using the fields described here.
The request is sent as form data.
In our example, the request looks like this:
"Form data": {
"draw": "5",
"columns[0][data]": "id",
"columns[0][name]": "",
"columns[0][searchable]": "true",
"columns[0][orderable]": "true",
"columns[0][search][value]": "",
"columns[0][search][regex]": "false",
"columns[1][data]": "name",
"columns[1][name]": "",
"columns[1][searchable]": "true",
"columns[1][orderable]": "true",
"columns[1][search][value]": "",
"columns[1][search][regex]": "false",
"columns[2][data]": "description",
"columns[2][name]": "",
"columns[2][searchable]": "true",
"columns[2][orderable]": "true",
"columns[2][search][value]": "",
"columns[2][search][regex]": "false",
"order[0][column]": "1",
"order[0][dir]": "asc",
"start": "30",
"length": "10",
"search[value]": "",
"search[regex]": "false"
}
These fields tell the server everything it needs to know, so it can prepare the correct response.
In our case the most important fields are these:
"start": "30",
"length": "10"
Start at row 30, and provide 10 records.
It is, again, the server's responsibility to prepare a JSON response which accurately reflects the requested data.
In our case this means the server needs to have logic to read through the text file to the correct starting point (data row 31 - remember the offset starts at zero), and 10 rows in total (rows 31 through 40).
Other fields in the above request from DataTables describe how the data is to be sorted, and filtered. In our case there is no filter "search[value]": "", - and the data is to be sorted by the first column in ascending order.
Final Notes
I have deliberately not described the following:
1) How your server-side code handles the creation of the JSON responses it sends back to DataTables;
2) How your server-side code parses the form requests it receives from DataTables.
That all depends entirely on what your server-side technology is. DataTables doesn't care. It's just passing JSON messages - it is decoupled from the server-side implementation - as it should be.
Regarding the "defer render" option described here, that is an enhancement you may choose to add if you feel you need it. But I would recommend getting a more basic server-side implementation working first.