Recipe 18.2. Displaying Query Results as Lists

Problem

A query result contains a set of items that should be displayed as a structured list.

Solution

There are several types of HTML lists. Write the list items within tags that are appropriate for the type of list you want to produce.

Discussion

More structured than paragraphs and less structured than tables, lists provide a useful way to display a set of individual items. HTML provides several styles of lists, such as ordered lists, unordered lists, and definition lists. You may also want to nest lists, which requires list-within-list formatting.

Lists generally consist of opening and closing tags that enclose a set of items, each of which is delimited by its own tags. List items correspond naturally to rows returned from a query, so generating an HTML list structure from within a program is a matter of encoding your query result, enclosing each row within the proper item tags, and adding the opening and closing list tags.

Two approaches to list generation are common. To print the tags as you process the result set, do this:

Print the list opening tag.
Fetch and print each result set row as a list item, including the item tags.
Print the list closing tag.

Alternatively, you can process the list in memory:

Store the list items in an array.
Pass the array to a list generation function that adds the appropriate tags.
Print the result.

The examples that follow demonstrate both approaches.

Ordered lists

An ordered list consists of items that have a particular sequence. Browsers typically display ordered lists as a set of numbered items:

1. First item 2. Second item 3. Third item

You need not specify the item numbers, because browsers add them automatically. The HTML for an ordered list is enclosed within <ol> and </ol> tags, and contains items that are enclosed within <li> and </li> tags:

<ol>   <li>First item</li>   <li>Second item</li>   <li>Third item</li> </ol>

Suppose that you have an ingredient table that contains numbered ingredients for a cooking recipe:

+----+---------------------------------+ | id | item                            | +----+---------------------------------+ |  1 | 3 cups flour                    | |  2 | 1/2 cup raw ("unrefined") sugar | |  3 | 3 eggs                          | |  4 | pinch (< 1/16 teaspoon) salt    | +----+---------------------------------+

The table contains an id column, but you need only fetch the text values in the proper order to display them as an ordered list, because a browser adds item numbers itself. The items contain the special characters " and <, so you should HTML-encode them before adding the tags that convert the items to an HTML list. The result looks like this:

<ol>   <li>3 cups flour</li>   <li>1/2 cup raw (&quot;unrefined&quot;) sugar</li>   <li>3 eggs</li>   <li>pinch (&lt; 1/16 teaspoon) salt</li> </ol>

One way to create such list from a script is by printing the HTML as you fetch the rows of the result set. Here's how you might do so in a JSP page using the JSTL tags:

<sql:query dataSource="${conn}" var="rs">   SELECT item FROM ingredient ORDER BY id </sql:query> <ol> <c:forEach items="${rs.rows}" var="row">   <li><c:out value="${row.item}"/></li> </c:forEach> </ol>

In PHP, the same operation can be performed like this:

$stmt = "SELECT item FROM ingredient ORDER BY id"; $result =& $conn->query ($stmt); if (PEAR::isError ($result))   die (htmlspecialchars ($result->getMessage ())); print ("<ol>\n"); while (list ($item) = $result->fetchRow ())   print ("<li>" . htmlspecialchars ($item) . "</li>\n"); $result->free (); print ("</ol>\n");

It's not necessary to add newlines after the closing tags as this example does; web browsers don't care if they're present or not. I like to add them because the HTML produced by a script is easier to examine directly if it's not all on a single line, which simplifies debugging.

The preceding examples use an approach to HTML generation that interleaves row fetching and output generation. It's also possible to separate (or decouple) the two operations: retrieve the data first, and then write the output. Queries tend to vary from list to list, but generating the list itself often is fairly stereotypical. If you put the list-generation code into a utility function, you can reuse it for different queries. The two issues that the function must handle are HTML-encoding the items (if they aren't already encoded) and adding the proper HTML tags. For example, a function named make_ordered_list⁠(⁠ ⁠ ⁠) can be written as follows in PHP. It takes the list items as an array argument and returns the list as a string:

function make_ordered_list ($items, $encode = TRUE) {   $str = "<ol>\n";   foreach ($items as $k => $v)   {     if ($encode)       $v = htmlspecialchars ($v);     $str .= "<li>$v</li>\n";   }   $str .= "</ol>\n";   return ($str); }

