How do I pagination using query limit, offset, and jquery datatable?

I made a page using the data table. However, as the number of data increases, the speed of data loading slows down, so we try to proceed paging. But I don’t know how to modify it in the current code. How should it be modified?

in this my datatable scipt

 $(function() {

          $('#datatable1').DataTable({
            responsive: true,
            order: [[ 1, "desc" ], [ 2, "desc"]],
            language: {


              searchPlaceholder: 'Search...',
              sSearch: '',
              lengthMenu: '_MENU_ items/page',
            },
            dom : 'Bfrtip',

            buttons: [
                'copy', 'excel', 'print'
            ]
          });
        });

in this sql query

    $sql = "SELECT id,  DATE_FORMAT(tranDate,'%Y-%m-%d') as tranDate, tranTime, date, cmd, paymethod, amount, orgRefNo, orgTranDate FROM noti_card_data  WHERE  mbrNO = '1'";

in this table view code


    if($result = mysqli_query($link, $sql)){               

            if(mysqli_num_rows($result) > 0){

                echo "<table id='datatable1' class = table style = 'width: 100%; font-size:12.5px; font-family:nanum; background-color:transparent;'>";
                echo "<thead >";
                echo "<tr>";
                echo "<th>No</th>";
                echo "<th>amount</th>";
                echo "</tr>";
                echo "</thead>";

                echo "<tbody>";
            while($row = mysqli_fetch_array($result)){
                echo "<tr>";
                echo "<td>" . $row['id'] . "</td>";
                echo "<td>" . number_format($row['amount']) . "</td>";
                echo "</tr>";
            }
                echo "</tbody>";                            
                echo "</table>";
                // Free result set
                mysqli_free_result($result);                                  
            } 

            else{

            }
        }     

    else{
    echo "ERROR: Could not able to execute $sql. " . mysqli_error($link);
    } 

    // Close connection
    mysqli_close($link);   

in this view

how to using pagination

Answer

You can achieve this by getting the data sets that you need for the current page that your pagination is in instead of getting all the data sets that your query returns.

For ex:

Imagine you are in page 1 in the pagination.And if you need just 100 items per a page then you can limit the items that your query retrieves to 100 and by doing that it will make more faster in retrieving data since instead of getting every data set from the query you are just getting 100 to display in the current page.

You have to pass the page number in the url. imagine your url is like something that is mentioned below.

http://localhost/pagination.php?page=2

  // getting the current page to set the starting point to get data.
    if (isset($_GET['page'])) {
      $curPage = $_GET['page'];
    } else {
      $curPage = 1;
    }

    //number of items you need per a page
     $labels_per_page = 100;

     //setting the starting point according to the page no.                  
 if($curPage>1){
     $start = ($curPage-1)*$labels_per_page;
  }else{
     $start = 0;
  }                     

//limiting the query according to the starting point nd items per page
$sql = "SELECT id,  DATE_FORMAT(tranDate,'%Y-%m-%d') as tranDate, tranTime, date, cmd, paymethod, amount, orgRefNo, orgTranDate FROM noti_card_data  WHERE  mbrNO = '1' LIMIT ".$start .','.$labels_per_page ;

result view

I want view