MySQL Cookbook

17.3 Displaying Query Results as Lists

17.3.1 Problem

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

17.3.2 Solution

Write the list items within the proper HTML tags for the desired type of list.

17.3.3 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 wish to nest lists, which requires list-within-list formatting.

Lists generally consist of an opening and closing tag that surround 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, surrounding each row with the proper item tags, and adding the opening and closing list tags. Two approaches to list generation are common. If you want to print the tags as you process the result set, do this:

Alternatively, you can process the list in memory:

The examples that follow demonstrate both approaches.

17.3.4 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 the browser will add them automatically. The HTML for an ordered list begins and ends with <ol> and </ol> opening and closing tags, and contains items surrounded by <li> and </li> tags:

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

Suppose 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 will add 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 will look 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 var="rs" dataSource="${conn}">
    SELECT item FROM ingredient ORDER BY id
</sql:query>
<ol>
<c:forEach var="row" items="${rs.rows}">
    <li><c:out value="${row.item}" /></li>
</c:forEach>
</ol>

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

$query = "SELECT item FROM ingredient ORDER BY id";
$result_id = mysql_query ($query, $conn_id);
if (!$result_id)
    die (htmlspecialchars (mysql_error ($conn_id)));
print ("<ol>\n");
while (list ($item) = mysql_fetch_row ($result_id))
    print ("<li>" . htmlspecialchars ($item) . "</li>\n");
mysql_free_result ($result_id);
print ("</ol>\n");

