Cómo implementar paginación en MySQL con PHP en Ubuntu 18.04

El autor seleccionó a la Apache Software Foundation para recibir una donación como parte del programa Write for DOnations.

Introducción

La paginación es el concepto de restringir la cantidad de filas devueltas en un conjunto de registros en páginas separadas y ordenadas para permitir una navegación sencilla entre ellas, de modo que cuando hay un conjunto de datos grande, se puede configurar la paginación para que solo devuelva una cantidad específica de filas en cada página. Por ejemplo, la paginación puede ayudar a evitar abrumar a los usuarios cuando una tienda web contiene miles de productos al reducir la cantidad de artículos enumerados en una página, ya que a menudo es poco probable que un usuario necesite ver todos los productos. Otro ejemplo es una aplicación que muestra registros en un dispositivo móvil; habilitar la paginación en tal caso dividiría los registros en varias páginas que pueden caber mejor en una pantalla.

Además de los beneficios visuales para los usuarios finales, la paginación hace que las aplicaciones sean más rápidas porque reduce la cantidad de registros que se devuelven a la vez. Esto limita los datos que se deben transmitir entre el cliente y el servidor, lo que ayuda a preservar los recursos del servidor, como la RAM.

En este tutorial, creará un script PHP para conectarse a su base de datos e implementar la paginación en su script utilizando la LIMITcláusula MySQL.

Prerrequisitos

Antes de comenzar, necesitarás lo siguiente:

• Un servidor Ubuntu 18.04 configurado siguiendo la configuración inicial del servidor con Ubuntu 18.04, incluido un usuario sudo que no sea root.
• Apache, MySQL y PHP instalados en su sistema. Puede seguir la guía sobre cómo instalar la pila Linux, Apache, MySQL y PHP (LAMP) en Ubuntu 18.04.

Paso 1: creación de un usuario de base de datos y una base de datos de prueba

En este tutorial, creará un script PHP que se conectará a una base de datos MySQL, obtendrá registros y los mostrará en una página HTML dentro de una tabla. Probará el script PHP de dos formas diferentes desde su navegador web. Primero, creará un script sin ningún código de paginación para ver cómo se muestran los registros. Segundo, agregará código de navegación de página en el archivo PHP para comprender cómo funciona la paginación en la práctica.

El código PHP requiere un usuario MySQL para fines de autenticación y una base de datos de muestra a la que conectarse. En este paso, creará un usuario no root para su base de datos MySQL, una base de datos de muestra y una tabla para probar el script PHP.

Para comenzar, inicie sesión en su servidor. A continuación, inicie sesión en su servidor MySQL con el siguiente comando:

sudo mysql -u root -p

Ingrese la contraseña raíz de su servidor MySQL y presione ENTERcontinuar. Luego, verá el mensaje de MySQL. Para crear una base de datos de muestra, que llamaremos test_dben este tutorial, ejecute el siguiente comando:

Create database test_db;

Verá el siguiente resultado:

OutputQuery OK, 1 row affected (0.00 sec)

Luego, crea un test_usery otorga al usuario todos los privilegios para el test_db. Reemplaza PASSWORDcon un valor fuerte:

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

OutputQuery OK, 1 row affected (0.00 sec)

Recargue los privilegios de MySQL con:

FLUSH PRIVILEGES;

OutputQuery OK, 1 row affected (0.00 sec)

A continuación, cambie a la test_dbbase de datos para comenzar a trabajar directamente en test_dbella:

Use test_db;

OutputDatabase changed

Ahora crea una productstabla. La tabla contendrá tus productos de muestra; para este tutorial, necesitarás solo dos columnas para los datos. La product_idcolumna servirá como clave principal para identificar de forma única cada registro. Esta columna se configurará para AUTO_INCREMENTgenerar una nueva product_idpara cada artículo insertado. Usarás el product_namecampo para diferenciar cada artículo por nombre:

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

OutputQuery OK, 0 rows affected (0.02 sec)

Para agregar diez productos de prueba a la productstabla, ejecute las siguientes instrucciones SQL:

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

Verás este resultado:

OutputQuery OK, 1 row affected (0.02 sec)

Verifique que los productos se hayan insertado en la tabla ejecutando:

select * from products;

Verá los productos en su salida dentro de las dos columnas:

