| tag.
Note that you should always supply the closing tag for each table element. This is a good idea in general for any HTML element, but especially so for tables. If you omit closing tags, some browsers will supply them automatically, but others may lock up or crash.
Suppose you want to display the contents of your CD collection:
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 |
+------+-----------------+-----------------------+
To display this query result as a bordered HTML table, you need to produce output that looks something like this:
... other rows here ...
| Year |
Artist |
Title |
| 1992 |
Charlie Peacock |
Lie Down in the Grass |
| 1996 |
Charlie Peacock |
strangelanguage |
| 1982 |
Undercover |
Undercover |
Converting the results of a query to an HTML table requires that you wrap each value from a given result set row in cell tags, each row in row tags, and the entire set of rows in table tags. A JSP page might produce an HTML table from the cd table query like this:
SELECT year, artist, title FROM cd ORDER BY artist, year
In Perl scripts, the table, row, data cell, and header cell elements are produced by the CGI.pm functions table( ), tr( ), td( ), and th( ). However, the tr( ) function that generates a table row should be invoked as Tr( ) to avoid a conflict with the built-in Perl tr function that transliterates characters.[1] Thus, to display the contents of the cd table as an HTML table, do this:
[1] If you use the CGI.pm object-oriented interface, there is no ambiguity. In that case, you invoke the tr( ) method through a CGI object and it is unnecessary to invoke it as Tr( ):
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);
Sometimes a table can be easier to understand if you display the rows in alternating colors, particularly if the table cells don't include borders. To do this, add a bgcolor attribute to each
|
and |
tag, and alternate the color value for each row. An easy way to do this is by using a variable that flip-flops between two values. In the following example, the $bgcolor variable alternates between the values silver and 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);
The preceding table-generation examples hardwire the column headings into the code, as well as knowledge about the number of columns. With a little effort, you can write a more general function that takes a database handle and an arbitrary query, then executes the query and returns the result as an HTML table. The function can automatically get the column labels from the query metadata; to get labels that differ from the table column names, specify column aliases in the query:
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;
Any kind of query that returns a result set can be passed to this function. You could, for example, use it to construct an HTML table that shows all the column metadata for a database table:
my $tbl_str = make_table_from_query ($dbh, "SHOW COLUMNS FROM profile");
print $tbl_str;
What does the make_table_from_query( ) function look like? Here's an implementation in Perl:
sub make_table_from_query
{
# db handle, query string, parameters to be bound to placeholders (if any)
my ($dbh, $query, @param) = @_;
my $sth = $dbh->prepare ($query);
$sth->execute (@param);
my @rows = ( );
# use column names for cells in the header row
push (@rows, Tr (th ([ map { escapeHTML ($_) } @{$sth->{NAME}} ])));
# fetch each data row
while (my $row_ref = $sth->fetchrow_arrayref ( ))
{
# encode cell values, avoiding warnings for undefined
# values and using for empty cells
my @val = map {
defined ($_) && $_ !~ /^s*$/ ? escapeHTML ($_) : " "
} @{$row_ref};
my $row_str;
for (my $i = 0; $i < @val; $i++)
{
# right-justify numeric columns
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( ) does some extra work to right-justify numeric columns so that the values line up better. It also allows you to pass values to be bound to placeholders in the query. Just specify them after the query string:
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;
The nbsp; Trick for Empty Table Cells
A display problem sometimes occurs for HTML tables that include borders around cells: when a table cell is empty or contains only whitespace, many browsers do not show a border around the cell. This makes the table look irregular. To avoid this problem, the make_table_from_query( ) function puts a non-breaking space ( ) into cells that would otherwise be empty. This causes borders for those cells to be displayed properly.
|
One thing to watch out for with program-generated tables is that browsers cannot render a table in a window until they've seen the entire thing. If you have a very large result set, it may take a very long time to display. Strategies for dealing with this problem include partitioning your data across multiple tables within a single page (so that the browser can display each table as it receives it), or across multiple pages. If you use multiple tables on a page, you should probably include some width attribute information in your header and data cell tags. Otherwise, each table will be sized to the actual widths of the values in its columns. If these differ across tables, your page will have a vertically ragged appearance.
17.4.4 See Also
To display a table in such a way that the user can click on any column heading to sort the table's contents by that column, see Recipe 18.12.
|