MySQL / 18. Обработка ввода через Web с помощью MySQL

Формирование ссылок на предыдущую и следующую страницы

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

Решение
Разбейте вывод запроса на несколько страниц и предоставьте ссылки, позволяющие пользователю перемещаться со страницы на страницу.

Обсуждение
Если запрос находит большой объем записей, то просматривать их все на одной веб-странице становится неудобно. В подобных случаях разумно распределить результат по нескольким страницам. Постраничный вывод избавляет пользователя от необходимости воспринимать большой объем данных сразу, но его сложнее реализовать.

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

SELECT name, abbrev, statehood, pop FROM states ORDER BY name;

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

SELECT name, abbrev, statehood, pop FROM states ORDER BY name LIMIT skip,select;

Возникает два вопроса. Во-первых, необходимо определить соответствующие значения skip и select. Во-вторых, следует сформировать ссылки, указывающие на другие страницы результата запроса. Можно выводить страницу, приводя ссылки только на предыдущую и следующую страницы. Для этого необходимо знать, существуют ли записи, предшествующие или следующие за представленными на выведенной странице. Можно предлагать ссылки на все доступные страницы. Тогда пользователь сможет сразу переходить на любую страницу, а не только на предыдущую или следующую. Реализация такого способа требует знания общего количества записей результирующего множества и количества строк на странице, чтобы можно было получить число страниц вывода.

Постраничный вывод со ссылками на предыдущуюи следующую страницы
Сценарий state_pager1.pl представляет записи таблицы states постранично, предлагая ссылки только на предыдущую и следующую страницы. Для указанной страницы необходимые ссылки определяются так:

• Ссылка на «предыдущую страницу» необходима, если в результирующем множестве содержатся записи, предшествующие отображенным на ука-занной странице. Если текущая страница начинается с записи 1, таких записей нет.

• Ссылка на «следующую страницу» необходима, если в результирующем множестве существуют записи, следующие за представленными на текущей странице. Вы можете проверить наличие таких записей, выполнив запрос SELECT COUNT(*), чтобы посмотреть, сколько всего записей найдено запросом. Есть и другой способ – выбрать на одну запись больше, чем нужно.

Например, если вы выводите по 10 записей на странице, попробуйте выбрать 11. Если получите 11, значит, существует следующая страница. Если получено 10 или меньше записей, ее нет. Сценарий state_pager1.pl использует второй подход.

Чтобы определить текущее положение в результирующем множестве и количество отображаемых записей, state_pager1.pl ищет входные параметры start и per_page. При первом вызове сценария этих параметров нет, поэтому они инициализируются в 1 и 10 соответственно. После этого сценарий формирует ссылки «предыдущая страница» и «следующая страница», указывающие на него же, URL которых содержит необходимые параметры для выбора предыдущей или последующей части результирующего множества.

