02 maio 2015
Paginação com MySQL no PHP
Olá pessoal, seguindo com a série de artigos sobre paginação de dados com PHP + PDO, segue abaixo como construir paginação com MySQL 5.5. Atualmente grande parte das páginas WEB de pequena e média escala estão rodando sobre um banco de dados MySQL, por esse motivo existe bastante material sobre paginação com ele, além do MySQL possuir comandos bem intuitivos para se trabalhar com paginação de dados.
No MySQL vamos adicionar a clausula LIMIT X, Y no final da nossa instrução SQL, esse comando recebe dois parâmetros, sendo o primeiro obrigatório e o segundo opcional.
Se for informado apenas 1 parâmetro a pesquisa irá retornar apenas a quantidade de registros de acordo com valor passado, no exemplo abaixo somente os primeiros “10” registros:
1 |
SELECT * FROM tabela LIMIT 10 |
Se forem informados os 2 parâmetros, o primeiro indica em qual linha vai iniciar a consulta e o segundo parâmetro quantos registros serão retornados a partir da linha indicada no primeiro parâmetro. No exemplo abaixo a consulta vai retornar registros começando pela linha “0” (não tem relação com campos ID) com os próximos “10” registros. Serão retornados 10 registros no total, mas conseguimos informar a partir de onde será iniciado essa contagem, é por aí que conseguimos parametrizar a consulta e paginar os dados, abaixo exemplo de paginação com MySQL.
1 |
SELECT * FROM tabela LIMIT , 10 |
Observação: Essa parametrização da clausula LIMIT a partir de consultas executadas em outras linguagens (PHP, Java, C# e etc) foi adicionada a partir da versão 5.5 do MySQL, na versões anteriores somente era possível isso utilizando prepared statements em procedures.
Agora que já sabemos basicamente como funciona a clausula LIMIT, é hora de por a mão na massa criando o banco de dados, tabela e inserindo os dados fictícios.
Abaixo segue o script para criar o banco de dados “blog” e a tabela artigos “artigos“:
1 2 3 4 5 6 7 8 9 10 11 12 13 |
CREATE DATABASE IF NOT EXISTS `blog` USE `blog`; DROP TABLE IF EXISTS `artigos`; CREATE TABLE `artigos` ( `id` int(11) NOT NULL AUTO_INCREMENT, `titulo` varchar(100) DEFAULT NULL, `autor` varchar(100) DEFAULT NULL, `previa` varchar(200) DEFAULT NULL, `data` date DEFAULT NULL, PRIMARY KEY (`id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8; |
Agora que já temos o banco e a tabela criados vamos inserir alguns registros, para podermos explorar todas as funcionalidades da paginação vamos inserir 35 registros, onde cada página vai conter 5 registros, totalizando 7 páginas. Vou usar uma procedure para executar vários INSERTs, assim fica mais fácil de realizar a inclusão dos 35 registros, lembrando que os dados em si não tem importância, a parte que nos interessa é a quantidade de registros.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 |
/* Altera o delimitador do MySQL para não dar conflito com o ";" */ DELIMITER $$ /* Deleta a procedure caso ela exista */ DROP PROCEDURE IF EXISTS insertTemp$$ /* Cria procedure de inserção */ CREATE PROCEDURE blog.insertTemp() BEGIN DECLARE i INT DEFAULT 1; WHILE i<=35 DO INSERT INTO artigos (titulo, autor, previa, data)VALUES(concat("Paginação com MySQL ", i), "João", "Paginação sobre MySQL publicada ...", CURRENT_DATE()); SET i=i+1; END WHILE; END$$ /* Volta o delimitador padrão do MySQL ";" */ DELIMITER ; /* Executando a procedure */ Call insertTemp(); |
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_mysql.dll (Windows) ou pdo_mysql.so (Linux) tem que estar habilitada no PHP, script segue abaixo:
Criando script de paginação com MySQL
index.php
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 |
<?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; /* Calcula a linha inicial da consulta */ $linha_inicial = ($pagina_atual -1) * QTDE_REGISTROS; /* Cria uma conexão PDO com MySQL */ $opcoes = array(PDO::MYSQL_ATTR_INIT_COMMAND => 'SET NAMES UTF8'); $pdo = new PDO("mysql:host=localhost; dbname=blog;", "root", "123456", $opcoes); /* Instrução de consulta para paginação com MySQL */ $sql = "SELECT id, titulo, autor, previa, DATE_FORMAT(data, '%d-%m-%Y') AS data FROM artigos LIMIT {$linha_inicial}, " . QTDE_REGISTROS; $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 : ; /* 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 : ; /* 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 - William Programação</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><hr> <?php if (!empty($dados)): ?> <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_botao_inicio?>' href="index.php?page=<?=$primeira_pagina?>" title="Primeira Página">Primeira</a> <a class='box-navegacao <?=$exibir_botao_inicio?>' href="index.php?page=<?=$pagina_anterior?>" title="Página Anterior">Anterior</a> <?php /* Loop para montar a páginação central com os números */ 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_botao_final?>' href="index.php?page=<?=$proxima_pagina?>" title="Próxima Página">Próxima</a> <a class='box-navegacao <?=$exibir_botao_final?>' href="index.php?page=<?=$ultima_pagina?>" title="Última Página">Último</a> </div> <?php else: ?> <p class="bg-danger">Nenhum registro foi encontrado!</p> <?php endif; ?> </div> </div> </body> </html> |
Observação: A folha de estilo CSS foi postado no Paginação de Dados no PHP com PDO – Introdução.
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).
1 2 3 |
/* 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.
1 2 |
/* 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.
1 2 |
/* Calcula a linha inicial da consulta */ $linha_inicial = ($pagina_atual -1) * QTDE_REGISTROS; |
4 – Nessa linha temos a mágica da paginação com MySQL usando a clausula LIMIT sendo parametrizada pelos valores recebidos e calculados.
1 |
$sql = "SELECT id, titulo, autor, previa, DATE_FORMAT(data, '%Y-%m-%d') AS data FROM artigos LIMIT {$linha_inicial}, " . QTDE_REGISTROS; |
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.
1 2 3 4 5 |
/* 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).
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 |
/* 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 : ; /* 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 : ; /* 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.
1 2 |
/* Verifica se vai exibir o botão "Primeira" e "Anterior" */ $exibir_botao_inicio = ($range_inicial < $pagina_atual) ? 'mostrar' : 'esconder'; |
1 2 |
/* Verifica se vai exibir o botão "Próxima" e "Última" */ $exibir_botao_final = ($range_final > $pagina_atual) ? 'mostrar' : 'esconder'; |
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.
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.
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.
Neste artigo demonstrei como montar paginação com MySQL, com isso chegamos ao final de mais um artigo, é 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.
Até a próxima …