Применение пагинации для отображения нескольких записей с использованием MySQL и PHP на Ubuntu 20.04

Shreyas Patil2022-06-27 · 15 min read

Применение пагинации для отображения нескольких записей с использованием MySQL и PHP на Ubuntu 20.04

Введение

Пагинация — важная концепция при создании приложений, работающих с данными. При извлечении записей из базы данных может быть получено большое количество записей. Например, при выполнении запроса для активных пользователей или пользователей, принадлежащих к определенному местоположению, может быть возвращено большое количество пользователей — сотни или даже тысячи. Отображение этого огромного количества записей на одной веб-странице не является удобным для пользователя решением, так как это может перегрузить пользователя, а также испортить плавность взаимодействия с интерфейсом.

Следовательно, предпочтительным вариантом является ограничение количества записей. В этом и заключается концепция пагинации. Другими словами, мы делим количество записей на страницы, а затем отображаем страницу. На одной странице может быть десять, пятнадцать или любое другое подходящее количество записей, которые могут быть отображены в интерфейсе.

Хотя пагинация может улучшить общий пользовательский опыт приложения, она также может ускорить работу приложения, поскольку мы можем извлекать меньшее количество записей. Следовательно, по сети между клиентом и сервером передается меньше данных, что сокращает время задержки.

В этом руководстве мы создадим код на PHP, который подключается к базе данных, и реализуем пагинацию с использованием базы данных MySQL и ее конструкции LIMIT clause.

Предварительные требования

Для выполнения этого руководства вам понадобятся:

Последняя запущенная и работающая версия Ubuntu. Следуйте руководству Как настроить сервер Ubuntu, если вам нужна помощь.
Также вам понадобятся установленные PHP и MySQL. Для настройки PHP и MySQL у нас есть руководство: Настройка стека LAMP – Linux Apache MySQL PHP.

Шаг 1 — Настройка пользователя базы данных и тестовой базы данных

В этом руководстве мы установим соединение с базой данных MySQL и извлечем записи. Эти записи будут отображаться на HTML-странице с помощью элемента таблицы. Для установления соединения и отображения записей мы будем использовать PHP-скрипт. Как только мы подготовим соединение с базой данных, мы протестируем наше приложение с пагинацией и без нее. Такое тестирование позволит нам понять, как пагинация работает на практике.

Для подключения к базе данных MySQL нам понадобится тестовая база данных MySQL и учетные данные пользователя для подключения к ней. На этом шаге вы создадите пользователя без прав root для вашей базы данных MySQL, тестовую базу данных и таблицу для тестирования PHP-скрипта.

Сначала войдите на свой сервер. Затем войдите на сервер MySQL, используя команду ниже:

mysql> sudo mysql -u root -p

1	mysql> sudo mysql -u root -p

Вам будет предложено ввести пароль пользователя root. Введите пароль и нажмите Enter, чтобы продолжить. Далее создайте тестовую базу данных с именем test_db, которую мы будем использовать в нашем руководстве. Чтобы создать новую базу данных, выполните команду ниже:

mysql> Create database test_db;

1	mysql> Create database test_db;

Вы увидите вывод от сервера MySQL о том, что была затронута одна строка. Далее нам нужно будет создать нового пользователя для этой базы данных. Мы предоставим этому пользователю все привилегии. Однако при работе с реальными приложениями обязательно ограничивайте привилегии. Мы назовем нашего пользователя test_user. В приведенной ниже команде замените PASSWORD на надежный пароль:

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

После создания пользователя обновите привилегии:

mysql> FLUSH PRIVILEGES;

1	mysql> FLUSH PRIVILEGES;

Теперь, когда и test_user, и test_db готовы, используйте test_db для создания таблиц. Чтобы переключиться на test_db, выполните команду ниже:

mysql> Use test_db;

1	mysql> Use test_db;

Как только мы увидим в выводе, что база данных изменена, мы готовы создать нашу таблицу. Мы создадим новую таблицу с именем Products для хранения продуктов. Для наших целей нам понадобятся только два столбца в этой таблице: product_id и product_name. Мы установим для product_id значение AUTO_INCREMENT, чтобы оно увеличивалось каждый раз, когда мы добавляем новый продукт. Другой столбец с именем product_name будет использоваться для хранения названия продукта. Столбец product_name будет использоваться для различения каждого продукта по названию:

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;

Теперь мы добавим несколько продуктов в эту только что созданную таблицу. Для этого выполните следующие команды:

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

Чтобы проверить, были ли эти продукты добавлены в таблицу, выполните следующую команду:

mysql> select * from products;

1	mysql> select * from products;