#! /usr/bin/perl -w
# state_pager1.pl – постраничное отображение штатов со ссылками
# на предыдущую/следующую страницы
use strict;
use lib qw(/usr/local/apache/lib/perl);
use CGI qw(:standard escape escapeHTML);
use Cookbook;
my $title = "Paged US State List";
my $page = header ()
. start_html (-title => $title, -bgcolor => "white")
. h3 ($title);
my $dbh = Cookbook::connect ();
# Проверка параметров, которые определяют, в каком именно месте вывода мы находимся.
# По умолчанию – начало результирующего множества, 10 записей на странице,
# если параметры отсутствуют или заданы некорректно.
my $start = param ("start");
$start = 1
if !defined ($start) || $start !~ /^\d+$/ || $start < 1;
my $per_page = param ("per_page");
$per_page = 10
if !defined ($per_page) || $per_page !~ /^\d+$/ || $per_page < 1;;
# Если start > 1, то необходима активная ссылка на предыдущую страницу.
# Чтобы определить, есть ли следующая страница, пытаемся выбрать на одну запись
# больше, чем требуется. Если удается, выводим только первые $per_page страниц,
# но добавляем активную ссылку на следующую страницу.# Выбираем записи текущей страницы результирующего множества и пытаемся
# получить дополнительную запись. (Не для того, чтобы ее выводить, а просто
# для проверки существования следующей страницы.)
my $query = sprintf (
"SELECT name, abbrev, statehood, pop
FROM states
ORDER BY name LIMIT %d,%d",
$start - 1, # количество пропускаемых записей
$per_page + 1); # количество выбираемых записей
my $tbl_ref = $dbh->selectall_arrayref ($query);
$dbh->disconnect ();
# Вывод результатов в виде таблицы HTML.
my @rows;
push (@rows, Tr (th (["Name", "Abbrevation", "Statehood", "Population"])));
for (my $i = 0; $i < $per_page && $i < @{$tbl_ref}; $i++)
{
# получаем значения в строке $i
my @cells = @{$tbl_ref->[$i]}; # получаем значения в строке $i
# сопоставляем значениям HTML-закодированные значения или  
# если значение null/пустое
@cells = map {
defined ($_) && $_ ne "" ? escapeHTML ($_) : " "
} @cells;
# добавляем ячейки в таблицу
push (@rows, Tr (td (\@cells)));
}
$page .= table ({-border => 1}, @rows) . br ();
# Если мы не в начале результата запроса, выводим активную ссылку
# на предыдущую страницу, иначе выводим статический текст.
if ($start > 1) # активная ссылка
{
my $url = sprintf ("%s?start=%d;per_page=%d",
url (),
$start - $per_page,
$per_page);
$page .= "[" . a ({-href => $url}, "previous page") . "] ";
}
else # статический текст
{
$page .= "[previous page]";
}
# Если мы получили дополнительную запись, выводим активную ссылку
# на следующую страницу, иначе выводим статический текст.
if (@{$tbl_ref} > $per_page) # активная ссылка
{
my $url = sprintf ("%s?start=%d;per_page=%d",url (),
$start + $per_page,
$per_page);
$page .= "[" . a ({-href => $url}, "next page") . "]";
}
else # статический текст
{
$page .= "[next page]";
}
$page .= end_html ();
print $page;
exit (0);

Постраничный вывод со ссылками на каждую страницу
Сценарий state_pager2.pl во многом похож на state_pager1.pl, но в данном случае страница вывода содержит ссылки на все страницы результирующего множества запроса. Для предоставления таких ссылок необходимо знать, сколько всего строк в результате. Сценарий state_pager2.pl определяет это, выполняя запрос SELECT COUNT(*). Поскольку известно общее количество строк, не нужно выбирать дополнительную запись при извлечении очередной порции результата для отображения.

Опустим части state_pager2.pl, повторяющие state_pager1.pl, и рассмотрим только извлечение записей и формирование ссылок:

# Определяем общее количество записей.
my $total_recs = $dbh->selectrow_array ("SELECT COUNT(*) FROM states");
# Выбираем записи текущей страницы результата.
my $query = sprintf (
"SELECT name, abbrev, statehood, pop
FROM states
ORDER BY name LIMIT %d,%d",
$start - 1, # количество пропускаемых записей
$per_page); # количество выбираемых записей
my $tbl_ref = $dbh->selectall_arrayref ($query);
$dbh->disconnect ();
# Вывести результаты в виде таблицы HTML.
my @rows;
push (@rows, Tr (th (["Name", "Abbrevation", "Statehood", "Population"])));
for (my $i = 0; $i < @{$tbl_ref}; $i++)
{
# получаем значения в строке $i
my @cells = @{$tbl_ref->[$i]};
# сопоставляем значениям HTML-закодированные значения или  
# если значение null/пусто@cells = map {
defined ($_) && $_ ne "" ? escapeHTML ($_) : " "
} @cells;
# добавляем ячейки в таблицу
push (@rows, Tr (td (\@cells)));
}
$page .= table ({-border => 1}, @rows) . br ();
# Формируем ссылки на все страницы результирующего множества. Активны все
# ссылки, кроме ссылки на текущую страницу, которая выводится как статический
# текст. Формат видимого текста ссылки - "[m to n]", где m и n – номера первой
# и последней отображаемых на странице записей.
for (my $first = 1; $first <= $total_recs; $first += $per_page)
{
my $last = $first + $per_page - 1;
$last = $total_recs if $last > $total_recs;
my $label = "$first to $last";
my $link;
if ($first != $start) # активная ссылка
{
my $url = sprintf ("%s?start=%d;per_page=%d",
url (),
$first,
$per_page);
$link = a ({-href => $url}, $label);
}
else # статический текст
{
$link = $label;
}
$page .= "[$link] ";
}

Статьи по MySQL на эту тему:

Ведение журнала Apache с помощью MySQL
Журнал доступа к веб-странице
Сортировка результатов запроса по произвольному столбцу
Счетчики посещаемости веб-страниц