How can I use the data table pagination?

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?

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).