Если вы видите продукты, которые ввели выше, значит, всё готово для продолжения. Выйдите из базы данных MySQL с помощью следующей команды:

mysql> quit;

1	mysql> quit;

Теперь, когда мы настроили базу данных MySQL и ввели тестовые данные, мы готовы написать PHP-скрипт для подключения к этой базе данных и получения записей.

Шаг 2 – Получение и отображение записей без пагинации

Как обсуждалось выше, сначала мы отобразим записи без пагинации. Мы создадим PHP-скрипт, который подключается к базе данных MySQL и извлекает записи. Мы подключимся к test_db, которую мы создали выше, используя test_user, и извлечем записи.

Наша таблица products в данный момент содержит всего десять записей. Многим пользователям может быть неочевидно, нужна пагинация или нет. Однако тестирование приложения без пагинации подробно покажет, как она помогает улучшить общее впечатление от приложения. Мы также поймем, как разделение данных помогает создать лучший пользовательский опыт и снижает нагрузку на сервер.

На вашем PHP-сервере в корневом каталоге документов папки веб-сайта выполните следующую команду:

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

Теперь добавьте в этот файл следующее содержимое. Обязательно замените PASSWORD на значение пароля вашего пользователя 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();

}

Сохраните файл. Прежде чем двигаться дальше, мы резюмируем то, что сделали выше:

Сначала мы подключились к базе данных MySQL, и для этого мы использовали PHP Data Object (PDO) библиотеку. Мы использовали учетные данные пользователя MySQL, которые мы использовали выше для подключения к базе данных MySQL. PDO — это чрезвычайно полезная библиотека, которая используется для подключения к базе данных. Она упрощает кодирование уровня доступа к данным и позволяет подключаться к различным базам данных без необходимости масштабного рефакторинга приложения. PDO использует подготовленные выражения, что гарантирует безопасность. Таким образом, запросы выполняются безопасно.

Затем мы использовали PDO API для выполнения select * from products оператора. $pdo->setAttribute(PDO::ATTR_EMULATE_PREPARES,false) гарантирует, что типы данных в PHP совпадают с типами данных в базе данных. Таким образом, product_id и product_name отображаются как целое число и строка соответственно.
Инструкция $pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION); указывает PDO вызывать исключение в случае ошибки. Благодаря этому мы можем легко перехватить исключение в блоке try … catch и обработать его соответствующим образом.

Теперь, когда наш скрипт готов, нам нужно его запустить. Для этого откройте браузер и перейдите по URL-адресу: /var/www/html/pagination_test.php. Обязательно добавьте к URL-адресу IP-адрес вашего сервера. Если вы запускаете локально, ваш URL-адрес примет вид http://localhost/var/www/html/pagination_test.php.

Вы увидите следующий вывод:

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

Здесь наши продукты возвращаются и отображаются на странице. Теперь представьте, если бы у нас были сотни продуктов. Это привело бы к длинному циклу для извлечения данных и их отображения на странице. Это увеличит время загрузки страницы.

В следующем разделе мы изменим PHP-скрипт и добавим MySQL LIMIT оператор. Это решит проблему отображения большого количества записей. Мы добавим ссылки пагинации, чтобы пользователь мог перемещаться по страницам.

Шаг 3 – Настройка пагинации с помощью PHP

На этом шаге мы настроим пагинацию с помощью PHP для отображения данных с использованием нескольких таблиц. Чтобы реализовать пагинацию, мы изменим скрипт, написанный на предыдущем шаге.

Мы будем использовать LIMIT оператор базы данных MySQL. Прежде чем добавлять его в скрипт, давайте рассмотрим пример MySQL LIMIT синтаксиса:

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

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

В приведенном выше операторе мы видим, что LIMIT оператор имеет аргументы offset и records. offset указывает, сколько записей нужно пропустить, а records указывает максимальное количество записей для отображения на странице или извлечения из базы данных.

Теперь, когда мы знаем, как работает пагинация, давайте посмотрим, как мы будем ее реализовывать. Мы будем отображать по три записи на странице. Таким образом, если в нашей базе данных десять записей, у нас должно быть четыре страницы, если разделить общее количество записей на количество записей на странице. Поскольку результат может быть не целым числом, мы будем использовать PHP-функцию Ceil для округления до ближайшего большего целого числа, чтобы получить целое число. Ниже приведен простой фрагмент кода для демонстрации функции Ceil:

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

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

Теперь мы добавим код для пагинации. Чтобы включить пагинацию и добавить навигационные ссылки, откройте файл /var/www/html/pagination_test.php с помощью следующей команды:

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

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

Затем замените содержимое этого файла следующим кодом:

