MySQL / 17. Внедрение результатов запросов в веб-страницы

Представление результатов запроса в виде списков

Задача
Результат запроса содержит ряд значений, которые надо структурировать в виде списка.

Решение
Заключите элементы списка в теги HTML, соответствующие требуемому типу списка.

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

Списки обычно состоят из открывающего и закрывающего тегов, окружающих набор элементов, каждый из которых помечен собственным тегом. Элементы списка естественным образом соответствуют строкам, полученным в запросе, поэтому создание HTML-списка из программы заключается в перекодировании результата запроса, добавлении к каждой строке нужных тегов и выводе открывающего и закрывающего тегов. Наиболее распространены два подхода к созданию списков. Если вы хотите выводить список по мере получения результатов запроса, делайте так:

1. Выведите открывающий тег списка.

2. Выбирайте и выводите каждую строку результирующего множества как элемент списка с соответствующими тегами.

3. Выведите закрывающий тег списка. Другой подход состоит в формировании списка в памяти:1. Сохраните элементы списка в массиве.

2. Передайте массив в функцию генерации списка, которая добавит необходимые теги, затем выведите результат.

Следующий пример иллюстрирует оба подхода.

Нумерованные списки
Нумерованный список (ordered list) состоит из элементов, расположенных в определенном порядке. Броузеры обычно отображают такие списки как набор элементов с порядковыми номерами:

1. Первый элемент
2. Второй элемент
3. Третий элемент

Вам не надо задавать номера элементов, так как броузер проставляет их автоматически. В HTML нумерованный список начинается и заканчивается тегами <ol> и </ol> соответственно и состоит из элементов, заключенных в теги <li> и </li>:

<ol>
<li>Первый элемент</li>
<li>Второй элемент</li>
<li>Третий элемент</li>
</ol>

Предположим, у вас есть таблица ingredient, содержащая пронумерованные ингредиенты кулинарного рецепта:

+--+------------------------------------+
| id | item                                          |
+--+------------------------------------+
| 1 | 3 cups flour                               |
| 2 | 1/2 cup raw ("unrefined") sugar |
| 3 | 3 eggs                                       |
| 4 | pinch (< 1/16 teaspoon) salt       |
+-+-------------------------------------+

В таблице есть столбец идентификаторов id, но для представления рецепта в виде нумерованного списка достаточно вывести только текстовые значения, так как броузер пронумерует их самостоятельно. Значения содержат специальные символы " и <, поэтому придется перекодировать их, прежде чем добавлять теги, превращающие значения в элементы списка. Результат должен выглядеть так:

<ol>
<li>3 cups flour</li>
<li>1/2 cup raw ("unrefined") sugar</li>
<li>3 eggs</li>
<li>pinch (< 1/16 teaspoon) salt</li>

</ol>Один из путей создания такого списка в программе – вывод в HTML по мере выборки строк результирующего множества. Вот как это можно сделать в JSP-странице с использованием тегов JSTL:

<sql:query var="rs" dataSource="${conn}">
SELECT item FROM ingredient ORDER BY id
</sql:query>
<ol>
<c:forEach var="row" items="${rs.rows}">
<li><c:out value="${row.item}" /></li>
</c:forEach>
</ol>

В PHP ту же операцию можно проделать так:

$query = "SELECT item FROM ingredient ORDER BY id";
$result_id = mysql_query ($query, $conn_id);
if (!$result_id)
die (htmlspecialchars (mysql_error ($conn_id)));
print ("<ol>\n");
while (list ($item) = mysql_fetch_row ($result_id))
print ("<li>" . htmlspecialchars ($item) . "</li>\n");
mysql_free_result ($result_id);
print ("</ol>\n");

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

Подход, использованный в предыдущих примерах, предполагает чередование выборки записей с генерацией вывода. Возможен и другой способ: раздельное выполнение операций, при котором данные сначала выбираются, затем выводятся. Запросы для разных списков могут различаться, но генерация самого списка выполняется, как правило, единообразно. Написав функцию генерации списка, вы сможете использовать ее с разными запросами. Две вещи, которые должна выполнять такая функция, – это перекодирование элементов (если это еще не сделано) и добавление требуемых HTML-
тегов. В PHP, например, функцию make_ordered_list() можно реализовать следующим образом (она принимает в качестве аргумента массив элементов списка и возвращает список в виде строки):

