Pagination in PHP

Question

I made the pagination available in the code below, following an example here of the stack, but it does not work as I need it to be: display at most 5 records per page and create the other pages. This code counts all the records of the table, let’s say it is 8, from that creates 8 pages and 8 blocks of pages, and would fit 5 in one and 3 in another. I accept suggestions on how to adjust.

$page     = (isset($_GET['pagina'])) ? (int)$_GET['pagina'] : 1;
$limit = 5;
$offset = ($page * $limit) - $limit;
$sql  = "SELECT * FROM registros LIMIT $limit OFFSET $offset";
$resp = mysqli_query($conexao, $sql); 
$res  = mysqli_fetch_assoc($resp);

$sql2 = "SELECT count(*) as count FROM registros";
$resultado = mysqli_query($conexao, $sql2); 
$row  = mysqli_fetch_assoc($resultado);
$total_de_paginas = $row['count'];

echo '<div>
<ul class="pagination pagination-sm pull-right">
<li><a href="#" id="anterior"><<</a></li>';
for($i = 1; $i <= $total_de_paginas; $i++){ ?>
<li><a href="?<?php echo http_build_query(array('page' => $i)) ?>"><?php echo $i ?></a></li>
<?php }   
echo '<li><a href="#" id="próxima">>></a></li>
</ul>
</div> ';

Answer 1

The code was not at all wrong, the problem was that I was using a part of it in the wrong place, followed some tips found on the internet and here in the comments. Below is the final code:

<?php
$page  = (isset($_GET['page'])) ? (int)$_GET['page'] : 1;
$limit = 5;
$ini   = $page * $limit;                                     
$select  = "SELECT * FROM registros limit $ini, $limit";
$result  = mysqli_query($conexao, $select);

while($exibe = mysqli_fetch_assoc($result)){
    echo '<td>' . utf8_encode(strtolower($exibe['nome'])) . '</td>';
}

$sql2 = "SELECT count(*) as count FROM registros";
$resultado = mysqli_query($conexao, $sql2); 
$row  = mysqli_fetch_assoc($resultado);
$total_registros = $row['count'];
$num_paginas     = ceil($total_registros / $limit);
?>
<div>
    <ul class="pagination pagination-sm pull-right">
        <li><a href="index.php?page=<?php echo $num_paginas -1?>" id="anterior"><<</a></li>
        <?php
            for($i = 1; $i <= $num_paginas; $i++){ ?>
                <li><a href="index.php?page=<?php echo $i - 1;?>"><?php echo $i;?></a></li>
        <?php }?>   
        <li><a href="index.php?page=0">>></a></li>
    </ul>
</div> 

Answer 2

I believe that in addition to LIMIT you will need to specify the OFFSET, to indicate from which record you want to bring.

Examples:

• Page 1: LIMIT 5 OFFSET 0
• Page 2: LIMIT 5 OFFSET 5
• Page 3: LIMIT 5 OFFSET 10

The limit will always be equal, the offset will vary, increasing the limit for each advanced page.

Answer 3

By the pagination concepts I use a way to solve your problem would be the implementation of a second parameter in the LIMIT of your query.

Following a logic of reasoning for you to implement, imagine the following situation:

• You have a table with 8 records and want to return 5 records at a time to display on your page. Considering that your page is correctly drawn and able to display the pagination markers correctly use this syntax to search for the records that will be displayed on each page:

  SELECT "fields" FROM "Table" LIMIT "param1", "param2"

In the LIMIT clause param1 will contain the first record of the list that will be used for your query. The param2 contains the amount of records that will be returned from it.

Example: SELECT * FROM pessoas LIMIT 0, 5

With this logic the implementation of paging is simple because on the second page you will return the data by passing to the query the values of "LIMIT 5, 5". Then at each page marker the "param1" of the query can be adjusted to correctly position in the record determined to start the count for your query.