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

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

Задача
Вы хотите представить результат запроса в виде HTML-таблицы.

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

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

• Таблица начинается и заканчивается тегами <table> и </table> и содержит набор строк.

• Каждая строка начинается и заканчивается тегами <tr> и </tr> и содержит набор ячеек.

• Ячейки ограничены тегами <td> и </td>. Для ячеек заголовка используются теги <th> и </th>. (Обычно броузеры выделяют ячейки заголовка жирным шрифтом или иным способом.)

• Теги могут иметь атрибуты. Например, для отображения границ ячеек используется атрибут border="1" тега <table>. Для выравнивания содержимого ячеек вправо – атрибут align="right" тега <td>.

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

Предположим, вы хотите показать содержимое вашей коллекции компактдисков:

mysql> SELECT year, artist, title FROM cd ORDER BY artist, year;

+-----+-------------------+-------------------------+
| year | artist                  | title                            |
+-----+-------------------+-------------------------+
| 1992 | Charlie Peacock | Lie Down in the Grass |
| 1996 | Charlie Peacock | strangelanguage         |
| 1990 | Iona                  | Iona                           |
| 1993 | Iona                  | Beyond These Shores |
| 1990 | Michael Gettel     | Return                       |
| 1989 | Richard Souther | Cross Currents           |
| 1987 | The 77s              | The 77s                      |
| 1982 | Undercover       | Undercover                |
+-----+-------------------+--------------------------+

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

<table border="1">
<tr>
<th>Year</th>
<th>Artist</th>
<th>Title</th>
</tr>
<tr>
<td>1992</td>
<td>Charlie Peacock</td>
<td>Lie Down in the Grass</td>
</tr>
<tr>
<td>1996</td>
<td>Charlie Peacock</td>
<td>strangelanguage</td>
</tr>
... еще строки ...
<tr>
<td>1982</td>
<td>Undercover</td>
<td>Undercover</td>
</tr>
</table>

В процессе преобразования результатов запроса в таблицу HTML каждое значение результирующего множества помещается в теги ячейки, каждая строка – в теги строки, а все множество – в теги таблицы. Страница JSP, формирующая таблицу HTML из результатов запроса к таблице cd, может выглядеть так:

<table border="1">
<tr>
<th>Year</th>
<th>Artist</th>
<th>Title</th>
</tr>
<sql:query var="rs" dataSource="${conn}">
SELECT year, artist, title FROM cd ORDER BY artist, year
</sql:query>
<c:forEach var="row" items="${rs.rows}">
<tr>
<td><c:out value="${row.year}" /></td>
<td><c:out value="${row.artist}" /></td>
<td><c:out value="${row.title}" /></td></tr>
</c:forEach>
</table>

В сценариях на Perl таблица, строка, ячейка данных и ячейка заголовка формируются функциями модуля CGI.pm table(), tr(), td() и th(). Но функцию tr(), генерирующую строку таблицы, следует вызывать как Tr(), чтобы избежать конфликта со встроенной функцией транслитерации tr. Отображение содержимого таблицы cd в виде HTML-таблицы выполняется так:

my $sth = $dbh->prepare (
"SELECT year, artist, title FROM cd ORDER BY artist, year"
);
$sth->execute ();
my @rows = ();
push (@rows, Tr (th ("Year"), th ("Artist"), th ("Title")));
while (my ($year, $artist, $title) = $sth->fetchrow_array ())
{
push (@rows, Tr (
td (escapeHTML ($year)),
td (escapeHTML ($artist)),
td (escapeHTML ($title))
));
}
print table ({-border => "1"}, @rows);

Иногда лучше воспринимается таблица со строками разных цветов, особенно если у ячеек нет рамки. Чтобы достичь такого эффекта, добавьте атрибут bgcolor в каждый из тегов <th> и <td> и укажите для каждой строки свой цвет. Это легко сделать с помощью переменной, переключающейся между двумя значениями. В следующем примере переменная $bgcolor попеременно принимает значения silver и white:

my $sth = $dbh->prepare (
"SELECT year, artist, title FROM cd ORDER BY artist, year"
);
$sth->execute ();
my $bgcolor = "silver";
my @rows = ();
push (@rows, Tr (
th ({-bgcolor => $bgcolor}, "Year"),
th ({-bgcolor => $bgcolor}, "Artist"),
th ({-bgcolor => $bgcolor}, "Title")
));
while (my ($year, $artist, $title) = $sth->fetchrow_array ())
{
$bgcolor = ($bgcolor eq "silver" ? "white" : "silver");push (@rows, Tr (
td ({-bgcolor => $bgcolor}, escapeHTML ($year)),
td ({-bgcolor => $bgcolor}, escapeHTML ($artist)),
td ({-bgcolor => $bgcolor}, escapeHTML ($title))
));
}
print table ({-border => "1"}, @rows);

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

my $tbl_str = make_table_from_query (
$dbh,
"SELECT
year AS Year, artist AS Artist, title AS Title
FROM cd
ORDER BY artist, year"
);
print $tbl_str;

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

my $tbl_str = make_table_from_query ($dbh, "SHOW COLUMNS FROM profile");
print $tbl_str;

Как же выглядит функция make_table_from_query()? Приведем ее реализацию на Perl:

sub make_table_from_query
{
# дескриптор БД, строка запроса, параметры для замены заполнителей (если есть)
my ($dbh, $query, @param) = @_;
my $sth = $dbh->prepare ($query);
$sth->execute (@param);
my @rows = ();
# поместить имена столбцов в ячейки заголовка
push (@rows, Tr (th ([ map { escapeHTML ($_) } @{$sth->{NAME}} ])));
# выбрать все строки данных
while (my $row_ref = $sth->fetchrow_arrayref ())
{
# кодировать значения ячеек, избегая предупреждений для
# неопределенных значений и вставляя   в пустые ячейки
my @val = map {
defined ($_) && $_ !~ /^\s*$/ ? escapeHTML ($_) : " "
} @{$row_ref};my $row_str;
for (my $i = 0; $i < @val; $i++)
{
# числовые столбцы выровнять вправо
if ($sth->{mysql_is_num}->[$i])
{
$row_str .= td ({-align => "right"}, $val[$i]);
}
else
{
$row_str .= td ($val[$i]);
}
}
push (@rows, Tr ($row_str));
}
return (table ({-border => "1"}, @rows));
}

Функция make_table_from_query() выполняет некоторые дополнительные действия для выравнивания вправо числовых столбцов (так они лучше смотрятся). Она также позволяет передать значения для связывания с заполнителями в запросе. Укажите их сразу после строки запроса:

my $tbl_str = make_table_from_query (
$dbh,
"SELECT
year AS Year, artist AS Artist, title AS Title
FROM cd
WHERE year < ?
ORDER BY artist, year",
1990
);
print $tbl_str;

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

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

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