how do I page other results

Question

I saw this code and used it in my project, and it worked, but it only displayed the 10 results to which it was limited. I would like to know how to paginate for the other results using php Pdo.

<?php
echo'<table width="88%" height="10" cellspacing="0" cellpadding="0"><tr>';
$conn = new PDO("mysql:host=localhost;dbname=loja", "root", "");
$stmt = $conn->prepare("SELECT * FROM `produtos` ORDER BY `id` ASC LIMIT 10");
$stmt->execute( );
$linha = $stmt->fetch(PDO::FETCH_ASSOC);
while($linha = $stmt->fetch(PDO::FETCH_ASSOC)) {
    echo'<br>';
    echo'<p><td align=center ><h2><font size="5" face="Trebuchet MS">'.$linha['nome'].'</font></font></p></h2>';
    echo "<div align=center ><img src='".$linha['foto']."' width='160' height='160' border='0'></p>";
    echo '<a href="carrinho.php?acao=add&id='.$linha['id'].'"><button type="button" name="" value="" class="quero">Eu Quero!</button>';
}
echo "</tr></table>";
?>

Answer 1

I didn’t quite understand your question! But I’ll show you an example I did to paginate a table, so you follow the logic of what you need.

// esse trecho seta as configurações
    <?php 
      include 'conexao.php'; //coloque sua conexão aqui caso tenha ela em outra classe php! caso não tenha, não esqueça de fazer a conexão.

$query = "SELECT ID_EQUIPE, NOME, RESPONSAVEL, UF, CIDADE, STATUS FROM Equipe";
    $page = 1;
    if(!empty($_GET['page'])) {
        $page = filter_input(INPUT_GET, 'page', FILTER_VALIDATE_INT);
        if(false === $page) {
            $page = 1;
        }   
    // items por pagina
    $items_per_page = 10;

    // query
    $offset = ($page - 1) * $items_per_page;
    $sql = "SELECT ID_EQUIPE, NOME, RESPONSAVEL, UF, CIDADE, STATUS FROM Equipe LIMIT " . $offset . ", " . $items_per_page;

    $resultado = mysqli_query($conn, $sql); 
    ?>

After your div or where you want to display the pages you should put:

<?php
 $sqls = "SELECT ID_EQUIPE FROM Equipe";

 $result = mysqli_query($conn, $sqls);
if(false === $result) {
   throw new Exception('Query failed with: ' . mysqli_error());
} else {
   $row_count = mysqli_num_rows($result);
   // free the result set as you don't need it anymore
   mysqli_free_result($result);
}

$page_count = 0;
if (0 === $row_count) {  
    // maybe show some error since there is nothing in your table
} else {
   // determine page_count
   $page_count = (int)ceil($row_count / $items_per_page);
   // double check that request page is in range
   if($page > $page_count) {
        // error to user, maybe set page to 1
        $page = 1;
   }
}

// make your LIMIT query here as shown above
// later when outputting page, you can simply work with $page and $page_count to output links
// for example
for ($i = 1; $i <= $page_count; $i++) {
   if ($i === $page) { // this is current page
     //  echo '' . $i . '';
     echo '
       <div class="pagination">
       <a class="active" href="/equipes.php?page=' . $i . '">' . $i . '</a> 
       </div>
       ';
   } else { // show link to other page   
       //echo '<a href="/equipes.php?page=' . $i . '">Page ' . $i . '</a><br>';
       echo '
       <div class="pagination">
       <a href="/equipes.php?page=' . $i . '">' . $i . '</a> 
       </div>
       ';
   }
}
?>

Try this to create the Table: NOTE: $name = $data['NAME']; the item within [''] should be exactly like in the database! Both the letters Uppercase and the minuscule.

<table id="tabelaEquipe" class="table table-borderless table-striped table-earning">
         <thead>
            <tr>
               <th>Equipe</th>
               <th>Responsável</th>
               <th>Cidade</th>
               <th class="text-right">UF</th>
               <th class="text-right"></th>
            </tr>
         </thead>


          <?php 
while($dados = mysqli_fetch_array($resultado)){
  $ID_EQUIPE= $dados['ID_EQUIPE'];
  $nome = $dados['NOME'];
  $responsavel = $dados['RESPONSAVEL'];
  $uf = $dados['UF'];
  $cidade = $dados['CIDADE'];
  $status = $dados['STATUS']; ?>

  <tbody>
    <tr>
      <td><?php echo $nome ?></td>
      <td><?php echo $responsavel?></td>
      <td><?php echo $cidade?></td>
      <td class="text-right"><?php echo $uf?></td>
     <?php  echo "<td class='text-right'><a href='editar-equipe.php?id=".$dados['ID_EQUIPE']."'>Visualizar Dados</td>";?>
    </tr> 
 <?php }
  ?>

         </tbody>
      </table>

I hope I helped! Good studies.