MySQL Cookbook

17.4 Displaying Query Results as Tables

17.4.1 Problem

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

17.4.2 Solution

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

17.4.3 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, 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:

<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>
  ... other rows here ...
  <tr>
    <td>1982</td>
    <td>Undercover</td>
    <td>Undercover</td>
  </tr>
</table>

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:

<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>

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 <th> and <td> 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   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.