Aplicando Paginação para Exibir Múltiplos Registros usando MySQL com PHP no Ubuntu 20.04

Shreyas Patil2022-06-27 · 16 min read

Aplicando Paginação para Exibir Múltiplos Registros usando MySQL com PHP no Ubuntu 20.04

Introdução

Paginação é um conceito importante na construção de aplicações orientadas a dados. Ao extrair os registros do banco de dados, um grande número de registros pode ser recuperado. Por exemplo, ao executar a consulta para usuários ativos ou usuários pertencentes a alguma localização específica, um grande número de usuários, na casa das centenas ou até milhares, pode ser retornado. Exibir esse enorme número de registros em uma única página web não é uma solução amigável para o usuário, pois pode sobrecarregá-lo, além de arruinar a fluidez da experiência do usuário.

Portanto, uma opção preferível é restringir o número de registros. Este é o conceito de paginação. Em outras palavras, dividimos o número de registros em páginas e depois exibimos a página. Uma única página pode ter dez ou até quinze ou qualquer outra quantidade viável de registros que possam ser exibidos no front-end.

Embora a paginação possa melhorar a experiência geral do usuário com a aplicação, ela também pode tornar a aplicação mais rápida, já que podemos recuperar um número reduzido de registros. Consequentemente, menos dados são transferidos pela rede entre o cliente e o servidor, reduzindo o tempo de latência.

Neste tutorial, nós iremos construir um código PHP que se conecta ao banco de dados e implementar a paginação usando o banco de dados MySQL e sua LIMIT cláusula.

Pré-requisitos

Para acompanhar este tutorial, você precisará de:

A versão mais recente do Ubuntu, instalada e em execução. Siga o tutorial Como configurar seu servidor Ubuntu se precisar de ajuda.
Você também precisará ter o PHP e o MySQL instalados. Para configurar o PHP e o MySQL, temos um tutorial: Configuração da Stack LAMP – Linux Apache MySQL PHP.

Passo 1 — Configurar um Usuário de Banco de Dados e um Banco de Dados de Teste

Neste tutorial, estabeleceremos a conexão com o banco de dados MySQL e recuperaremos os registros. Esses registros serão exibidos em uma página HTML com o elemento table. Para estabelecer a conexão e exibir os registros, usaremos um script PHP. Assim que estivermos prontos com a conexão do banco de dados, testaremos nossa aplicação com paginação e sem paginação. Testar dessa forma nos permitirá entender como a paginação funciona na prática.

Para conectar ao banco de dados MySQL, precisaremos de um banco de dados MySQL de exemplo e das informações de autenticação do usuário para conectar ao banco de dados MySQL. Nesta etapa, você criará um usuário não-root para o seu banco de dados MySQL, um banco de dados de exemplo e uma tabela para testar o script PHP.

Primeiro, faça login no seu servidor. Em seguida, faça login no servidor MySQL usando o comando abaixo:

mysql> sudo mysql -u root -p

1	mysql> sudo mysql -u root -p

Será solicitada a senha do usuário root. Insira a senha e pressione Enter para prosseguir. Em seguida, crie um banco de dados de exemplo, chamado test_db, que usaremos em nosso tutorial. Para criar um novo banco de dados, execute o comando abaixo:

mysql> Create database test_db;

1	mysql> Create database test_db;

Você verá a saída do servidor MySQL indicando que uma linha foi afetada. Em seguida, precisaremos criar um novo usuário para este banco de dados. Concederemos a este usuário todos os privilégios. No entanto, ao trabalhar com aplicações práticas, certifique-se de limitar os privilégios. Chamaremos nosso usuário de test_user. No comando abaixo, substitua PASSWORD por uma senha forte:

mysql> GRANT ALL PRIVILEGES ON test_db.* TO 'test_user'@'localhost' IDENTIFIED BY 'PASSWORD';

1	mysql> GRANT ALL PRIVILEGES ON test_db.* TO 'test_user'@'localhost' IDENTIFIED BY 'PASSWORD';

Assim que o usuário for criado, limpe os privilégios (flush):

mysql> FLUSH PRIVILEGES;

1	mysql> FLUSH PRIVILEGES;

Agora que tanto o test_user quanto o test_db estão prontos, use o test_db para criar tabelas. Para alternar para o test_db, execute o comando abaixo:

