Aplicación de paginación para mostrar múltiples registros usando MySQL con PHP en Ubuntu 20.04

Shreyas Patil2022-06-27 · 17 min read

Aplicación de paginación para mostrar múltiples registros usando MySQL con PHP en Ubuntu 20.04

Introducción

Paginación es un concepto importante en la creación de aplicaciones basadas en datos. Al extraer los registros de la base de datos, se puede recuperar una gran cantidad de registros. Por ejemplo, al ejecutar la consulta de usuarios activos o usuarios que pertenecen a alguna ubicación específica, se puede devolver una gran cantidad de usuarios, de cientos o incluso miles. Mostrar esta enorme cantidad de registros en una sola página web no es una solución fácil de usar, ya que puede abrumar al usuario y arruinar la fluidez de la experiencia del usuario.

Por lo tanto, una opción preferible es restringir el número de registros. Este es el concepto de paginación. En otras palabras, dividimos el número de registros en páginas y luego mostramos la página. Una sola página puede tener diez o incluso quince o cualquier otra cantidad de registros viables que se puedan mostrar en el front-end.

Si bien la paginación puede mejorar la experiencia general del usuario de la aplicación, también puede hacer que la aplicación sea más rápida, ya que podemos recuperar un número reducido de registros. Por lo tanto, se transfieren menos datos a través de la red entre el cliente y el servidor, lo que reduce el tiempo de latencia.

En este tutorial, vamos a construir un código PHP que se conecta a la base de datos e implementa la paginación utilizando la base de datos MySQL y su cláusula LIMIT clause.

Requisitos previos

Para seguir este tutorial, necesitará:

La última versión de Ubuntu, instalada y en funcionamiento. Siga el tutorial Cómo configurar su servidor Ubuntu si necesita ayuda.
También necesitará tener instalados PHP y MySQL. Para configurar PHP y MySQL, tenemos un tutorial: Configuración de LAMP Stack – Linux Apache MySQL PHP.

Paso 1 — Configurar un usuario de base de datos y una base de datos de prueba

En este tutorial, estableceremos la conexión con la base de datos MySQL y recuperaremos los registros. Estos registros se mostrarán en una página HTML con el elemento table. Para establecer la conexión y mostrar los registros utilizaremos un script PHP. Una vez que estemos listos con la conexión a la base de datos, probaremos nuestra aplicación con paginación y sin paginación. Probar de esta manera nos permitirá comprender cómo funciona la paginación en la práctica.

Para conectarse a la base de datos MySQL necesitaremos una base de datos MySQL de muestra y la información de autenticación del usuario para conectarse a la base de datos MySQL. En este paso, creará un usuario que no sea root para su base de datos MySQL, una base de datos de muestra y una tabla para probar el script PHP.

Primero, inicie sesión en su servidor. Luego, inicie sesión en el servidor MySQL utilizando el siguiente comando:

mysql> sudo mysql -u root -p

1	mysql> sudo mysql -u root -p

Se le solicitará la contraseña del usuario root. Ingrese la contraseña y presione Enter para continuar. A continuación, cree una base de datos de muestra, llamada test_db, que utilizaremos en nuestro tutorial. Para crear una nueva base de datos, ejecute el siguiente comando:

mysql> Create database test_db;

1	mysql> Create database test_db;

Verá la salida del servidor MySQL indicando que una fila ha sido afectada. A continuación, necesitaremos crear un nuevo usuario para esta base de datos. Le otorgaremos a este usuario todos los privilegios. Sin embargo, al trabajar con aplicaciones prácticas, asegúrese de limitar los privilegios. Llamaremos a nuestro usuario test_user. En el siguiente comando, reemplace PASSWORD con una contraseña segura:

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';

Once the user has been created, flush the privileges:

mysql> FLUSH PRIVILEGES;

1	mysql> FLUSH PRIVILEGES;

Ahora que tanto test_user como test_db están listos, use test_db para crear tablas. Para cambiar a test_db, ejecute el siguiente comando:

mysql> Use test_db;

1	mysql> Use test_db;

Una vez que veamos que la base de datos ha cambiado en la salida, estaremos listos para crear nuestra tabla. Crearemos una nueva tabla llamada Products para contener los productos. Para nuestros propósitos, solo necesitaremos dos columnas en esta tabla: product_id y product_name. Estableceremos product_id como AUTO_INCREMENT, para que se incremente cada vez que añadamos un nuevo producto. La otra columna llamada product_name se utilizará para almacenar el nombre del producto. La columna product_name se utilizará para diferenciar cada producto por su nombre:

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;

