Trabalhando com Paginação de dados no Microsoft SQL Server 2012 e conjunto com a Linguagem PHP

Paginação com SQL Server 2012 e PHPOlá pessoal, meu nome é William sou analista de desenvolvimento WEB esse é meu primeiro post como convidado do Junior Galvão – MVP, pretendo colaborar em outras oportunidades sempre com foco em desenvolvimento com SQL Server e para começar vou demonstrar como podemos construir uma paginação com SQL Server 2012 e PHP.

No meu blog devwilliam.com.br já publiquei uma série de artigos sobre paginação com diversos SGBDs (SQL Server 2008, PostgreSQL e MySQL) em conjunto com a linguagem PHP:

Paginação com SQL Server 2008 R2 e PHP
Paginação com PostgreSQL e PHP
Paginação com MySQL e PHP

Introdução sobre OFFSET e FETCH

Mas esse post com SQL Server 2012 difere da maneira como foi montada a paginação com SQL Server 2008 R2, pois até essa versão era necessário usar CTE (Commo Table Expression) para obtermos a paginação dos dados, já na versão 2012 foram adicionadas as cláusulas OFFSET e FETCH que devem ser usadas sempre em conjunto com a cláusula ORDER BY para paginação.

Essa feature facilitou muito a vida do desenvolvedor quando era necessário paginar um grande volume de registros, principalmente em aplicações WEB, além de possuir um plano de execução menos custoso que as paginações envolvendo CTE.

OFFSET -> Identifica a quantidade de linhas que deverão ser ignoradas antes das linhas que serão retornadas pela consulta, valor inteiro maior ou igual a zero.

FETCH -> Identifica a quantidade de linhas que deverão ser retornadas pela consulta após o processamento da cláusula OFFSET, valor inteiro maior ou igual a zero.

Observação: A cláusula TOP não pode ser combinada na mesma instrução de consulta que utiliza OFFSET e FETCH.

Exemplo, selecionando registros da tabela cliente ordenados pelo campo id, mas serão ignorados as 5 primeiras linhas e serão retornados apenas 2 linhas:

SELECT id, nome, email FROM cliente ORDER BY id ASC OFFSET 5 ROWS FETCH NEXT 2 ROWS ONLY

 

Construindo Paginação com SQL Server 2012 e PHP

Abaixo segue o script para criar o banco de dados “blog” e  a tabela artigos “artigos“:
CREATE DATABASE blog
GO

USE blog

GO  
 
CREATE TABLE `artigos` (  
  id INT IDENTITY PRIMARY KEY,  
  titulo varchar(100),   
  autor varchar(100),  
  previa varchar(200),  
  data date  
)

 

Vamos inserir os 35 registros, não vou usar procedure ou function, no SQL Server podemos usar uma “gambiarra” que funciona bem para esses testes com carga de dados, após a instrução INSERT adicionamos “GO” seguido da quantidade de registros que queremos inserir.

 

INSERT INTO artigos(titulo, autor, previa, data)VALUES('Paginação com SQL Server 2012', 'João', 'Paginação sobre SQL Server 2012 publicada ...', getDate())

GO 35

 

Com isso já temos toda a parte de banco de dados finalizada, agora vamos para o principal, o script da index.php mas agora com toda a lógica PHP de consulta e paginação, para que esse código funcione é importante mencionar que a extensão pdo_sqlsrv.dll (Windows) ou pdo_dblib.so (Linux) tem que estar habilitada no PHP, script segue abaixo:

index.php

<?php 
 /* Constantes de configuração */ 
 define('QTDE_REGISTROS', 5); 
 define('RANGE_PAGINAS', 1); 
 
 /* Recebe o número da página via parâmetro na URL */ 
 $pagina_atual = (isset($_GET['page']) && is_numeric($_GET['page'])) ? $_GET['page'] : 1; 
 
 /* Cria uma conexão PDO com SQL Server */ 
 $pdo = new PDO("sqlsrv:host=localhost; database=blog;", "usuario", "123456"); 

