Постраничный вывод данных на MySQL и PHP в Ubuntu 18.04

LAMP Stack, LEMP Stack, mySQL, PHP, Ubuntu | Amber | 1 Comment

Разбиение на страницы (или пагинация) выполняется путем ограничения количества возвращаемых строк в наборе записей. В результате вывод делится на отдельные упорядоченные страницы, что обеспечивает удобную навигацию между ними. При работе с большими наборами данных вы можете настроить постраничный вывод и получить определенное количество строк на каждой странице. Также пагинация поможет избежать ошибок при слишком большом количестве пользователей: например, во всем интернет-магазине могут содержаться тысячи товаров, но за счет пагинации количество товаров на одной странице сокращается – ведь обычно пользователям нужен конкретный товар и они не просматривают всё.

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

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

Требования

• Сервер Ubuntu 18.04, настроенный по этому мануалу.
• Стек LAMP, установить который вам поможет этот мануал.

1: Создание тестовой базы данных и пользователя

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

Для работы нам нужен новый пользователь MySQL и тестовая база данных. сейчас мы создадим  пользователя без прав root, образец базы данных и таблицы для тестирования сценария PHP.

Для начала подключитесь к виртуальному серверу. Затем войдите в MySQL с помощью следующей команды:

sudo mysql -u root -p

Введите root пароль MySQL и нажмите ENTER, чтобы продолжить. Затем вы увидите командную строку MySQL. Чтобы создать тестовую БД (мы назовем ее test_db), выполните следующую команду:

Create database test_db;

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

Query OK, 1 row affected (0.00 sec)

Затем создайте пользователя test_user и предоставьте ему все права на test_db. Вместо PASSWORD выберите надежный пароль:

GRANT ALL PRIVILEGES ON test_db.* TO 'test_user'@'localhost' IDENTIFIED BY 'PASSWORD';
Query OK, 1 row affected (0.00 sec)

Сбросьте привилегии MySQL с помощью команды:

FLUSH PRIVILEGES;
Query OK, 1 row affected (0.00 sec)

Затем перейдите в базу данных test_db, чтобы начать работать непосредственно с тестовой БД:

Use test_db;
Database changed

Теперь создайте таблицу products. В этой таблице будут храниться тестовые данные о продуктах. Для этого мануала нам понадобятся только два столбца данных. Столбец product_id будет содержать уникальный идентификатор каждой записи, а product_name – название продукта:

Create table products (product_id BIGINT PRIMARY KEY, product_name VARCHAR(50) NOT NULL ) Engine = InnoDB;
Query OK, 0 rows affected (0.02 sec)

Чтобы добавить 10 строк с тестовыми данными, используйте следующие выражения SQL:

Insert into products(product_id, product_name) values ('1', 'WIRELESS MOUSE');
Insert into products(product_id, product_name) values ('2', 'BLUETOOTH SPEAKER');
Insert into products(product_id, product_name) values ('3', 'GAMING KEYBOARD');
Insert into products(product_id, product_name) values ('4', '320GB FAST SSD');
Insert into products(product_id, product_name) values ('5', '17 INCHES TFT');
Insert into products(product_id, product_name) values ('6', 'SPECIAL HEADPHONES');
Insert into products(product_id, product_name) values ('7', 'HD GRAPHIC CARD');
Insert into products(product_id, product_name) values ('8', '80MM THERMAL PRINTER');
Insert into products(product_id, product_name) values ('9', 'HDMI TO VGA CONVERTER');
Insert into products(product_id, product_name) values ('10', 'FINGERPRINT SCANNER');

Вы получите такой вывод:

Query OK, 1 row affected (0.02 sec)

Чтобы убедиться, что записи добавлены в таблицу, запустите команду:

select * from products;

Вы увидите такой вывод:

+------------+-----------------------+
| 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)

Закройте MySQL:

quit;

Имея тестовую БД, таблицы и данные для дальнейшей работы, вы можете создать сценарий PHP для отображения этих данных на веб-странице.

2: Стандартное отображение записей MySQL

Теперь мы создадим сценарий PHP, который подключается к базе данных MySQL и отображает строки в веб-браузере. Пока что код PHP будет стандартным, без пагинации – так вы посмотрите, как записи отображаются на одной странице, и позже сможете сравнить результаты. Хотя для тестирования у нас есть всего десять записей, что не так уж много, этого будет достаточно, чтобы вы смогли оценить преимущества пагинации (улучшение работы пользователей и снижение нагрузки на сервер).

Создайте файл для сценария 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();
}
?>

Сохраните и закройте файл.

Этот скрипт подключается к базе данных MySQL с помощью ее учетных данных, используя библиотеку PDO (PHP Data Object).

PDO – это легкий интерфейс для подключения к базам данных. Уровень доступа к данным является более гибким и может работать с различными базами данных при незначительных изменениях кода. PDO обладает большей безопасностью, поскольку поддерживает подготовленные операторы, что позволяет выполнять запросы быстрее и надежнее.

Затем API-интерфейс PDO выполняет оператор select * from products и выводит продукты в таблице HTML без разбивки на страницы. Строка

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

выводит типы данных в том виде, в каком они появляются в базе. Это означает, что PDO вернет product_id как целое число, а product_name как строку. Строка

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

позволяет PDO выдавать исключение, если обнаружена ошибка. Для простоты отладки ошибка захватывается внутри PHP-блока try {} … catch {}.

Чтобы выполнить созданный вами сценарий PHP /var/www/html/pagination_test.php, перейдите по следующему URL-адресу, заменив your-server-IP внешним IP-адресом вашего сервера:

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

Вы увидите страницу с таблицей продуктов.

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

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

3: Пагинация вывода с помощью PHP

На этом этапе наша цель – разделить данные на несколько управляемых страниц. Это не только улучшит читабельность вывода, но и более эффективно использует ресурсы сервера. Мы отредактируем скрипт PHP, который создали в предыдущем разделе, и добавим в него блок для пагинации.

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

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

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

В данном примере на странице будет отображаться три записи. Чтобы получить общее количество страниц, вы должны разделить общее количество записей в вашей таблице на количество строк, которые будут отображаться на одной странице. Затем полученное значение округляется до ближайшего целого числа с помощью PHP функции Ceil, как показано в следующем примере кода PHP:

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

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

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);
/* 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();
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).">Previous</a></td>";

}

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

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

}

echo "</tr>";

echo "</table>";

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

В этом файле мы использовали дополнительные параметры пагинации:

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

Снова отройте свой IP-адрес в браузере (замените your_server_ip внешним IP-адресом вашего сервера):

http://your_server_ip/pagination_test.php

Вы увидите несколько кнопок навигации внизу страницы. На первой странице не будет кнопки Previous, а на последней странице не будет кнопки Next. Также обратите внимание, как изменяется параметр page URL при посещении каждой страницы.

Навигационные ссылки внизу страницы добавляются при помощи следующего фрагмента кода PHP:

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

Здесь переменная $page определяет номер текущей страницы. Затем, чтобы получить предыдущую страницу, код вычтет 1 от переменной. Если вы находитесь на странице 2, формула (2-1) даст результат 1, и это будет предыдущая страница, которая появится в ссылке. Однако имейте в виду, предыдущая страница будет отображаться только в том случае, если результат будет больше или равен 1.

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

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

Заключение

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

Читайте также:

• Доступ к логам ошибок MySQL
• Запросы в MySQL
• Устранение неполадок в запросах MySQL