How to optimize my paging (Datatable) using LIMIT?

Question

all right? I’ve been having some problems for a while, with a table of my system, my table has only 430 records, and whenever I give "F5", perform a INSERT or something like, the "time/delay" table comes back, and it’s been bothering me.

I tried to use Server-Side already, which is a "property" of Datatable, however, I found a little complicated...

I was looking at some topics and sites, of the staff using the function LIMIT in their work, for better performance, but, did not understand how I will do to use the function LIMIT on every page of my Datatable...

Example: (This is my mysql query)

$sql = "SELECT contas.* , estabelecimento_detalhes.* FROM contas, estabelecimento_detalhes WHERE contas.estabelecimento = estabelecimento_detalhes.estabelecimento AND contas.categoria != 'EMPRESA-X' LIMIT 1,10";

In my query, only displays 10 records from my table.

How can I consult only 10 out of 10 records?

I will be providing all the code of my pagination below:

<!-- LISTAGEM  -->  
       <div class="main-content">
              <h3><center><i><a style="font-family: 'Cinzel', serif;"><b>CONTAS PAGAS </b></a></i></center></h3>
                <div class="section__content section__content--p30">
                    <div class="container-fluid">
                        <div class="row">
                            <div class="col-lg-12">



      <table class="table-earning full_table_list" id="dtHorizontalExample" cellspacing="0" width="100%">


        <thead>
            <tr>
                <th class="th-sm">Estabelecimento</th>

                <th class="th-sm">Categoria</th>

                <th class="th-sm">Mês referênte</th>

                <th class="th-sm">Parcela/Valor total</th>

                <th class="th-sm">Valor pago</th>               

                <th class="th-sm">Banco</th>                

                <th class="th-sm">Tipo do Pg</th>

                <th class="th-sm">Pago</th>

                <th class="th-sm">Vencimento</th>

                <th class="th-sm">Nota fiscal</th>

                <th class="th-sm">Nº Documento</th>

                <th class="th-sm">OBS</th>

                <th class="th-sm">Anexo</th>

                <th class="th-sm">Opções</th>

                <th class="th-sm" style="visibility: hidden;"></th>
            </tr>
        </thead>

</div>
</div>
</div>
</div>

</div>
  <tbody>


<?php
$sql="";
require('conexaobd.php');


if (isset($_GET["pesquisa"])) {
$estabelecimento = $_GET["pesquisa"];
$sql = "SELECT * FROM contas WHERE estabelecimento LIKE '%$estabelecimento%'";
}else

$sql = "SELECT contas.* , estabelecimento_detalhes.* FROM contas, estabelecimento_detalhes WHERE contas.estabelecimento = estabelecimento_detalhes.estabelecimento AND contas.categoria != 'EMPRESA R&C'";

$resultado = mysqli_query($link, $sql);

$inc = 0;

while ($cont = mysqli_fetch_array($resultado)) {

    $valor_dividido = $cont['valor'] / 100;

// No primeiro ECHO, estou exibindo o nome com o link do visualizar.
    echo '
    <tr>

    <td><a href="#" data-target="#myModal" data-toggle="modal" title="Ver info" style="color:black">
    '.$cont['estabelecimento'].'</a></td>'; 

    echo "             
    <td style='color:black'>".$cont['categoria']."</td>
    <td style='color:black'>".$cont['mes']."</td>
    <td style='color:black'>".$cont['parcela']."</td>
    <td style='color:black'> R$ ".number_format($valor_dividido, 2, ',', '.')."</td>
    <td style='color:black'>".$cont['banco']."</td>
    <td style='color:black'>".$cont['tipo_pg']."</td>
    <td style='color:black'>".converte_data($cont['pagamento'])."</td> 
    <td style='color:black'>".converte_data($cont['vencimento'])."</td> 
    <td style='color:black'>".$cont['nota_fiscal']."</td>
    <td style='color:black'>".$cont['n_documento']."</td>
    <td style='color:black'>".$cont['obs']."</td>";

// Exibindo comprovante com icone
echo $cont['comprovante'] != "" ? "<td><a href='ver-arquivo.php?documento=uploads_pagamentos/{$cont['comprovante']}' title='{$cont['comprovante']}' target='_blank'><img src='images/icon/comprovante.png' style='width:30px; height:30px;cursor:pointer;'></a></td>" : "<td></td>";

    echo "     
    <td> 
    <div class='table-data-feature'>

    <a href='edit_contas.php?ID_contas=".$cont['ID_contas']."' target='new_blank' title='Editar' style='color:black'>
    <i class='zmdi zmdi-edit' style='margin: 0 8px'></i>
    </a>

    <a href='del_contas.php?ID_contas=".$cont['ID_contas']."&categoria=".$cont['categoria']."&pagamento=".$cont['pagamento']."' target='new_blank' title='Excluir' confirm='Tem certeza que deseja deletar essa conta?' style='color:black'>
    <i class='zmdi zmdi-delete' style='margin: 0 8px'></i></a>

    </div>
    </td>


    <td style='display: none;'>".json_encode($cont)."</td>

    </tr>";

          } ?>  

    </tbody>
</table>

I am new to programming, I accept any kind of help/ tip, I appreciate.

Answer 1

What @Ricardo Pontual commented makes sense and is the correct way. You need to update your OFFSET, not the limit.

OFFSET is a offset that starts at 0.

Example:

-- se desloca da primeira linha pelas próximas 10
select
    *
from minha_tabela
limit 10 offset 0 

-- se desloca da 11ª linha pelas próximas 10
select
    *
from minha_tabela
limit 10 offset 10 

To show 10 to 10 in your HTML, you only need a simple arithmetic: every time someone clicks on a pagination number, for example, you query the database, multiplying OFFSET 10 by that number minus 1.

The link with number 1 (page 1), for example, would send OFFSET {10 * (1 - 1)}; The link with the number 2 (page 2) would send OFFSET {10 * (2 - 1)};

Of course you could put the page count from 0 on the link’s HREF too, then just multiply that number by the OFFSET.

OFFSET can also be changed. Know those dropdown which inform how many records per time you want to display? Well, it’s OFFSET. 10, 25, 50, 100 records per page.