/* Calcula a linha inicial da consulta */  
 $linha_inicial = ($pagina_atual -1) * QTDE_REGISTROS; 
 
 /* Instrução de consulta para paginação com SQL Server 2012 */ 
 $sql = "SELECT id, titulo, autor, previa, CONVERT(VARCHAR(10), data, 103) AS data FROM artigos ORDER BY id ASC OFFSET {$linha_inicial} ROWS FETCH NEXT ".QTDE_REGISTROS." ROWS ONLY";
 $stm = $pdo->prepare($sql); 
 $stm->execute(); 
 $dados = $stm->fetchAll(PDO::FETCH_OBJ); 
 
 /* Conta quantos registos existem na tabela */ 
 $sqlContador = "SELECT COUNT(*) AS total_registros FROM artigos"; 
 $stm = $pdo->prepare($sqlContador); 
 $stm->execute(); 
 $valor = $stm->fetch(PDO::FETCH_OBJ); 
 
 /* Idêntifica a primeira página */ 
 $primeira_pagina = 1; 
 
 /* Cálcula qual será a última página */ 
 $ultima_pagina = ceil($valor->total_registros / QTDE_REGISTROS); 
 
 /* Cálcula qual será a página anterior em relação a página atual em exibição */ 
 $pagina_anterior = ($pagina_atual > 1) ? $pagina_atual -1 : 0 ; 
 
 /* Cálcula qual será a pŕoxima página em relação a página atual em exibição */ 
 $proxima_pagina = ($pagina_atual < $ultima_pagina) ? $pagina_atual +1 : 0 ; 
 
 /* Cálcula qual será a página inicial do nosso range */ 
 $range_inicial = (($pagina_atual - RANGE_PAGINAS) >= 1) ? $pagina_atual - RANGE_PAGINAS : 1 ; 
 
 /* Cálcula qual será a página final do nosso range */ 
 $range_final = (($pagina_atual + RANGE_PAGINAS) <= $ultima_pagina ) ? $pagina_atual + RANGE_PAGINAS : $ultima_pagina ; 
 
 /* Verifica se vai exibir o botão "Primeiro" e "Pŕoximo" */ 
 $exibir_botao_inicio = ($range_inicial < $pagina_atual) ? 'mostrar' : 'esconder'; 
 
 /* Verifica se vai exibir o botão "Anterior" e "Último" */ 
 $exibir_botao_final = ($range_final > $pagina_atual) ? 'mostrar' : 'esconder'; 
 
 ?> 
<!DOCTYPE html>   
<html>   
<head>   
<meta charset='utf-8'>   
<title>Paginação com SQL Server 2012 e PHP</title>   
<link rel="stylesheet" href="https://maxcdn.bootstrapcdn.com/bootstrap/3.3.4/css/bootstrap.min.css">   
<link rel="stylesheet" href="css/estilo.css">   
</head>   
<body>   
<div class='container'>   
   <div class="row">   
     <h1 class="text-center">Paginação de Dados</h1>   
 
     <table class="table table-striped table-bordered">   
      <thead>   
         <tr class='active'>   
           <th>Código</th>   
           <th>Título</th>   
           <th>Autor</th>   
           <th>Prévia</th>   
           <th>Data</th>   
         </tr>   
      </thead>   
      <tbody>   
         <?php foreach($dados as $artigo):?>  
          <tr>   
            <td><?=$artigo->id?></td>   
            <td><?=$artigo->titulo?></td>   
            <td><?=$artigo->autor?></td>   
            <td><?=$artigo->previa?></td>   
            <td><?=$artigo->data?></td>   
          </tr>   
         <?php endforeach; ?>  
      </tbody>   
     </table>   
 
     <div class='box-paginacao'>     
         <a class='box-navegacao <?=$exibir_inicio?>' href="index.php?page=<?=$primeira_pagina?>" title="Primeira Página">Primeira</a>   
         <a class='box-navegacao <?=$exibir_inicio?>' href="index.php?page=<?=$pagina_anterior?>" title="Página Anterior">Anterior</a>     

        <?php 
        for ($i=$range_inicial; $i <= $range_final; $i++):  
            $destaque = ($i == $pagina_atual) ? 'destaque' : '' ;
            ?>  
            <a class='box-numero <?=$destaque?>' href="index.php?page=<?=$i?>"><?=$i?></a>    
        <?php endfor; ?>    

         <a class='box-navegacao <?=$exibir_final?>' href="index.php?page=<?=$proxima_pagina?>" title="Próxima Página">Próxima</a>   
         <a class='box-navegacao <?=$exibir_final?>' href="index.php?page=<?=$ultima_pagina?>" title="Última Página">Último</a>   
     </div>   
   </div>   
</div>   
</body>   
</html>

Observação: A folha de estilo CSS foi postado no artigo Paginação de Dados no PHP com PDO – Introdução.

O script está bem comentado, mas vou explicar os pontos que considero chave para que funcione a paginação com SQL Server 2012 da maneira que planejamos.

1 – Define 2 constantes no início do script, sendo a primeira QTDE_REGISTROS, para identificar quantos registros quero exibir por página, nesse caso apenas 5. Segunda constante RANGE_PAGINAS, serve para identificar quantas opções de páginas para navegação vou exibir antes e depois da página em destaque, nesse exemplo quero apenas 1 página (2 – 3 – 4).
 /* Constantes de configuração */  
 define('QTDE_REGISTROS', 5);   
 define('RANGE_PAGINAS', 1);

 

2 – Nesse trecho fazemos uma validação do parâmetro (page) recebido via GET, verificando se o parâmetro page existe e se o valor é numérico, caso uma das validações não seja verdadeira atribuímos o valor “1” para variável $pagina_atual.
 /* Recebe o número da página via parâmetro na URL */   
$pagina_atual = (isset($_GET['page']) && is_numeric($_GET['page'])) ? $_GET['page'] : 1;

 

3 – Nessa linha estou calculando qual será a linha inicial da consulta, baseado no parâmetro passado via URL e no valor da constante QTDE_REGISTROS.
 $linha_inicial = ($pagina_atual -1) * QTDE_REGISTROS;

 

