ProblemYou want to display a query result as an HTML table. SolutionUse 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. DiscussionHTML 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:
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:
$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 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 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;
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. See AlsoSection 18.10 discusses how to generate tables using templates. 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 Section 19.11. |