Now I’m calling up the whole data and using the data table to express it in a view. However, if there is a lot of data, rendering speed is slow. What should we do to improve?
in this datatable code
<script>
$(function() {
'use strict';
$('#datatable1').DataTable({
responsive: true,
ordering: false,
language: {
searchPlaceholder: 'Search...',
sSearch: '',
lengthMenu: '_MENU_ items/page',
}
});
</script>
in this sql code and table view code
$sql = "SELECT * FROM hospital_payment_data where store_mbrno = '1' ";
if($result = mysqli_query($link, $sql)){
if(mysqli_num_rows($result) > 0){
// echo "<table id='datatable1' class='table display responsive nowrap' style='width: 100%;'>";
echo "<table id='datatable1' class = table style = 'width: 100%; font-size:12.3px; font-family:nanum; background-color:#01b3ed;'>";
echo "<thead >";
echo "<tr>";
echo "<th>No</th>";
echo "<th>chart_num</th>";
echo "</tr>";
echo "</thead>";
echo "<tbody>";
while($row = mysqli_fetch_array($result)){
echo "<tr>";
echo "<td>" . $row['id'] . "</td>";
echo "<td>" . $row['chart_num'] . "</td>";
}
echo "</tbody>";
echo "</table>";
// Free result set
mysqli_free_result($result);
}
What should I do to pagination using datatable and php and mariadb?
Advertisement
Answer
Make limit of the results like:
$sql = "SELECT * FROM hospital_payment_data
WHERE store_mbrno = '1'
LIMIT 100 OFFSET ".$offset_var;
That way you will return 100 results per page. The variable $offset_var can be passed through URL like a GET variable (of course prior the SQL query):
$offset_var = $_GET['offset']??0;
And than you can use it for example for hyperlinks for your pagination.
You should of course not forget to sanitize this variable. Something like:
$offset_var = (int)$offset_var;
if($offset_var<0) $offset_var=0;
And also think about the eventual case when somebody hits the page with a big offset number in the url (make sure this “empty” page still renders pretty).