Output+------------+-----------------------+| product_id | product_name          |+------------+-----------------------+|          1 | WIRELESS MOUSE        ||          2 | BLUETOOTH SPEAKER     ||          3 | GAMING KEYBOARD       ||          4 | 320GB FAST SSD        ||          5 | 17 INCHES TFT         ||          6 | SPECIAL HEADPHONES    ||          7 | HD GRAPHIC CARD       ||          8 | 80MM THERMAL PRINTER  ||          9 | HDMI TO VGA CONVERTER ||         10 | FINGERPRINT SCANNER   |+------------+-----------------------+10 rows in set (0.00 sec)

Salir de MySQL:

quit;

Con la base de datos de muestra, la tabla y los datos de prueba en su lugar, ahora puede crear un script PHP para mostrar datos en una página web.

Paso 2: Visualización de registros MySQL sin paginación

Ahora creará un script PHP que se conecta a la base de datos MySQL que creó en el paso anterior y enumera los productos en un navegador web. En este paso, su código PHP se ejecutará sin ningún tipo de paginación para demostrar cómo se muestran los registros no divididos en una sola página. Aunque solo tiene diez registros para fines de prueba en este tutorial, ver los registros sin paginación demostrará por qué la segmentación de datos creará en última instancia una mejor experiencia de usuario y supondrá una menor carga para el servidor.

Cree el archivo de script PHP en la raíz del documento de su sitio web con el siguiente comando:

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

A continuación, añade el siguiente contenido al archivo. Recuerda sustituirlo PASSWORDpor el valor correcto de la contraseña que asignaste en test_userel paso anterior:

/var/www/html/pagination_test.php

?phptry {  $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 presionando CTRL+X, Y, y ENTER.

En este script, se conectará a la base de datos MySQL utilizando la biblioteca PDO (objeto de datos PHP) con las credenciales de base de datos que creó en el Paso 1.

PDO es una interfaz liviana para conectarse a bases de datos. La capa de acceso a datos es más portátil y puede funcionar en diferentes bases de datos con solo reescrituras menores de código. PDO tiene mayor seguridad ya que admite declaraciones preparadas, una característica que permite que las consultas se ejecuten más rápido de manera segura.

Luego, le indica a la API de PDO que ejecute la select * from productsdeclaración y enumere los productos en una tabla HTML sin paginación. La línea $pdo-setAttribute(PDO::ATTR_EMULATE_PREPARES,false);garantiza que los tipos de datos se devuelvan tal como aparecen en la base de datos. Esto significa que PDO devolverá como product_idun entero y product_namecomo una cadena. $pdo-setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);Le indica a PDO que lance una excepción si se encuentra un error. Para facilitar la depuración, está capturando el error dentro del try{}...catch{}bloque PHP.

Para ejecutar el /var/www/html/pagination_test.phparchivo de script PHP que ha creado, visite la siguiente URL reemplazándola your-server-IPcon la dirección IP pública de su servidor:

http://your-server-IP/pagination_test.php

Verás una página con una tabla de tus productos.

Su script PHP funciona como se esperaba; enumera todos los productos en una página. Si tuviera miles de productos, esto generaría un bucle largo mientras los productos se obtienen de la base de datos y se muestran en la página PHP.

Para superar esta limitación, modificará el script PHP e incluirá la LIMITcláusula MySQL y algunos enlaces de navegación en la parte inferior de la tabla para agregar funcionalidad de paginación.

Paso 3: Implementación de la paginación con PHP

En este paso, el objetivo es dividir los datos de prueba en varias páginas manejables. Esto no solo mejorará la legibilidad, sino que también utilizará los recursos del servidor de manera más eficiente. Modificará el script PHP que creó en el paso anterior para adaptarlo a la paginación.

Para ello, deberá implementar la LIMITcláusula MySQL. Antes de agregarla al script, veamos un ejemplo de la LIMITsintaxis MySQL:

Select [column1, column2, column n...] from [table name] LIMIT offset, records;

La LIMITcláusula toma dos argumentos, como se muestra hacia el final de esta declaración. El offsetvalor es la cantidad de registros que se omitirán antes de la primera fila. recordsEstablece la cantidad máxima de registros que se mostrarán por página.