After writing the utility function, you can fetch the data first and then print the HTML like so:

# fetch items for list $stmt = "SELECT item FROM ingredient ORDER BY id"; $result =& $conn->query ($stmt); if (PEAR::isError ($result))   die (htmlspecialchars ($result->getMessage ())); $items = array (); while (list ($item) = $result->fetchRow ())   $items[] = $item; $result->free (); # generate HTML list print (make_ordered_list ($items));

In Python, the utility function can be defined like this:

def make_ordered_list (items, encode = True):   list = "<ol>\n"   for item in items:     if item is None:  # handle possibility of NULL item       item = ""     # make sure item is a string, and then encode if necessary     item = str (item)     if encode:       item = cgi.escape (item, 1)     list = list + "<li>" + item + "</li>\n"   list = list + "</ol>\n"   return list

And used like this:

# fetch items for list stmt = "SELECT item FROM ingredient ORDER BY id" cursor = conn.cursor () cursor.execute (stmt) items = [] for (item,) in cursor.fetchall ():   items.append (item) cursor.close () # generate HTML list print make_ordered_list (items)

The second argument to make_ordered_list⁠(⁠ ⁠ ⁠) indicates whether it should perform HTML-encoding of the list items. The easiest thing is to let the function handle this for you (which is why the default is true). However, if you're creating a list from items that themselves include HTML tags, you wouldn't want the function to encode the special characters in those tags. For example, if you're creating a list of hyperlinks, each list item will contain <a> tags. To prevent these from being converted to <a>, pass make_ordered_list⁠(⁠ ⁠ ⁠) a second argument that evaluates to false.

If your API provides functions to generate HTML structures, you need not write them yourself, of course. That's the case for the Perl CGI.pm and Ruby cgi modules. For example, in Perl, generate each item by invoking its li⁠(⁠ ⁠ ⁠) function to add the opening and closing item tags, save up the items in an array, and pass the array to ol⁠(⁠ ⁠ ⁠) to add the opening and closing list tags:

my $stmt = "SELECT item FROM ingredient ORDER BY id"; my $sth = $dbh->prepare ($stmt); $sth->execute (); my @items = (); while (my $ref = $sth->fetchrow_arrayref ()) {   # handle possibility of NULL (undef) item   my $item = (defined ($ref->[0]) ? escapeHTML ($ref->[0]) : "");   push (@items, li ($item)); } print ol (@items);

The reason for converting NULL values (represented by undef) to the empty string is to avoid having Perl generate uninitialized-value warnings when run with warnings enabled. (The ingredient table doesn't actually contain any NULL values, but the technique is useful for dealing with tables that might.)

The previous example intertwines row fetching and HTML generation. To use a more decoupled approach that separates fetching the items from printing the HTML, first retrieve the items into an array. Then pass the array by reference to li⁠(⁠ ⁠ ⁠) and the result to ol⁠(⁠ ⁠ ⁠):

# fetch items for list my $stmt = "SELECT item FROM ingredient ORDER BY id"; my $item_ref = $dbh->selectcol_arrayref ($stmt); # generate HTML list, handling possibility of NULL (undef) items $item_ref = [ map { defined ($_) ? escapeHTML ($_) : "" } @{$item_ref} ]; print ol (li ($item_ref));

Note two things about the li⁠(⁠ ⁠ ⁠) function:

It doesn't perform any HTML-encoding; you must do that yourself.
It can handle a single value or an array of values. However, if you pass an array, you should pass it by reference. When you do that, li⁠(⁠ ⁠ ⁠), adds <li> and </li> tags to each array element, and then concatenates them and returns the resulting string. If you pass the array itself rather than a reference, li⁠(⁠ ⁠ ⁠) concatenates the items first, and then adds a single set of tags around the result, which is usually not what you want. This behavior is shared by several other CGI.pm functions that can operate on single or multiple values. For example, the table data td⁠(⁠ ⁠ ⁠) function adds a single set of <td> and </td> tags if you pass it a scalar or list. If you pass a list reference, it adds the tags to each item in the list.

Should You Intertwine or Decouple Row Fetching and HTML Generation?

If you want to write a script in a hurry, you can probably get it running most quickly by writing code that prints HTML from query rows as you fetch them. There are, however, certain advantages to separating data retrieval from output production. The most obvious ones are that by using a utility function to generate the HTML, you have to write the function only once, and you can share it among scripts. But there are other benefits as well:

Functions that generate HTML structures can be used with data obtained from other sources, not just from a database.
The decoupled approach takes advantage of the fact that you need not generate output directly. You can construct a page element in memory, and then print it when you're ready. This is particularly useful for building pages that consist of several components, because it gives you more latitude to create the components in the order that's most convenient. (On the other hand, if you're retrieving very large result sets, this approach can entail considerable memory use.)
Decoupling row fetching and output generation gives you more flexibility in the types of output you produce. If you decide to generate an unordered list rather than an ordered list, just call a different output function; the data collection phase need not change. This is true even if you decide to use a different output format (XML or WML rather than HTML, for example). In this case, you still need only a different output function; data collection remains unchanged.
By prefetching the list items, you can make adaptive decisions about what type of list to create. Although we are not yet to the point of discussing web forms, they make heavy use of their own kinds of lists. In that context, having items in hand before generating an HTML structure from them can be useful if you want to choose the list type based on the size of the list. For example, you can display a set of radio buttons if the number of items is small, or a pop-up menu or scrolling list if the number is large.

The Ruby equivalent of the previous example looks like this:

# fetch items for list stmt = "SELECT item FROM ingredient ORDER BY id" items = dbh.select_all(stmt) list = "" items.each do |item|   list << cgi.li { CGI.escapeHTML(item.to_s) } end list = cgi.ol { list }

Unordered lists

An unordered list is like an ordered list except that browsers display all the items with the same marker character, such as a bullet:

• First item • Second item • Third item

"Unordered" refers to the fact that the marker character provides no sequence information. You can of course display the items in any order you choose. The HTML tags for an unordered list are the same as for an ordered list except that the opening and closing tags are <ul> and </ul> rather than <ol> and </ol>:

<ul>   <li>First item</li>   <li>Second item</li>   <li>Third item</li> </ul>

For APIs where you print the tags directly, use the same procedure as for ordered lists, but print <ul> and </ul> instead of <ol> and </ol>. Here is an example in JSP:

<sql:query dataSource="${conn}" var="rs">   SELECT item FROM ingredient ORDER BY id </sql:query> <ul> <c:forEach items="${rs.rows}" var="row">   <li><c:out value="${row.item}"/></li> </c:forEach> </ul>

In Perl, create an unordered list by using the CGI.pm ul⁠(⁠ ⁠ ⁠) function rather than ol⁠(⁠ ⁠ ⁠):

# fetch items for list my $stmt = "SELECT item FROM ingredient ORDER BY id"; my $item_ref = $dbh->selectcol_arrayref ($stmt); # generate HTML list, handling possibility of NULL (undef) items $item_ref = [ map { defined ($_) ? escapeHTML ($_) : "" } @{$item_ref} ]; print ul (li ($item_ref));

If you're writing your own utility function for unordered lists, it's easily derived from a function that generates ordered lists. For example, it's simple to adapt make_ordered_list⁠(⁠ ⁠ ⁠) to create make_unordered_list⁠(⁠ ⁠ ⁠), because they differ only in the opening and closing list tags used.

Definition lists

A definition list consists of two-part items, each including a term and a definition. "Term" and "definition" have loose meanings, because you can display any kind of information you want. For example, the following doremi table associates the name of each note in a musical scale with a mnemonic phrase for remembering it, but the mnemonics aren't exactly what you'd call definitions:

+----+------+----------------------------+ | id | note | mnemonic                   | +----+------+----------------------------+ |  1 | do   | A deer, a female deer      | |  2 | re   | A drop of golden sun       | |  3 | mi   | A name I call myself       | |  4 | fa   | A long, long way to run    | |  5 | so   | A needle pulling thread    | |  6 | la   | A note to follow so        | |  7 | ti   | A drink with jam and bread | +----+------+----------------------------+

Nevertheless, the note and mnemonic columns can be displayed as a definition list:

do     A deer, a female deer re     A drop of golden sun mi     A name I call myself fa     A long, long way to run so     A needle pulling thread la     A note to follow so ti     I drink with jam and bread

The HTML for a definition list begins and ends with <dl> and </dl> tags. Each item has a term enclosed within <dt> and </dt> tags and a definition enclosed within <dd> and </dd> tags:

<dl>   <dt>do</dt> <dd>A deer, a female deer</dd>   <dt>re</dt> <dd>A drop of golden sun</dd>   <dt>mi</dt> <dd>A name I call myself</dd>   <dt>fa</dt> <dd>A long, long way to run</dd>   <dt>so</dt> <dd>A needle pulling thread</dd>   <dt>la</dt> <dd>A note to follow so</dd>   <dt>ti</dt> <dd>A drink with jam and bread</dd> </dl>

In a JSP page, you can generate the definition list like this:

<sql:query dataSource="${conn}" var="rs">   SELECT note, mnemonic FROM doremi ORDER BY note </sql:query> <dl> <c:forEach items="${rs.rows}" var="row">   <dt><c:out value="${row.note}"/></dt>   <dd><c:out value="${row.mnemonic}"/></dd> </c:forEach> </dl>

In PHP, create the list like this:

$stmt = "SELECT note, mnemonic FROM doremi ORDER BY id"; $result =& $conn->query ($stmt); if (PEAR::isError ($result))   die (htmlspecialchars ($result->getMessage ())); print ("<dl>\n"); while (list ($note, $mnemonic) = $result->fetchRow ()) {   print ("<dt>" . htmlspecialchars ($note) . "</dt>\n");   print ("<dd>" . htmlspecialchars ($mnemonic) . "</dd>\n"); } $result->free (); print ("</dl>\n");

Or fetch the data and then pass it to a utility function that takes arrays of terms and definitions and returns the list as a string:

# fetch items for list $stmt = "SELECT note, mnemonic FROM doremi ORDER BY id"; $result =& $conn->query ($stmt); if (PEAR::isError ($result))   die (htmlspecialchars ($result->getMessage ())); $terms = array (); $defs = array (); while (list ($note, $mnemonic) = $result->fetchRow ()) {   $terms[] = $note;   $defs[] = $mnemonic; } $result->free (); # generate HTML list print (make_definition_list ($terms, $defs));

The make_definition_list⁠(⁠ ⁠ ⁠) function can be written like this:

function make_definition_list ($terms, $definitions, $encode = TRUE) {   $str = "<dl>\n";   $n = count ($terms);   for ($i = 0; $i < $n; $i++)   {     $term = $terms[$i];     $definition = $definitions[$i];     if ($encode)     {       $term = htmlspecialchars ($term);       $definition = htmlspecialchars ($definition);     }     $str .= "<dt>$term</dt>\n<dd>$definition</dd>\n";   }   $str .= "</dl>\n";   return ($str); }

In Ruby, use the dt and dd methods to create the list item content, and then pass it to the dl method to add the outermost list tags:

stmt = "SELECT note, mnemonic FROM doremi ORDER BY id" list = "" dbh.execute(stmt) do |sth|   sth.fetch do |row|     list << cgi.dt { CGI.escapeHTML(row["note"].to_s) }     list << cgi.dd { CGI.escapeHTML(row["mnemonic"].to_s) }   end end list = cgi.dl { list }

Here is another example (in Perl). Each term is a database name, and the corresponding definition indicates how many tables are in the database. The numbers are obtained from INFORMATION_SCHEMA using a query that counts the number of tables in each database. You can create the terms and definitions by invoking dt⁠(⁠ ⁠ ⁠) and dd⁠(⁠ ⁠ ⁠), save them in an array, and pass the array to dl⁠(⁠ ⁠ ⁠):

# count number of tables per database my $sth = $dbh->prepare ("SELECT TABLE_SCHEMA, COUNT(TABLE_NAME)                           FROM INFORMATION_SCHEMA.TABLES                           GROUP BY TABLE_SCHEMA"); $sth->execute (); my @items = (); while (my ($db_name, $tbl_count) = $sth->fetchrow_array ()) {   push (@items, dt (escapeHTML ($db_name)));   push (@items, dd (escapeHTML ($tbl_count . " tables"))); } print dl (@items);

The counts indicate the number of tables accessible to the MySQL account that the script uses when it connects to the MySQL server. If there are databases or tables that are not accessible, you cannot get information about them and they are not included in the counts.

Unmarked lists

A type of list not normally discussed as such is a list with no markings at all. This is simply a set of items, each on a separate line. An unmarked list is very easy to produce: fetch each item and add a break tag after it. Here's an example in JSP:

<c:forEach items="${rs.rows}" var="row">   <c:out value="${row.item}"/><br /> </c:forEach>

If you already have the items in an array, just iterate through it. For example, in Ruby, if you have a set of items in an array named items, generate the list like this:

list = items.collect { |item| CGI.escapeHTML(item.to_s) + cgi.br }.join

Nested lists

Some applications display information that is most easily understood when presented as a list of lists. The following example displays state names as a definition list, grouped by the initial letter of the names. For each item in the list, the term is the initial letter, and the definition is an unordered list of the state names beginning with that letter:

A   • Alabama   • Alaska   • Arizona   • Arkansas C   • California   • Colorado   • Connecticut D   • Delaware ...

One way to produce such a list (in Perl) is as follows:

# get list of initial letters my $ltr_ref = $dbh->selectcol_arrayref (                   "SELECT DISTINCT UPPER(LEFT(name,1)) AS letter                    FROM states ORDER BY letter"); my @items = (); # get list of states for each letter foreach my $ltr (@{$ltr_ref}) {   my $item_ref = $dbh->selectcol_arrayref (                     "SELECT name FROM states WHERE LEFT(name,1) = ?                      ORDER BY name", undef, $ltr);   $item_ref = [ map { escapeHTML ($_) } @{$item_ref} ];   # convert list of states to unordered list   my $item_list = ul (li ($item_ref));   # for each definition list item, the initial letter is   # the term, and the list of states is the definition   push (@items, dt ($ltr));   push (@items, dd ($item_list)); } print dl (@items);

The preceding example uses one query to get the list of distinct letters, and another for each letter to find the states associated with each letter. You could also retrieve all the information using a single query, and then march through the result set and begin a new list item each time you reach a new letter:

my $sth = $dbh->prepare ("SELECT name FROM states ORDER BY name"); $sth->execute (); my @items = (); my @names = (); my $cur_ltr = ""; while (my ($name) = $sth->fetchrow_array ()) {   my $ltr = uc (substr ($name, 0, 1));  # initial letter of name   if ($cur_ltr ne $ltr)         # beginning a new letter?   {     if (@names)     # any stored-up names from previous letter?     {       # for each definition list item, the initial letter is       # the term, and the list of states is the definition       push (@items, dt ($cur_ltr));       push (@items, dd (ul (li (\@names))));     }     @names = ();     $cur_ltr = $ltr;   }   push (@names, escapeHTML ($name)); } if (@names)           # any remaining names from final letter? {   push (@items, dt ($cur_ltr));   push (@items, dd (ul (li (\@names)))); } print dl (@items);

A third approach uses a single query but separates the data-collection and HTML-generation phases:

# collect state names and associate each with the proper # initial-letter list my $sth = $dbh->prepare ("SELECT name FROM states ORDER BY name"); $sth->execute (); my %ltr = (); while (my ($name) = $sth->fetchrow_array ()) {   my $ltr = uc (substr ($name, 0, 1));  # initial letter of name   # initialize letter list to empty array if this is   # first state for it, and then add state to array   $ltr{$ltr} = [] unless exists ($ltr{$ltr});   push (@{$ltr{$ltr}}, $name); } # now generate the output lists my @items = (); foreach my $ltr (sort (keys (%ltr))) {   # encode list of state names for this letter, generate unordered list   my $ul_str = ul (li ([ map { escapeHTML ($_) } @{$ltr{$ltr}} ]));   push (@items, dt ($ltr), dd ($ul_str)); } print dl (@items);