mysql> Use test_db;

1	mysql> Use test_db;

Assim que virmos a alteração do banco de dados na saída, estaremos prontos para criar nossa tabela. Criaremos uma nova tabela chamada Products para conter os produtos. Para nossos propósitos, precisaremos de apenas duas colunas nesta tabela: product_id e product_name. Definiremos o product_id como AUTO_INCREMENT, para que seja incrementado toda vez que adicionarmos um novo produto. A outra coluna chamada product_name será usada para armazenar o nome do produto. A product_name coluna será usada para diferenciar cada produto pelo nome:

mysql> Create table products (product_id BIGINT PRIMARY KEY AUTO_INCREMENT, product_name VARCHAR(50) NOT NULL ) Engine = InnoDB;

1	mysql> Create table products (product_id BIGINT PRIMARY KEY AUTO_INCREMENT, product_name VARCHAR(50) NOT NULL ) Engine = InnoDB;

Agora vamos adicionar alguns produtos a esta tabela recém-criada. Para isso, execute os comandos abaixo:

mysql> Insert into products(product_name) values ('WIRELESS MOUSE');
mysql> Insert into products(product_name) values ('BLUETOOTH SPEAKER');
mysql> Insert into products(product_name) values ('GAMING KEYBOARD');
mysql> Insert into products(product_name) values ('320GB FAST SSD');
mysql> Insert into products(product_name) values ('17 INCHES TFT');
mysql> Insert into products(product_name) values ('SPECIAL HEADPHONES');
mysql> Insert into products(product_name) values ('HD GRAPHIC CARD');
mysql> Insert into products(product_name) values ('80MM THERMAL PRINTER');
mysql> Insert into products(product_name) values ('HDMI TO VGA CONVERTER');
mysql> Insert into products(product_name) values ('FINGERPRINT SCANNER');

mysql> Insert into products(product_name) values ('WIRELESS MOUSE');

mysql> Insert into products(product_name) values ('BLUETOOTH SPEAKER');

mysql> Insert into products(product_name) values ('GAMING KEYBOARD');

mysql> Insert into products(product_name) values ('320GB FAST SSD');

mysql> Insert into products(product_name) values ('17 INCHES TFT');

mysql> Insert into products(product_name) values ('SPECIAL HEADPHONES');

mysql> Insert into products(product_name) values ('HD GRAPHIC CARD');

mysql> Insert into products(product_name) values ('80MM THERMAL PRINTER');

mysql> Insert into products(product_name) values ('HDMI TO VGA CONVERTER');

mysql> Insert into products(product_name) values ('FINGERPRINT SCANNER');

Para verificar se esses produtos foram inseridos na tabela, execute o comando abaixo:

mysql> select * from products;

1	mysql> select * from products;

Se você vir os produtos que inseriu acima, está tudo pronto para prosseguir. Saia do banco de dados MySQL usando o comando abaixo:

mysql> quit;

1	mysql> quit;

Agora que configuramos o banco de dados MySQL e inserimos os dados de teste, estamos prontos para codificar o script PHP para conectar a este banco de dados e buscar os registros.

Passo 2 – Buscando e Exibindo Registros Sem Paginação

Como discutido acima, primeiro exibiremos os registros sem paginação. Estaremos construindo um script PHP que se conecta ao banco de dados MySQL e busca os registros. Nós nos conectaremos ao test_db que criamos acima usando o test_user e buscaremos os registros.

Nossa tabela products atualmente tem apenas dez registros. Pode não ser óbvio para muitos usuários se a paginação é necessária ou não. No entanto, testar a aplicação sem a paginação detalhará como ela ajuda na experiência geral da aplicação. Também entenderemos como a divisão dos dados ajuda a criar uma melhor experiência de usuário e reduz a carga no servidor.

No seu servidor PHP, no diretório raiz do site, execute o comando abaixo:

sudo nano /var/www/html/pagination_test.php

Agora, neste arquivo, adicione o conteúdo abaixo. Certifique-se de substituir o PASSWORD pelo valor da senha do seu test_user:

<?php
    try {
        $pdo = new PDO("mysql:host=localhost;dbname=test_db", "test_user", "PASSWORD");
        $pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
        $pdo->setAttribute(PDO::ATTR_EMULATE_PREPARES,false);
        $sql = "select * from products";
        $stmt = $pdo->prepare($sql);
        $stmt -> execute();
        echo "<table border='1' align='center'>";
        while (($row = $stmt -> fetch(PDO::FETCH_ASSOC)) !== false) {
            echo "<tr>";
            echo "<td>".$row['product_id']."</td>";
            echo "<td>".$row['product_name']."</td>";
            echo "</tr>";
        }
        echo "</table>";
    } 

    catch(PDOException $e) {
        echo $e->getMessage();
    }
?>

<?php

try {

$pdo = new PDO("mysql:host=localhost;dbname=test_db", "test_user", "PASSWORD");

$pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);

$pdo->setAttribute(PDO::ATTR_EMULATE_PREPARES,false);

$sql = "select * from products";

$stmt = $pdo->prepare($sql);

$stmt -> execute();

echo "<table border='1' align='center'>";

while (($row = $stmt -> fetch(PDO::FETCH_ASSOC)) !== false) {

echo "<tr>";

echo "<td>".$row['product_id']."</td>";

echo "<td>".$row['product_name']."</td>";

echo "</tr>";

}

echo "</table>";

}

catch(PDOException $e) {

echo $e->getMessage();

}

Salve o arquivo. Antes de prosseguirmos, vamos resumir o que fizemos acima:

Primeiro, nos conectamos ao banco de dados MySQL e, para isso, usamos a PHP Data Object (PDO) biblioteca. Usamos as credenciais do usuário do MySQL que havíamos usado acima para nos conectar ao banco de dados MySQL. O PDO é uma biblioteca extremamente útil que é usada para se conectar ao banco de dados. Ele torna a camada de acesso a dados fácil de codificar e permite a conexão a diversos bancos de dados sem a necessidade de refatorar extensivamente a aplicação. O PDO usa prepared statements, o que garante que a segurança não seja comprometida. Portanto, as consultas são executadas de forma segura.

Em seguida, usamos a PDO API para executar a instrução select * from products. O $pdo->setAttribute(PDO::ATTR_EMULATE_PREPARES,false) garante que os tipos de dados no PHP sejam os mesmos do banco de dados. Portanto, product_id e product_name aparecem como inteiro e string, respectivamente.
O $pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION); diz ao PDO para lançar a exceção se houver um erro. Ao fazer isso, podemos facilmente capturar a exceção no bloco try … catch e tratá-la conforme necessário.

Agora que temos nosso script pronto, precisamos executá-lo. Para isso, abra seu navegador e visite a URL: /var/www/html/pagination_test.php. Certifique-se de anexar o IP do seu servidor à URL. Se estiver executando localmente, sua URL será http://localhost/var/www/html/pagination_test.php.

Você verá a saída abaixo:

Applying Pagination to Display Multiple Records using MySQL with PHP image 1

Aqui, nossos produtos são retornados e exibidos na página. Agora, imagine se tivéssemos centenas de produtos. Isso resultaria em um loop longo para buscar os dados e exibi-los na página. Isso aumentará o tempo de carregamento da página.

Na próxima seção, modificaremos o script PHP e incluiremos o MySQL LIMIT. Isso corrigirá o problema de exibição de um grande número de registros. Adicionaremos os links de paginação para que o usuário possa navegar pelas páginas.

Passo 3 – Configurando a Paginação usando PHP

Neste passo, configuraremos a paginação usando PHP para exibir os dados usando várias tabelas. Para acomodar a paginação, modificaremos o script que codificamos no passo anterior.

Nós usaremos a cláusula LIMIT do banco de dados MySQL. Antes de adicionar isso ao script, vejamos um exemplo da sintaxe LIMIT do MySQL:

mysql> Select [coluna1, coluna2, coluna n...] from [nome_da_tabela] LIMIT offset, registros;

1	mysql> Select [coluna1, coluna2, coluna n...] from [tabela nome] LIMIT offset, registros;

Na instrução acima, podemos ver que a cláusula LIMIT possui os argumentos offset e registros. O offset diz quantos registros pular e registros diz o número máximo de registros a serem exibidos por página ou recuperados do banco de dados.