Ahora añadiremos algunos productos a esta tabla recién creada. Para ello, ejecute los siguientes comandos:

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 si estos productos se han introducido en la tabla, ejecute el siguiente comando:

mysql> select * from products;

1	mysql> select * from products;

Si ve los productos que introdujo anteriormente, ya está listo para continuar. Salga de la base de datos MySQL utilizando el siguiente comando:

mysql> quit;

1	mysql> quit;

Ahora que hemos configurado la base de datos MySQL y hemos introducido los datos de prueba, estamos listos para programar el script PHP para conectarnos a esta base de datos y recuperar los registros.

Paso 2 – Recuperación y visualización de registros sin paginación

Como se mencionó anteriormente, primero mostraremos los registros sin paginación. Crearemos un script PHP que se conecte con la base de datos MySQL y recupere los registros. Nos conectaremos a la base de datos test_db que creamos anteriormente utilizando el usuario test_user y recuperaremos los registros.

Nuestra tabla products actualmente tiene solo diez registros. Puede que no sea obvio para muchos usuarios si la paginación es necesaria o no. Sin embargo, probar la aplicación sin la paginación detallará cómo ayuda en la experiencia general de la aplicación. También comprenderemos cómo la división de los datos ayuda a crear una mejor experiencia de usuario y reduce la carga en el servidor.

En su servidor PHP, en la raíz de documentos de la carpeta del sitio web, ejecute el siguiente comando:

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

Ahora en este archivo, añada el siguiente contenido. Asegúrese de reemplazar PASSWORD con el valor de la contraseña de su usuario 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();

}

Guarde el archivo. Antes de seguir adelante, resumiremos lo que hicimos anteriormente:

Primero, nos conectamos a la base de datos MySQL, y para ello utilizamos la PHP Data Object (PDO) biblioteca. Usamos las credenciales del usuario de MySQL que habíamos utilizado anteriormente para conectarnos a la base de datos MySQL. PDO es una biblioteca extremadamente útil que se utiliza para conectarse a la base de datos. Hace que la capa de acceso a datos sea fácil de codificar y permite conectarse a diversas bases de datos sin tener que refactorizar la aplicación de forma extensiva. PDO utiliza sentencias preparadas, lo que garantiza que la seguridad no se vea comprometida. Por lo tanto, las consultas se ejecutan de forma segura.

A continuación, utilizamos la PDO API para ejecutar la sentencia select * from products. El $pdo->setAttribute(PDO::ATTR_EMULATE_PREPARES,false) garantiza que los tipos de datos en PHP sean los mismos que en la base de datos. Por lo tanto, product_id y product_name aparecen como entero y cadena respectivamente.
El $pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION); le indica a PDO que lance la excepción si hay un error. Al hacerlo, podemos capturar fácilmente la excepción en el bloque try … catch y manejarla según sea necesario.

Ahora que tenemos nuestro script listo, tenemos que ejecutarlo. Para ello, abra su navegador y visite la URL: /var/www/html/pagination_test.php. Asegúrese de añadir la IP de su servidor a la URL. Si lo está ejecutando localmente, su URL será http://localhost/var/www/html/pagination_test.php.

Verá la siguiente salida:

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

Aquí, nuestros productos se devuelven y se muestran en la página. Ahora, imagine si tuviéramos cientos de productos. Esto resultaría en un bucle largo para recuperar los datos y mostrarlos en la página. Esto aumentará el tiempo de carga de la página.

En la siguiente sección, modificaremos el script PHP e incluiremos la cláusula MySQL LIMIT. Esto solucionará el problema de mostrar una gran cantidad de registros. Añadiremos los enlaces de paginación para que el usuario pueda navegar por las páginas.

Paso 3 – Configuración de la paginación con PHP

En este paso, configuraremos la paginación usando PHP para mostrar los datos utilizando múltiples tablas. Para dar cabida a la paginación, modificaremos el script que habíamos codificado en el paso anterior.

Utilizaremos la cláusula LIMIT de la base de datos MySQL. Antes de añadir esto al script, veamos un ejemplo de la sintaxis de MySQL LIMIT:

mysql> Select [columna1, columna2, columna n...] from [nombre_tabla] LIMIT offset, registros;

1	mysql> Select [columna1, columna2, columna n...] from [tabla nombre] LIMIT offset, registros;

