Pagination in PHP

1

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: display a maximum of 5 records per page and thus create the other pages. This code counts all the records of the table, let's say it is 8, from this it creates 8 pages and 8 blocks of pages, which 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> ';
    
asked by anonymous 12.06.2017 / 18:26

3 answers

2

The code was not all wrong, the problem was that I was using a part of it in the wrong place, I followed some tips found on the internet and here in the comments. Here 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> 
    
13.06.2017 / 22:08
1

I believe that in addition to LIMIT you will need to specify 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 the same, the offset will vary, increasing the limit on each advanced page.

    
12.06.2017 / 18:34
1

For paging concepts I use a way to solve your problem would be implementing 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 you want to return 5 records at a time to display on your page. Since your page is correctly drawn and can display the paging markers correctly, use this syntax to fetch the records that will appear on each page:

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

In the LIMIT clause, the param1 will contain the first record in the list that will be used for your query. param2 contains the number of records that will be returned from that.

Example: SELECT * FROM pessoas LIMIT 0, 5

With this logic the implementation of the pagination is simple because in the second page you will return the data 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 the given record to start counting for your query.

    
12.06.2017 / 18:48