<?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);
    
        /* Начало информации о пагинации */
        $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;
        /* Конец информации о пагинации */
 
        $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>";

        /* Начало навигации */
        echo "<table border='1' align='center'>";

        echo "<tr>";

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

        if ($page+1 <= $total_pages) {
            echo "<td><a href=".$_SERVER['PHP_SELF']."?page=".($page + 1).">Вперед</a></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);

/* Начало информации о пагинации */

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

/* Конец информации о пагинации */

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

/* Начало навигации */

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

echo "<tr>";

if ($page-1 >= 1) {

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

}

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

echo "<td><a href=".$_SERVER['PHP_SELF']."?page=".($page + 1).">Вперед</a></td>";

}

echo "</tr>";

echo "</table>";

/* Конец навигации */

}

catch(PDOException $e) {

echo $e->getMessage();

}

Вот краткое описание этого кода:

Мы должны хранить информацию о странице, на которой сейчас находимся. Для этого мы используем $page переменную. Переменная $page используется для извлечения страницы, на которой мы сейчас находимся, с помощью переменной $_GET[‘page’] .
Далее нам нужно указать, сколько записей мы отображаем на странице. Для этого мы используем $per_page переменная. Здесь мы отображаем только три товара на странице.
Как обсуждалось выше, нам необходимо знать общее количество записей, существующих в базе данных. Это необходимо для того, чтобы мы могли определить общее количество страниц. $total_records переменная содержит эту информацию.
Наконец, нам нужно знать, сколько записей следует пропустить. Например, если мы находимся на второй странице, нам нужно пропустить первые три записи в базе данных. Мы используем $offset переменную для хранения этой информации. Мы вычисляем эту информацию при каждой загрузке страницы по формуле $offset=($page-1)*$per_page. Если вам нужно отобразить другое количество элементов, вы можете указать значение в $per_page переменной.
Для отображения кнопок навигации мы используем следующий код:

. . .
if( $page-1>=1) {
    echo "<td><a href=".$_SERVER['PHP_SELF']."?page=".($page-1).">Предыдущая</a></td>";
    }

if( $page+1<=$total_pages) {
    echo "<td><a href=".$_SERVER['PHP_SELF']."?page=".($page+1).">Следующая</a></td>";
}

. . .

. . .

if( $page-1>=1) {

echo "<td><a href=".$_SERVER['PHP_SELF']."?page=".($page-1).">Предыдущая</a></td>";

}

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

echo "<td><a href=".$_SERVER['PHP_SELF']."?page=".($page+1).">Следующая</a></td>";

}

. . .

Логика заключается в том, что $page используется для хранения текущей страницы. Если мы хотим отобразить ссылку на предыдущую страницу, скрипт вычтет единицу из значения $page переменной. Если результат больше или равен единице, на странице отображается ссылка на предыдущую страницу.

Аналогично, чтобы отобразить ссылку на следующую страницу, мы прибавляем единицу к $page переменной. Здесь мы гарантируем, что результат сложения не превысит общее количество страниц в $total_pages переменной.

После этого пришло время проверить страницу. Откройте браузер и перейдите на страницу по URL-адресу, который вы использовали ранее:

http://your_server_ip/pagination_test.php

1	http://your_server_ip/pagination_test.php

Вы увидите следующий результат:

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

table2

table

Здесь вы наконец реализовали пагинацию с использованием конструкции LIMIT в MySQL в PHP-скрипте. Реализовав пагинацию в своем приложении, вы получите более удобный способ навигации по множеству записей.

Заключение

В этом руководстве мы рассмотрели, как реализовать пагинацию с помощью PHP и MySQL на Ubuntu. Используя эти шаги, вы сможете создавать приложения, которые могут адекватно отображать большое количество записей, разделенных по страницам для эффективного управления.

Кроме того, у нас есть другие полезные руководства по PHP и MySQL на Ubuntu:

Приятной работы!

Shreyas Patil

Автор · CloudSigma

Preslav Dobrev — креативный дизайнер в CloudSigma, сосредоточенный на формировании последовательного корпоративного образа с помощью традиционных и инновационных маркетинговых каналов. Он умело сочетает художественное видение со стратегическим маркетингом, создавая убедительные истории бренда.

Применение пагинации для отображения нескольких записей с использованием MySQL и PHP на Ubuntu 20.04

Введение

Предварительные требования

Шаг 1 — Настройка пользователя базы данных и тестовой базы данных

Шаг 2 – Получение и отображение записей без пагинации

Шаг 3 – Настройка пагинации с помощью PHP

Заключение

Shreyas Patil

Комментарии