4 – Nessa linha temos a mágica, com o auxílio das cláusulas OFFSET e FETCH conseguimos montar nossa paginação com SQL Server 2012 através da consulta sendo parametrizada pelos valores recebidos e calculados.
 $sql = "SELECT id, titulo, autor, previa, CONVERT(VARCHAR(10), data, 103) AS data FROM artigos ORDER BY id ASC OFFSET {$pagina} ROWS FETCH NEXT ".QTDE_REGISTROS." ROWS ONLY";

 

5 – O segredo para saber quantas páginas serão necessárias é saber quantos registros serão retornados, podemos fazer isso executando um COUNT(*) na tabela. Com esse resultado basta dividi-lo pela quantidade de registros que iremos exibir por página, nesse caso pela constante QTDE_REGISTROS. Esse valor também serve para sabermos qual será a última página.
 /* Conta quantos registos existem na tabela */  
 $sqlContador = "SELECT COUNT(*) AS total_registros FROM artigos";   
 $stm = $pdo->prepare($sqlContador);   
 $stm->execute();   
 $valor = $stm->fetch(PDO::FETCH_OBJ);

 

6 – Nesse conjunto de linhas fica toda a mágica da paginação, coloquei os nomes das variáveis bem intuitivos. Observem que para deixar o código mais legível estou usando em alguns pontos operadores TERNÁRIOS, para não poluir o código com vários IFs simples. Observem que para calcular a quantidade de páginas basta dividir o total de registros pela constante QTDE_REGISTROS por página $ultima_pagina = ceil($valor->total_registros / QTDE_REGISTROS).
 /* Idêntifica a primeira página */  
 $primeira_pagina = 1;   
 
 /* Cálcula qual será a última página */  
 $ultima_pagina  = ceil($valor->total_registros / QTDE_REGISTROS);   
 
 /* Cálcula qual será a página anterior em relação a página atual em exibição */   
 $pagina_anterior = ($pagina_atual > 1) ? $pagina_atual -1 : 0 ;   
 
 /* Cálcula qual será a pŕoxima página em relação a página atual em exibição */   
 $proxima_pagina = ($pagina_atual < $ultima_pagina) ? $pagina_atual +1 : 0 ;  
 
 /* Cálcula qual será a página inicial do nosso range */    
 $range_inicial  = (($pagina_atual - RANGE_PAGINAS) >= 1) ? $pagina_atual - RANGE_PAGINAS : 1 ;   
 
 /* Cálcula qual será a página final do nosso range */    
 $range_final   = (($pagina_atual + RANGE_PAGINAS) <= $ultima_pagina ) ? $pagina_atual + RANGE_PAGINAS : $ultima_pagina ;

 

7 – O código abaixo utiliza classes que criei no CSS para esconder ou exibir os botões de Primeira, Anterior, Próxima e Última página, conforme a necessidade.
 /* Verifica se vai exibir o botão "Primeira" e "Anterior" */   
 $exibir_botao_inicio = ($range_inicial < $pagina_atual) ? 'mostrar' : 'esconder'; &nbsp;
Paginação Inicial

 

 
 /* Verifica se vai exibir o botão "Próxima" e "Última" */   
 $exibir_botao_final = ($range_final > $pagina_atual) ? 'mostrar' : 'esconder'; &nbsp;
Paginação Final

 

Abaixo temos o resultado final quando acessamos a primeira página, notem que não temos opções para o primeiro e nem registro anterior.
Paginação de dados no início

 

Agora acessando a página “3” temos opções baseadas no range, com 1 página anterior e 1 página posterior, além das opções de primeira, anterior, próxima e última página.
Paginação de dados no meio

 

E para finalizar, acessando a última página “7” temos a opção de exibir uma página anterior “6” e os botões anterior e primeira página.
Paginação de dados no fim

 

Bom pessoal, neste artigo demonstrei como construir uma paginação com SQL Server 2012 e PHP, é evidente que existem diversas maneiras de se montar esse layout de paginação, com variações de CSS, HTML e lógica PHP. Na internet existem diversos plugins para esse tipo de funcionalidade, os principais frameworks para PHP possuem métodos para isso, mas antes de usar soluções prontas é interessante saber como funciona, quais as limitações do SGBD que você está usando em seu projeto para se trabalhar com paginação.

Para quem quiser conhecer meu blog devwilliam.com.br, tenho vários posts sempre ligados a desenvolvimento com Delphi, PHP, jQuery e Linux!

Agradeço mais uma vez ao Junior Galvão – MVP pelo convite, espero que tenham gostado e até a próxima …

Deixe uma resposta

Preencha os seus dados abaixo ou clique em um ícone para log in:

Logotipo do WordPress.com

Você está comentando utilizando sua conta WordPress.com. Sair / Alterar )

Imagem do Twitter

Você está comentando utilizando sua conta Twitter. Sair / Alterar )

Foto do Facebook

Você está comentando utilizando sua conta Facebook. Sair / Alterar )

Foto do Google+

Você está comentando utilizando sua conta Google+. Sair / Alterar )

Conectando a %s