Generating Web Content from Query Results - MySQL - page 611

# fetch items for list
stmt = "SELECT item FROM ingredient ORDER BY id"
cursor = conn . cursor ()
cursor . execute ( stmt )
items = []
for ( item ,) in cursor :
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, for a list of items that themselves include
HTML tags, you wouldn't want the function to encode the special characters in those
tags. For example, if the list items are hyperlinks, each contains <a> tags. To prevent
these from being converted to &lt;a&gt; , pass make_ordered_list() a second argu‐
ment that evaluates to false.
If your API provides functions to generate HTML structures, you need not write them
yourself. That's the case for the Perl CGI.pm and Ruby cgi modules. In Perl, generate
each item by invoking its li() function to add the opening and closing item tags, save
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 code converts 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 in the general case,
you don't know that.)
The preceding example intertwines row fetching and HTML generation. To decouple
item fetching 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