Agora que sabemos como funciona a paginação, vejamos como vamos implementá-la. Exibiremos três registros por página. Portanto, se tivermos dez registros em nosso banco de dados, teremos quatro páginas dividindo o número total de registros pelo número de registros por página. Como o resultado pode não ser um número inteiro, usaremos a função PHP Ceil para arredondar para o número inteiro mais próximo para obter o resultado do número inteiro. Abaixo está um trecho de código simples para demonstrar a função Ceil:

$total_pages=ceil($total_records/$per_page);

1	$total_pages=ceil($total_records/$per_page);

Agora adicionaremos o código para a paginação. Para habilitar a paginação e adicionar os links de navegação, abra o arquivo /var/www/html/pagination_test.php usando o comando abaixo:

$ sudo nano /var/www/html/pagination_test.php

1	$ sudo nano /var/www/html/pagination_test.php

Em seguida, substitua o conteúdo deste arquivo pelo código abaixo:

<?php
    try {
        $pdo = new PDO("mysql:host=localhost;dbname=test_db", "test_user", "PASSWORD");
        $pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
        $pdo->setAttribute(PDO::ATTR_EMULATE_PREPARES,false);
    
        /* Begin Paging Info */
        $page = 1;

        if (isset($_GET['page'])) {
            $page = filter_var($_GET['page'], FILTER_SANITIZE_NUMBER_INT);
        }
        $per_page = 3;
        $sqlcount = "select count(*) as total_records from products";
        $stmt = $pdo->prepare($sqlcount);
        $stmt->execute();
        $row = $stmt->fetch();
        $total_records = $row['total_records'];
        $total_pages = ceil($total_records / $per_page);
        $offset = ($page-1) * $per_page;
        /* End Paging Info */
 
        $sql = "select * from products limit :offset, :per_page";
        $stmt = $pdo->prepare($sql);
        $stmt->execute(['offset'=>$offset, 'per_page'=>$per_page]);

        echo "<table border='1' align='center'>";

        while ( ($row = $stmt->fetch(PDO::FETCH_ASSOC) ) !== false) {
            echo "<tr>";
            echo "<td>".$row['product_id']."</td>";
            echo "<td>".$row['product_name']."</td>";
            echo "</tr>";
        }

        echo "</table>";

        /* Begin Navigation */
        echo "<table border='1' align='center'>";

        echo "<tr>";

        if ($page-1 >= 1) {
            echo "<td><a href=".$_SERVER['PHP_SELF']."?page=".($page - 1).">Anterior</a></td>";
            }

        if ($page+1 <= $total_pages) {
            echo "<td><a href=".$_SERVER['PHP_SELF']."?page=".($page + 1).">Próximo</a></td>";
        }

        echo "</tr>";

        echo "</table>";
        /* End Navigation */
    } 
    catch(PDOException $e) {
        echo $e->getMessage();
    }
?>

<?php

try {

$pdo = new PDO("mysql:host=localhost;dbname=test_db", "test_user", "SENHA");

$pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);

$pdo->setAttribute(PDO::ATTR_EMULATE_PREPARES,false);

/* Início das Informações de Paginação */

$page = 1;

if (isset($_GET['page'])) {

$page = filter_var($_GET['page'], FILTER_SANITIZE_NUMBER_INT);

}

$per_page = 3;

$sqlcount = "select count(*) as total_records from products";

$stmt = $pdo->prepare($sqlcount);

$stmt->execute();

$row = $stmt->fetch();

$total_records = $row['total_records'];

$total_pages = ceil($total_records / $per_page);

$offset = ($page-1) * $per_page;

/* Fim das Informações de Paginação */

$sql = "select * from products limit :offset, :per_page";

$stmt = $pdo->prepare($sql);

$stmt->execute(['offset'=>$offset, 'per_page'=>$per_page]);

echo "<table border='1' align='center'>";

while ( ($row = $stmt->fetch(PDO::FETCH_ASSOC) ) !== false) {

echo "<tr>";

echo "<td>".$row['product_id']."</td>";

echo "<td>".$row['product_name']."</td>";

echo "</tr>";

}

echo "</table>";

/* Início da Navegação */

echo "<table border='1' align='center'>";

echo "<tr>";