Para probar la paginación, deberá mostrar tres registros por página. Para obtener la cantidad total de páginas, debe dividir la cantidad total de registros de su tabla por las filas que desea mostrar por página. Luego, redondee el valor resultante al entero más cercano utilizando Ceilla función PHP, como se muestra en el siguiente fragmento de código PHP:

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

A continuación se muestra la versión modificada del script PHP con el código de paginación completo. Para incluir los códigos de paginación y navegación, abra el /var/www/html/pagination_test.phparchivo:

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

Luego, agregue el siguiente código resaltado a su archivo:

/var/www/html/pagination_test.php

?phptry {  $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 "tda href=".$_SERVER['PHP_SELF']."?page=".($page - 1)."Previous/a/td";  }  if ($page+1 = $total_pages) {    echo "tda href=".$_SERVER['PHP_SELF']."?page=".($page + 1)."Next/a/td";  }  echo "/tr";  echo "/table";  /* End Navigation */} catch(PDOException $e) {  echo $e-getMessage();}?

En su archivo ha utilizado parámetros adicionales para ejecutar la paginación:

• $page: Esta variable contiene la página actual en su secuencia de comandos. Al moverse entre las páginas, su secuencia de comandos recupera un parámetro de URL cuyo nombre se obtiene pagemediante la $_GET['page']variable.
• $per_page: Esta variable contiene la cantidad máxima de registros que desea que se muestren por página. En su caso, desea incluir tres productos en cada página.
• $total_records:Antes de enumerar los productos, debe ejecutar una declaración SQL para obtener un recuento total de registros en su tabla de destino y asignarlo a la $total_recordsvariable.
• $offset: Esta variable representa la cantidad total de registros que se deben omitir antes de la primera fila. Este valor se calcula de forma dinámica mediante el script PHP mediante la fórmula $offset=($page-1)*$per_page. Puede adaptar esta fórmula a sus proyectos de paginación PHP. Recuerde que puede cambiar la $per_pagevariable para adaptarla a sus necesidades. Por ejemplo, puede cambiarla a un valor de 50para mostrar cincuenta elementos por página si está ejecutando un sitio web u otra cantidad para un dispositivo móvil.

Nuevamente, visite su dirección IP en un navegador y reemplácela your_server_ipcon la dirección IP pública de su servidor:

http://your_server_ip/pagination_test.php

Ahora verás algunos botones de navegación en la parte inferior de la página. En la primera página, no verás el botón Anterior . Lo mismo ocurre en la última página, donde no verás el botón Siguientepage . Además, observa cómo cambia el parámetro URL a medida que visitas cada página.

Los enlaces de navegación en la parte inferior de la página se logran utilizando el siguiente fragmento de código PHP de su archivo:

/var/www/html/pagination_test.php

. . .if( $page-1=1) {  echo "tda href=".$_SERVER['PHP_SELF']."?page=".($page-1)."Previous/a/td";}if( $page+1=$total_pages) {  echo "tda href=".$_SERVER['PHP_SELF']."?page=".($page+1)."Next/a/td";}. . .

Aquí, la $pagevariable representa el número de página actual. Luego, para obtener la página anterior, el código restará 1de la variable. Por lo tanto, si está en la página 2, la fórmula (2-1)le dará un resultado de 1y esta será la página anterior que aparecerá en el enlace. Sin embargo, tenga en cuenta que solo mostrará la página anterior si hay un resultado mayor o igual a 1.

De manera similar, para llegar a la siguiente página, agregas uno a la $pagevariable y también debes asegurarte de que el $pageresultado que agregamos al pageparámetro URL no sea mayor que el total de páginas que has calculado en tu código PHP.

En este punto, su script PHP está funcionando con paginación y puede implementar la LIMITcláusula MySQL para una mejor navegación de registros.

Conclusión

En este tutorial, implementó la paginación en MySQL con PHP en un servidor Ubuntu 18.04. Puede utilizar estos pasos con un conjunto de registros más grande utilizando el script PHP para incluir la paginación. Al utilizar la paginación en su sitio web o aplicación, puede crear una mejor navegación para el usuario y una utilización óptima de los recursos en su servidor.

Desde aquí puedes considerar una mayor optimización para tu base de datos y otras tareas de base de datos con estos tutoriales:

• Cómo optimizar MySQL con Query Cache en Ubuntu 18.04
• Introducción a las consultas en MySQL
• Cómo solucionar problemas en MySQL