En la declaración anterior podemos ver que la cláusula LIMIT tiene los argumentos offset y registros. offset indica cuántos registros omitir y registros indica el número máximo de registros a mostrar por página o a recuperar de la base de datos.

Ahora que sabemos cómo funciona la paginación, veamos cómo la implementaremos. Mostraremos tres registros por página. Por lo tanto, si tenemos diez registros en nuestra base de datos, tendremos cuatro páginas al dividir el número total de registros por el número de registros por página. Dado que el resultado podría no ser un número entero, utilizaremos la función PHP Ceil para redondear al entero más cercano y obtener el resultado como un número entero. A continuación se muestra un fragmento de código simple para demostrar la función Ceil:

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

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

Ahora añadiremos el código para la paginación. Para habilitar la paginación y añadir los enlaces de navegación, abra el archivo /var/www/html/pagination_test.php usando el siguiente comando:

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

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

A continuación, reemplace el contenido de este archivo con el siguiente código:

<?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).">Siguiente</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", "PASSWORD");

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

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

/* Inicio de información de paginación */

$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;

/* Fin de información de paginación */

$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>";

/* Inicio de navegación */

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).">Siguiente</a></td>";

}

echo "</tr>";

echo "</table>";

/* Fin de navegación */

}

catch(PDOException $e) {

echo $e->getMessage();

}

Aquí hay un resumen de este código:

Tenemos que mantener la información sobre la página en la que nos encontramos actualmente. Para esto estamos utilizando la $page variable. La $page variable se utiliza para extraer la página en la que nos encontramos actualmente utilizando la $_GET[‘page’] variable.
A continuación, necesitamos especificar cuántas páginas estamos mostrando en una página. Para esto estamos utilizando la $per_page variable. Aquí solo estamos mostrando tres productos por página.
Como se mencionó anteriormente, necesitamos tener el número total de registros que existen en la base de datos. Esto es necesario para que podamos identificar el número total de páginas. La variable $total_records contiene esta información.
Finalmente, necesitamos saber cuántos registros omitir. Por ejemplo, si estamos en la segunda página, necesitamos omitir los tres primeros registros de la base de datos. Estamos utilizando la variable $offset para contener esta información. Calculamos esta información en cada carga de página utilizando la fórmula $offset=($page-1)*$per_page. Si necesita mostrar cualquier otro número de elementos, puede especificar el valor en la variable $per_page.
Para mostrar los botones de navegación, utilizamos el siguiente código:

. . .
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).">Siguiente</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).">Siguiente</a></td>";

}

. . .

La lógica es que la variable $page se utiliza para contener la página actual. Si queremos mostrar la opción anterior en la página, el script restará uno al valor de la variable $page. Si el resultado es mayor o igual a uno, la opción anterior se muestra en la página.

Del mismo modo, para mostrar la siguiente opción, sumamos uno a la variable $page. Aquí nos aseguramos de que el resultado de la suma no supere el número total de páginas en la variable $total_pages.

Después de eso, es hora de comprobar la página. Abra el navegador y navegue a la página utilizando la URL que utilizó antes:

http://your_server_ip/pagination_test.php

1	http://your_server_ip/pagination_test.php

Verá el resultado como se muestra a continuación:

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

table2

table

Aquí finalmente ha implementado la paginación utilizando la cláusula LIMIT de MySQL en el script PHP. Al implementar la paginación en su aplicación, tendrá una mejor manera de navegar a través de múltiples registros.

Conclusión

En este tutorial, vimos cómo implementar la paginación utilizando PHP y MySQL en Ubuntu. Utilizando estos pasos, puede crear aplicaciones que puedan mostrar adecuadamente una gran cantidad de registros, divididos en páginas para una manipulación efectiva.

Además, tenemos más tutoriales útiles sobre PHP y MySQL utilizando Ubuntu:

¡Feliz informática!

Shreyas Patil

Autor · CloudSigma

Preslav Dobrev es diseñador creativo en CloudSigma, centrado en una identidad empresarial coherente mediante el uso de canales de marketing tradicionales e innovadores. Es experto en fusionar la visión artística con el marketing estratégico para crear narrativas de marca impactantes.

Aplicación de paginación para mostrar múltiples registros usando MySQL con PHP en Ubuntu 20.04

Introducción

Requisitos previos

Paso 1 — Configurar un usuario de base de datos y una base de datos de prueba

Paso 2 – Recuperación y visualización de registros sin paginación

Paso 3 – Configuración de la paginación con PHP

Conclusión

Shreyas Patil

Comentarios