MySQL Cookbook, 2nd Edition

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