Recipe 18.3. Displaying Query Results as Tables

Problem

You want to display a query result as an HTML table.

Solution

Use each row of the result as a table row. If you want an initial row of column labels, supply your own or perhaps use the query metadata to obtain the names of the columns in the query result.

Discussion

HTML tables are useful for presenting highly structured output. One reason they're popular for displaying the results of queries is that they consist of rows and columns, so there's a natural conceptual correspondence between HTML tables and database tables or query results. In addition, you can obtain column headers for the table by accessing the query metadata. The basic structure of an HTML table is as follows:

The table begins and ends with <table> and </table> tags and encloses a set of rows.
Each row begins and ends with <tr> and </tr> tags and encloses a set of cells.
Tags for data cells are <td> and </td>. Tags for header cells are <th> and </th>. (Typically, browsers display header cells using boldface or other emphasis.)
Tags may include attributes. For example, to put a border around each cell, add a border="1" attribute to the <table> tag. To right-justify a table cell, add an align="right" attribute to the <td> 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, the resulting browser behavior is unpredictable.

Suppose that you want to display the contents of your CD collection:

mysql> SELECT year, artist, title FROM cd ORDER BY artist, year; +------+-----------------+------------------+ | year | artist          | title            | +------+-----------------+------------------+ | 1999 | Adrian Snell    | City of Peace    | | 1999 | Charlie Peacock | Kingdom Come     | | 2004 | Dave Bainbridge | Veil of Gossamer | | 1990 | Iona            | Iona             | | 2001 | Iona            | Open Sky         | | 1998 | jaci velasquez  | jaci velasquez   | | 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:

<table border="1">   <tr>     <th>Year</th>     <th>Artist</th>     <th>Title</th>   </tr>   <tr>     <td>1999</td>     <td>Adrian Snell</td>     <td>City of Peace</td>   </tr>   <tr>     <td>1999</td>     <td>Charlie Peacock</td>     <td>Kingdom Come</td>   </tr>   ... other rows here ...   <tr>     <td>1982</td>     <td>Undercover</td>     <td>Undercover</td>   </tr> </table>

To convert the results of a query to an HTML table, 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:

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

In Perl scripts, the CGI.pm functions table⁠(⁠ ⁠ ⁠), TR⁠(⁠ ⁠ ⁠), TD⁠(⁠ ⁠ ⁠), and th⁠(⁠ ⁠ ⁠) produce the table, row, data cell, and header cell elements. 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.^[*] Thus, to display the contents of the cd table as an HTML table, do this:

^[*] 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⁠(⁠ ⁠ ⁠):

$cgi->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 make sense of 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 <th> and <td> tag, and alternate the color value for each row. An easy way to do this is by using a variable that toggles 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";   # row-color variable 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 ()) {   # toggle the row-color variable   $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 statement, then executes the statement, and then returns its result as an HTML table. The function can get the column labels from the statement metadata automatically. To produce labels that differ from the table column names, specify column aliases in the statement:

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 statement that returns a result set can be passed to this function. You could, for example, use it to construct an HTML table from the result of a CHECK TABLE statement, which returns a result set that indicates the outcome of the check operation.

print p("Result of CHECK TABLE operation:"); my $tbl_str = make_table_from_query ($dbh, "CHECK TABLE 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, $stmt, @param) = @_;   my $sth = $dbh->prepare ($stmt);   $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 &nbsp; for empty cells     my @val = map {                 defined ($_) && $_ !~ /^\s*$/ ? escapeHTML ($_) : "&nbsp;"               } @{$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 enables you to pass values to be bound to placeholders in the statement. Just specify them after the statement 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   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 nonbreaking space ( ) into cells that would otherwise be empty, so that borders for them display 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.

Recipe 18.3. Displaying Query Results as Tables

Problem

Solution

Discussion

The &nbsp; Trick for Empty Table Cells

See Also

The Trick for Empty Table Cells