function make_ordered_list ($items, $encode = TRUE)
{
if (!is_array ($items))
return ("make_ordered_list: items argument must be an array");
$str = "<ol>\n";
reset ($items);
while (list ($k, $v) = each ($items))
{
if ($encode)$v = htmlspecialchars ($v);
$str .= "<li>$v</li>\n";
}
$str .= "</ol>\n";
return ($str);
}

Написав такую функцию, вы можете сделать выборку и вывести результат в HTML, например, так:

# получить элементы списка
$query = "SELECT item FROM ingredient ORDER BY id";
$result_id = mysql_query ($query, $conn_id);
if (!$result_id)
die (htmlspecialchars (mysql_error ($conn_id)));
$items = array ();
while (list ($item) = mysql_fetch_row ($result_id))
$items[] = $item;
mysql_free_result ($result_id);
# сформировать HTML-список
print (make_ordered_list ($items));

В Python подобная функция будет выглядеть так:

def make_ordered_list (items, encode = 1):
if type (items) not in (types.ListType, types.TupleType):
return ("make_ordered_list: items argument must be a list")
list = "<ol>\n"
for item in items:
if item is None: # обработать возможный пустой элемент
item = ""
# убедиться, что элемент – строка, и при необходимости перекодировать
if type (item) is not types.StringType:
item = `item`
if encode:
item = cgi.escape (item, 1)
list = list + "<li>" + item + "</li>\n"
list = list + "</ol>\n"
return list

А использовать ее надо так:

# получить элементы списка
query = "SELECT item FROM ingredient ORDER BY id"
cursor = conn.cursor ()
cursor.execute (query)
items = []
for (item,) in cursor.fetchall ():
items.append (item)
cursor.close ()
# сформировать HTML-список

print make_ordered_list (items)В обоих вариантах функции make_ordered_list() на PHP и Python проверяется, является ли первый аргумент массивом. Если нет, возвращается строка с описанием ошибки. Благодаря возврату содержательной строки проблема сразу становится очевидной при взгляде на веб-страницу, сформированную функцией. Если хотите, можете возвращать другие признаки ошибки или генерировать исключение, или завершать выполнение сценария.

Второй аргумент функции make_ordered_list() определяет, надо ли перекодировать элементы списка. Проше всего позволить функции определять необходимость этого самостоятельно (поэтому по умолчанию и передается «истина»). Но если вы формируете список из элементов, которые уже содержат теги HTML, то не захотите, чтобы функция преобразовывала специальные символы, содержащиеся в этих тегах. Например, если вы создаете список гиперссылок, то каждый элемент списка будет содержать тег (a). Чтобы исключить перекодирование тега в
Конечно, если в вашем API есть функции генерации HTML-структур, вам не придется писать их самостоятельно. Это относится к модулю Perl CGI.pm: вызовите функцию li(), которая создает элемент, добавляя к нему открывающий и закрывающий теги, сохраните элементы в массиве, затем передайте этот массив в функцию ol(), которая добавит открывающий и закрывающий теги списка:

my $query = "SELECT item FROM ingredient ORDER BY id";
my $sth = $dbh->prepare ($query);
$sth->execute ();
my @items = ();
while (my $ref = $sth->fetchrow_arrayref ())
{
# обработать возможный элемент NULL (undef)
my $item = (defined ($ref->[0]) ? escapeHTML ($ref->[0]) : "");
push (@items, li ($item));
}
print ol (@items);

Преобразовывать неопределенные значения (undef или NULL) в пустые строки необходимо для того, чтобы исключить генерацию предупреждений о неинициализированных значениях, когда Perl запускается с опцией -w. (Таблица ingredient не содержит значений NULL, но этот способ полезен при работе с таблицами, в которых такие значения встречаются.)

В предыдущем примере выборка записей и генерация HTML выполняются попеременно. Чтобы разделить операции выборки и формирования кода, поместите элементы в массив. Затем передайте массив по ссылке функции li(), а ее результат – функции ol():

# получить элементы списка
my $query = "SELECT item FROM ingredient ORDER BY id";
my $item_ref = $dbh->selectcol_arrayref ($query);
# сгенерировать список HTML, обрабатывая возможные элементы NULL (undef)$item_ref = [ map { defined ($_) ? escapeHTML ($_) : "" } @{$item_ref} ];
print ol (li ($item_ref));