It's not necessary to add the 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 record fetching and output generation. It's also possible to separate or decouple the two operations: retrieve the data first, 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 the function must handle are HTML-encoding of the items (if they aren't already encoded) and adding the proper HTML tags. For example, in PHP, a function make_ordered_list( ) can be written as follows. It takes the list items as an array argument and returns the list as a string:

function make_ordered_list ($items, $encode = TRUE)
{
    if (!is_array ($items))
        return ("make_ordered_list: items argument must be an array");
    $str = "<ol>\n";
    reset ($items);
    while (list ($k, $v) = each ($items))
    {
        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 and print HTML like so:

# fetch items for list
$query = "SELECT item FROM ingredient ORDER BY id";
$result_id = mysql_query ($query, $conn_id);
if (!$result_id)
    die (htmlspecialchars (mysql_error ($conn_id)));
$items = array ( );
while (list ($item) = mysql_fetch_row ($result_id))
    $items[ ] = $item;
mysql_free_result ($result_id);

# generate HTML list
print (make_ordered_list ($items));

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

def make_ordered_list (items, encode = 1):
    if type (items) not in (types.ListType, types.TupleType):
        return ("make_ordered_list: items argument must be a list")
    list = "<ol>\n"
    for item in items:
        if item is None:    # handle possibility of NULL item
            item = ""
        # make sure item is a string, then encode if necessary
        if type (item) is not types.StringType:
            item = `item`
        if encode:
            item = cgi.escape (item, 1)
        list = list + "<li>" + item + "</li>\n"
    list = list + "</ol>\n"
    return list

And used as follows:

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

# generate HTML list
print make_ordered_list (items)

The PHP and Python versions of make_ordered_list( ) check their first argument to make sure it's an array. If it's not, they return an error string indicating the problem. Returning a descriptive string makes problems immediately obvious in the web page when you look at the output produced by the function. You could return some other kind of error indicator if you like, or perhaps raise an exception or terminate the script.

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 &lt;a&gt;, pass make_ordered_list( ) a second argument of FALSE (for PHP) or 0 (for Python).

If your API provides functions to generate HTML structures, you need not write them yourself, of course. That's the case for Perl CGI.pm module: 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 $query = "SELECT item FROM ingredient ORDER BY id";
my $sth = $dbh->prepare ($query);
$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 undef (NULL) values to the empty string is to avoid having Perl generate uninitialized-value warnings when run with the -w option. (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 record 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 $query = "SELECT item FROM ingredient ORDER BY id";
my $item_ref = $dbh->selectcol_arrayref ($query);

# 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, then concatenates them and returns the resulting string. If you pass the array itself rather than a reference, li( ) concatenates the items first, 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 add the tags to each item in the list.

Should You Intertwine or Decouple Record Fetchingand 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. (And if your API provides the function, you don't have to write it even once; so much the better.) 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, 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. Decoupling record 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.

17.3.5 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 for an unordered list is 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 var="rs" dataSource="${conn}">
    SELECT item FROM ingredient ORDER BY id
</sql:query>
<ul>
<c:forEach var="row" items="${rs.rows}">
    <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 $query = "SELECT item FROM ingredient ORDER BY id";
my $item_ref = $dbh->selectcol_arrayref ($query);

# 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 the PHP and Python versions of make_ordered_list( ) to create make_unordered_list( ) functions, because they differ only in the opening and closing list tags used.

17.3.6 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   | I 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 delimited by <dt> and </dt>, and a definition delimited by <dd> and </dd>:

<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>I drink with jam and bread</dd>
</dl>

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

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

In PHP, create the list like this:

$query = "SELECT item FROM ingredient ORDER BY id";
$result_id = mysql_query ($query, $conn_id);
if (!$result_id)
    die (htmlspecialchars (mysql_error ($conn_id)));
print ("<dl>\n");
while (list ($note, $mnemonic) = mysql_fetch_row ($result_id))
{
    print ("<dt>" . htmlspecialchars ($note) . "</dt>\n");
    print ("<dd>" . htmlspecialchars ($mnemonic) . "</dt>\n");
}
mysql_free_result ($result_id);
print ("</dl>\n");

Or write a utility function that takes arrays of terms and definitions and returns the list as a string:

function make_definition_list ($terms, $definitions, $encode = TRUE)
{
    if (!is_array ($terms))
        return ("make_definition_list: terms argument must be an array");
    if (!is_array ($definitions))
        return ("make_definition_list: definitions argument must be an array");
    if (count ($terms) != count ($definitions))
        return ("make_definition_list: term and definition list size mismatch");
    $str = "<dl>\n";
    reset ($terms);
    reset ($definitions);
    while (list ($dtk, $dtv) = each ($terms))
    {
        list ($ddk, $ddv) = each ($definitions);
        if ($encode)
        {
            $dtv = htmlspecialchars ($dtv);
            $ddv = htmlspecialchars ($ddv);
        }
        $str .= "<dt>$dtv</dt>\n<dd>$ddv</dd>\n";
    }
    $str .= "</dl>\n";
    return ($str);
}

Use the make_definition_list( ) function like this:

# fetch items for list
$query = "SELECT note, mnemonic FROM doremi ORDER BY id";
$result_id = mysql_query ($query, $conn_id);
if (!$result_id)
    die (htmlspecialchars (mysql_error ($conn_id)));
$terms = array ( );
$defs = array ( );
while (list ($note, $mnemonic) = mysql_fetch_row ($result_id))
{
    $terms[ ] = $note;
    $defs[ ] = $mnemonic;
}
mysql_free_result ($result_id);

# generate HTML list
print (make_definition_list ($terms, $defs));

In Perl, create the terms and definitions by invoking dt( ) and dd( ), save them in an array, and pass the array to dl( ):

my $query = "SELECT note, mnemonic FROM doremi ORDER BY id";
my $sth = $dbh->prepare ($query);
$sth->execute ( );
my @items = ( );
while (my ($note, $mnemonic) = $sth->fetchrow_array ( ))
{
    # handle possibility of NULL (undef) values
    $note = (defined ($note) ? escapeHTML ($note) : "");
    $mnemonic = (defined ($mnemonic) ? escapeHTML ($mnemonic) : "");
    push (@items, dt ($note));
    push (@items, dd ($mnemonic));
}
print dl (@items);

Here is a slightly more complex example. Each term is a database name, and the corresponding definition indicates how many tables are in the database. The numbers are obtained by issuing a SHOW TABLES query for each database and counting the number of rows in the result:

# get list of database names
my $db_ref = $dbh->selectcol_arrayref ("SHOW DATABASES");
my @items = ( );
foreach my $db_name (@{$db_ref})
{
    # get list of table names in database; disable RaiseError for
    # this query, to prevent script termination in case the current
    # user has no access to the database
    $dbh->{RaiseError} = 0;
    my $tbl_ref = $dbh->selectcol_arrayref ("SHOW TABLES FROM $db_name");
    $dbh->{RaiseError} = 1;
    my $tbl_count = (defined ($tbl_ref)         # error?
                    ? @{$tbl_ref} . " tables"   # no, get table count
                    : "cannot access");         # yes, indicate problem
    push (@items, dt (escapeHTML ($db_name)));
    push (@items, dd (escapeHTML ($tbl_count)));
}
print dl (@items);

Note that it's necessary to take care not to die on an error when issuing SHOW TABLES statements, should the user running the script not have access to a given database.

17.3.7 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 var="row" items="${rs.rows}">
    <c:out value="${row.item}" /><br />
</c:forEach>

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

foreach my $item (@items)
{
    # handle possibility of NULL (undef) values
    $item = (defined ($item) ? escapeHTML ($item) : "");
    print $item . br ( );
}

17.3.8 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, then another query for each letter to find the states associated with each letter. You could also retrieve all the information using a single query, 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, 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);