if ($page-1 >= 1) {

echo "<td><a href=".$_SERVER['PHP_SELF']."?page=".($page - 1).">Anterior</a></td>";

}

if ($page+1 <= $total_pages) {

echo "<td><a href=".$_SERVER['PHP_SELF']."?page=".($page + 1).">Próximo</a></td>";

}

echo "</tr>";

echo "</table>";

/* Fim da Navegação */

}

catch(PDOException $e) {

echo $e->getMessage();

}

Aqui está um resumo deste código:

Temos que reter a informação sobre a página em que nos encontramos atualmente. Para isso, estamos usando a $page variável. A $page variável é usada para extrair a página em que nos encontramos atualmente usando a $_GET[‘page’] variável.
Em seguida, precisamos especificar quantas páginas estamos exibindo em uma página. Para isso, estamos usando a $per_page variável. Aqui estamos exibindo apenas três produtos por página.
Como discutido acima, precisamos ter o número total de registros existentes no banco de dados. Isso é necessário para que possamos identificar o número total de páginas. A $total_records variável contém essa informação.
Finalmente, precisamos saber quantos registros ignorar. Por exemplo, se estivermos na segunda página, precisamos ignorar os três primeiros registros do banco de dados. Estamos usando a $offset variável para conter essa informação. Calculamos essa informação a cada carregamento de página usando a fórmula $offset=($page-1)*$per_page. Se você precisar exibir qualquer outro número de itens, poderá especificar o valor na $per_page variável.
Para exibir os botões de navegação, usamos o código abaixo:

. . .
if( $page-1>=1) {
    echo "<td><a href=".$_SERVER['PHP_SELF']."?page=".($page-1).">Anterior</a></td>";
    }

if( $page+1<=$total_pages) {
    echo "<td><a href=".$_SERVER['PHP_SELF']."?page=".($page+1).">Próximo</a></td>";
}

. . .

. . .

if( $page-1>=1) {

echo "<td><a href=".$_SERVER['PHP_SELF']."?page=".($page-1).">Anterior</a></td>";

}

if( $page+1<=$total_pages) {

echo "<td><a href=".$_SERVER['PHP_SELF']."?page=".($page+1).">Próximo</a></td>";

}

. . .

A lógica é que a $page é usada para conter a página atual. Se quisermos exibir a opção anterior na página, o script subtrairá um do valor da $page variável. Se o resultado for maior ou igual a um, a opção anterior será exibida na página.

Da mesma forma, para exibir a próxima opção, adicionamos um à $page variável. Aqui estamos garantindo que o resultado da adição não exceda o número total de páginas na $total_pages variável.

Depois disso, é hora de verificar a página. Abra o navegador e navegue até a página usando a URL que você usou antes:

http://your_server_ip/pagination_test.php

1	http://your_server_ip/pagination_test.php

Você verá a saída como abaixo:

Applying Pagination to Display Multiple Records using MySQL with PHP image 2

table2

table

Aqui você finalmente implementou a paginação usando a cláusula LIMIT do MySQL no script PHP. Ao implementar a paginação em sua aplicação, você terá uma maneira melhor de navegar por múltiplos registros.

Conclusão

Neste tutorial, vimos como implementar a paginação usando PHP e MySQL no Ubuntu. Utilizando essas etapas, você pode criar aplicações que exibam adequadamente um grande número de registros, divididos entre as páginas para uma manipulação eficaz.

Além disso, temos mais tutoriais úteis sobre PHP e MySQL usando o Ubuntu:

Feliz Computação!

Shreyas Patil

Autor · CloudSigma

Preslav Dobrev é um designer criativo na CloudSigma, focado na construção de uma identidade empresarial consistente por meio de canais de marketing tradicionais e inovadores. Ele é hábil em combinar a visão artística com o marketing estratégico para criar narrativas de marca impactantes.

Aplicando Paginação para Exibir Múltiplos Registros usando MySQL com PHP no Ubuntu 20.04

Introdução

Pré-requisitos

Passo 1 — Configurar um Usuário de Banco de Dados e um Banco de Dados de Teste

Passo 2 – Buscando e Exibindo Registros Sem Paginação

Passo 3 – Configurando a Paginação usando PHP

Conclusão

Shreyas Patil

Comentários