Имейте в виду следующие два свойства функции li():

• Она не выполняет никакого HTML-кодирования; вы должны сделать это самостоятельно.

• Она может обработать как отдельное значение, так и массив. Но если вы передаете ей массив, вы должны передать его по ссылке. Тогда функция добавит теги <li> и </li> к каждому элементу массива, затем склеит их и вернет результирующую строку. Если вы передадите массив не по ссылке, а по значению, функция сначала объединит элементы, а затем заключит результат в единственную пару тегов, что, скорее всего, не соответствует вашим ожиданиям. Аналогичное поведение свойственно и некоторым другим функциям CGI.pm, которые могут оперировать как одним, так и несколькими значениями. Например, функция обработки табличных данных td() добавляет единственную пару тегов <td> и </td>, если получает скаляр или список. Если же ей передать ссылку на список, она добавит теги к каждому из его элементов.

Маркированные списки
Маркированный список (unordered list) аналогичен нумерованному, за исключением того, что броузер отображает его элементы, помечая их одним и тем же символом (маркером):

• Первый элемент
• Второй элемент
• Третий элемент

Маркированные списки являются неупорядоченными в том смысле, что символ маркера не содержит информацию о порядке элементов. Разумеется, вы можете вывести элементы в нужном вам порядке. Код HTML для маркированного списка аналогичен коду нумерованного списка, только вместо тегов <ol> и </ol> используются <ul> и </ul>:

<ul>
<li>Первый элемент</li>
<li>Второй элемент</li>
<li>Третий элемент</li>
</ul>

В тех API, где вы непосредственно выводите теги, используйте ту же процедуру, что и для нумерованных списков, но вместо <ol> и </ol> выводите <ul> и </ul>. Вот пример на JSP:

<sql:query var="rs" dataSource="${conn}">
SELECT item FROM ingredient ORDER BY id
</sql:query>
<ul>
<c:forEach var="row" items="${rs.rows}">
<li><c:out value="${row.item}" /></li></c:forEach>
</ul>

В Perl маркированный список создается функцией ul() модуля CGI.pm:

# получить элементы списка
my $query = "SELECT item FROM ingredient ORDER BY id";
my $item_ref = $dbh->selectcol_arrayref ($query);
# сгенерировать список HTML, обрабатывая возможные элементы NULL (undef)$item_ref = [ map { defined ($_) ? escapeHTML ($_) : "" } @{$item_ref} ];
print ul (li ($item_ref));

Если вы пишете собственную функцию для маркированного списка, то можете адаптировать функцию формирования нумерованного списка. Например, обе версии функции make_ordered_list() для PHP и Python легко превращаются в make_unordered_list(), так как отличие заключается только в открывающем и закрывающем тегах.

Списки определений
Элементы списка определений (definition list) состоят из двух частей – термина и его определения. Здесь слова «термин» и «определение» можно толковать сколь угодно широко: вы можете размещать здесь любую информацию. Например, в приведенной ниже таблице doremi каждой ноте музыкальной гаммы (note) сопоставлена мнемоническая фраза (mnemonic), облегчающая запоминание (которая в действительности определением не является):

+--+------+------------------------------+
| id | note | mnemonic                        |
+--+------+------------------------------+
| 1 | do    | A deer, a female deer          |
| 2 | re     | A drop of golden sun         |
| 3 | mi     | A name I call myself            |
| 4 | fa     | A long, long way to run      |
| 5 | so     | A needle pulling thread       |
| 6 | la      | A note to follow so             |
| 7 | ti       | I drink with jam and bread |
+--+------+------------------------------+

Тем не менее столбцы note и mnemonic могут быть представлены списком определений:

do
A deer, a female deer
re
A drop of golden sun
mi
A name I call myself
fa
A long, long way to run
so
A needle pulling thread
la
A note to follow so
ti
I drink with jam and bread

Код HTML для списка определений начинается и заканчивается тегами <dl> и </dl> соответственно. Каждый элемент содержит термин, заключенный в теги <dt> и </dt>, и определение, помещенное в теги <dd> и </dd>:

<dl>
<dt>do</dt> <dd>A deer, a female deer</dd>
<dt>re</dt> <dd>A drop of golden sun</dd>
<dt>mi</dt> <dd>A name I call myself</dd>
<dt>fa</dt> <dd>A long, long way to run</dd>
<dt>so</dt> <dd>A needle pulling thread</dd>
<dt>la</dt> <dd>A note to follow so</dd>
<dt>ti</dt> <dd>I drink with jam and bread</dd>
</dl>

В JSP-страницах список определений можно сгенерировать так:

<sql:query var="rs" dataSource="${conn}">
SELECT note, mnemonic FROM doremi ORDER BY note
</sql:query>
<dl>
<c:forEach var="row" items="${rs.rows}">
<dt><c:out value="${row.note}" /></dt>
<dd><c:out value="${row.mnemonic}" /></dd>
</c:forEach>
</dl>

В PHP можно сделать так:

$query = "SELECT item FROM ingredient ORDER BY id";
$result_id = mysql_query ($query, $conn_id);
if (!$result_id)
die (htmlspecialchars (mysql_error ($conn_id)));
print ("<dl>\n");
while (list ($note, $mnemonic) = mysql_fetch_row ($result_id))
{
print ("<dt>" . htmlspecialchars ($note) . "</dt>\n");
print ("<dd>" . htmlspecialchars ($mnemonic) . "</dt>\n");
}
mysql_free_result ($result_id);
print ("</dl>\n");

Или можно написать функцию, принимающую массивы терминов и определений и возвращающую список в виде строки:

function make_definition_list ($terms, $definitions, $encode = TRUE)
{
if (!is_array ($terms))
return ("make_definition_list: terms argument must be an array");
if (!is_array ($definitions))
return ("make_definition_list: definitions argument must be an array");
if (count ($terms) != count ($definitions))
return ("make_definition_list: term and definition list size mismatch");
$str = "<dl>\n";
reset ($terms);
reset ($definitions);
while (list ($dtk, $dtv) = each ($terms))
{list ($ddk, $ddv) = each ($definitions);
if ($encode)
{
$dtv = htmlspecialchars ($dtv);
$ddv = htmlspecialchars ($ddv);
}
$str .= "<dt>$dtv</dt>\n<dd>$ddv</dd>\n";
}
$str .= "</dl>\n";
return ($str);
}

Приведем пример использования функции make_definition_list():

# получить элементы списка
$query = "SELECT note, mnemonic FROM doremi ORDER BY id";
$result_id = mysql_query ($query, $conn_id);
if (!$result_id)
die (htmlspecialchars (mysql_error ($conn_id)));
$terms = array ();
$defs = array ();
while (list ($note, $mnemonic) = mysql_fetch_row ($result_id))
{
$terms[] = $note;
$defs[] = $mnemonic;
}
mysql_free_result ($result_id);
# сгенерировать список HTML
print (make_definition_list ($terms, $defs));

В Perl создайте термины и определения вызовом функций dt() и dd(), сохраните их в массиве и передайте его функции dl():

my $query = "SELECT note, mnemonic FROM doremi ORDER BY id";
my $sth = $dbh->prepare ($query);
$sth->execute ();
my @items = ();
while (my ($note, $mnemonic) = $sth->fetchrow_array ())
{
# обработать возможные значения NULL (undef)
$note = (defined ($note) ? escapeHTML ($note) : "");
$mnemonic = (defined ($mnemonic) ? escapeHTML ($mnemonic) : "");
push (@items, dt ($note));
push (@items, dd ($mnemonic));
}
print dl (@items);

Ниже приведен чуть более сложный пример. Каждый термин – это имя базы данных, а в соответствующем определении указано количество таблиц в ней. Значения получены выполнением запроса SHOW TABLES к каждой базе данных и подсчетом строк результата:

# получить список имен баз данных
my $db_ref = $dbh->selectcol_arrayref ("SHOW DATABASES");
my @items = ();
foreach my $db_name (@{$db_ref})
{
# получить список имен таблиц базы данных; отключить RaiseError для этого запроса,
# чтобы сценарий не завершался, если пользователь не имеет доступа к текущей БД
$dbh->{RaiseError} = 0;
my $tbl_ref = $dbh->selectcol_arrayref ("SHOW TABLES FROM $db_name");
$dbh->{RaiseError} = 1;
my $tbl_count = (defined ($tbl_ref) # ошибка?
? @{$tbl_ref} . " tables" # нет, считаем таблицы
: "cannot access"); # да, сообщаем о проблеме
push (@items, dt (escapeHTML ($db_name)));
push (@items, dd (escapeHTML ($tbl_count)));
}
print dl (@items);

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

