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

    and
    tags and encloses a set of rows.
  • Each row begins and ends with and tags and encloses a set of cells.
  • Tags for data cells are and . Tags for header cells are and . (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

    tag. To right-justify a table cell, add an align="right" attribute to the
    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
    
     
    Year Artist Title
         

    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.

    17 5 Displaying Query Results as Hyperlinks

Using the mysql Client Program

Writing MySQL-Based Programs

Record Selection Techniques

Working with Strings

Working with Dates and Times

Sorting Query Results

Generating Summaries

Modifying Tables with ALTER TABLE

Obtaining and Using Metadata

Importing and Exporting Data

Generating and Using Sequences

Using Multiple Tables

Statistical Techniques

Handling Duplicates

Performing Transactions

Introduction to MySQL on the Web

Incorporating Query Resultsinto Web Pages

Processing Web Input with MySQL

Using MySQL-Based Web Session Management

Appendix A. Obtaining MySQL Software

Appendix B. JSP and Tomcat Primer

Appendix C. References



MySQL Cookbook
MySQL Cookbook
ISBN: 059652708X
EAN: 2147483647
Year: 2005
Pages: 412
Authors: Paul DuBois

Similar book on Amazon

Flylib.com © 2008-2017.
If you may any questions please contact us: flylib@qtcs.net