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:
- 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, then print the result.
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
- and
opening and closing tags, and contains items surrounded by
- and
- tags:
- First item
- Second item
- Third item
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:
- 3 cups flour
- 1/2 cup raw ("unrefined") sugar
- 3 eggs
- pinch (< 1/16 teaspoon) salt
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:
SELECT item FROM ingredient ORDER BY id
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 ("
- "); while (list ($item) = mysql_fetch_row ($result_id)) print ("
- " . htmlspecialchars ($item) . "
- "); mysql_free_result ($result_id); print ("
");
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 = "
- "; reset ($items); while (list ($k, $v) = each ($items)) { if ($encode) $v = htmlspecialchars ($v); $str .= "
- $v
- "; } $str .= "
"; 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 = "
- " 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 + "
- " + item + "
- " list = list + "
" 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 tags. To prevent these from being converted to <a>, 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
- and
- 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 and 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.
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
- and
- and
:
- First item
- Second item
- Third item
For APIs where you print the tags directly, use the same procedure as for ordered lists, but print
- and
- and
. Here is an example in JSP:
SELECT item FROM ingredient ORDER BY id
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
and
tags. Each item has a term delimited byand
, and a definition delimited byand
: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
In a JSP page, you can generate the definition list like this:
SELECT note, mnemonic FROM doremi ORDER BY note
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 (" "); while (list ($note, $mnemonic) = mysql_fetch_row ($result_id)) { print ("
" . htmlspecialchars ($note) . "
"); print ("" . htmlspecialchars ($mnemonic) . " "); } mysql_free_result ($result_id); print ("
");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 = " "; reset ($terms); reset ($definitions); while (list ($dtk, $dtv) = each ($terms)) { list ($ddk, $ddv) = each ($definitions); if ($encode) { $dtv = htmlspecialchars ($dtv); $ddv = htmlspecialchars ($ddv); } $str .= "
$dtv
$ddv
"; } $str .= "
"; 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:
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);
Категории