Немаркированные списки
В списках этого типа, обычно даже не рассматриваемых при обсуждении списков, вообще нет маркеров. Это просто набор элементов, каждый из которых расположен на отдельной строке. Создать немаркированный список очень просто: после каждой строки добавьте тег перевода строки. Вот пример на JSP:

<c:forEach var="row" items="${rs.rows}">
<c:out value="${row.item}" />

</c:forEach>

Если элементы уже помещены в массив, просто пройдите их в цикле. Например, в Perl, если элементы находятся в массиве @items, список генерируется так:

foreach my $item (@items)
{
# обработать возможные значения NULL (undef)
$item = (defined ($item) ? escapeHTML ($item) : "");
print $item . br ();
}

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

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

A
• Alabama
• Alaska
• Arizona
• Arkansas
C
• California
• Colorado
• Connecticut
D
• Delaware
...

Вот один из способов создания (на Perl) такого списка:

# получить список начальных букв
my $ltr_ref = $dbh->selectcol_arrayref (
"SELECT DISTINCT UPPER(LEFT(name,1)) AS letter
FROM states ORDER BY letter");
my @items = ();
# получить список штатов для каждой буквы
foreach my $ltr (@{$ltr_ref})
{
my $item_ref = $dbh->selectcol_arrayref (
"SELECT name FROM states WHERE LEFT(name,1) = ?
ORDER BY name", undef, $ltr);
$item_ref = [ map { escapeHTML ($_) } @{$item_ref} ];
# сформировать маркированный список названий штатов
my $item_list = ul (li ($item_ref));
# для каждого элемента списка определений начальная буква – термин,
# а список штатов - определение
push (@items, dt ($ltr));
push (@items, dd ($item_list));
}
print dl (@items);

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

my $sth = $dbh->prepare ("SELECT name FROM states ORDER BY name");
$sth->execute ();
my @items = ();
my @names = ();
my $cur_ltr = "";
while (my ($name) = $sth->fetchrow_array ())
{my $ltr = uc (substr ($name, 0, 1)); # начальная буква названия
if ($cur_ltr ne $ltr) # начать с новой буквы?
{
if (@names) # есть ли уже имена для предыдущих букв?
{
# для каждого элемента списка определений начальная буква – термин,
# а список штатов - определение
push (@items, dt ($cur_ltr));
push (@items, dd (ul (li (\@names))));
}
@names = ();
$cur_ltr = $ltr;
}
push (@names, escapeHTML ($name));
}
if (@names) # остались названия для последней буквы?
{
push (@items, dt ($cur_ltr));
push (@items, dd (ul (li (\@names))));
}
print dl (@items);

В третьем варианте используется единственный запрос, но разделены этапы подготовки данных и генерации HTML-кода:

# получить названия штатов и сопоставить их списку начальных букв
my $sth = $dbh->prepare ("SELECT name FROM states ORDER BY name");
$sth->execute ();
my %ltr = ();
while (my ($name) = $sth->fetchrow_array ())
{
my $ltr = uc (substr ($name, 0, 1)); # первая буква названия
# инициализировать список букв пустым массивом, если
# это первый штат, затем добавлять штаты в массив
$ltr{$ltr} = [] unless exists ($ltr{$ltr});
push (@{$ltr{$ltr}}, $name);
}
# теперь создать результирующий список
my @items = ();
foreach my $ltr (sort (keys (%ltr)))
{
# кодировать список названий штатов для данной буквы,
# создать маркированный список
my $ul_str = ul (li ([ map { escapeHTML ($_) } @{$ltr{$ltr}} ]));
push (@items, dt ($ltr), dd ($ul_str));
}
print dl (@items);

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

Извлечение изображений и других двоичных данных
Использование результатов запроса для загрузки файлов
Представление результатов запроса в виде гиперссылок
Представление результатов запроса в виде таблиц
Работа с баннерами