How can I paginate these results?

Question

I made an SQL to filter results according to the desired parameters which resulted in the following statement:

$query = $pdo->query("SELECT * FROM imovel ".trim($where, ' AND '));

Whereas I used PDO to mount the $query I have to paginate results in PDO or I can do it in MySQL?

Example of the result of $query:

SELECT * FROM imovel WHERE 1=1 AND CATEGORIA IN ('CASA') AND DORMITORIO IN (2)

I also have to execute the pagination, the number of the pages:

Answer 1

Roughly, PDO is just a function that will send your instruction SQL to the database Mysql.

And paging is done in Mysql, or you can also do it via programming...

Here’s a little example:

<?php

   $page = ((isset($_GET['page']) && intval($_GET['page']) > 1)? intval($_GET['page']) : 1) - 1;

   $limite = 15; // limite de registros por página

   $atual = $page*$limite; // Pula a quantidade de registros até os da página atual

   $limit = " LIMIT {$atual}, {$limite}";

   // Registros limitados
   $query = $pdo->query("SELECT * FROM imovel ".trim($where, ' AND ').$limit); 

   // Todos os Registros para criar as página
   $total = $pdo->query("SELECT count(id) as total FROM imovel ".trim($where, ' AND ')); 

   $qtdtotal = $total['total']; // Pegue a quantidade total
   $qtdpage = ceil($qtdtotal/$limite); // Quantidade de páginas

  // Criando os links
  for ($i = 1; $i<$qtdpage; $i++){

     echo '<a href="paginacao.php?page='.$i.'">'.$i.'</a><br>';

  }

So for programming purposes, in this case, there’s not much difference if you’re using PDO, mysql_* or mysqli::*.

Some links on paging:

Mysql Pagination of Records
Understanding Mysql Log Paging
How to limit the number of pages shown in a pagination?