MySQL Cookbook

17.6 Creating a Navigation Index from Database Content

17.6.1 Problem

A list of items in a web page is long. You want to make it easier to move around in the page.

17.6.2 Solution

Create a navigational index containing links to different sections of the list.

17.6.3 Discussion

It's easy to display lists in web pages (Recipe 17.3). But if a list contains a lot of items, the page containing it may become quite long. In such cases, it's often useful to break up the list into sections and provide a navigation index, in the form of hyperlinks that allow users to reach sections of the list quickly without scrolling the page manually. For example, if you retrieve records from a table and display them grouped into sections, you can include an index that lets the user jump directly to any section. The same idea can be applied to multiple-page displays as well, by providing a navigation index in each page so that users can reach any other page easily.

This section provides two examples to illustrate these techniques, both of which are based on the kjv table that was introduced in Recipe 4.12. The examples implement two kinds of display, using the verses from the book of Esther stored in the kjv table:

• A single-page display that lists all verses in all chapters of Esther. The list is broken into 10 sections (one per chapter), with a navigation index that has links pointing to the beginning of each section.

• A multiple-page display consisting of pages that each show the verses from a single chapter of Esther, and a main page that instructs the user to choose a chapter. Each of these pages also displays a list of chapters as hyperlinks to the pages that display the corresponding chapter verses. These links allow any page to be reached easily from any other.

17.6.4 Creating a Single-Page Navigation Index

This example displays all verses in Esther in a single page, with verses grouped into sections by chapter. To display the page so that each section contains a navigation marker, place an <a name> anchor element before each chapter's verses:

<a name="1">Chapter 1</a>
    ... list of verses in chapter 1...
<a name="2">Chapter 2</a>
    ... list of verses in chapter 2...
<a name="3">Chapter 3</a>
    ... list of verses in chapter 3...
...

This generates a list that includes a set of markers named 1, 2, 3, and so forth. To construct the navigation index, build a set of hyperlinks, each of which points to one of the name markers:

<a href="#1>Chapter 1</a>
<a href="#2>Chapter 2</a>
<a href="#3>Chapter 3</a>
...

The # in each href attribute signifies that the link points to a location within the same page. For example, href="#3" points to the anchor with the name="3" attribute.

To implement this kind of navigation index, you can use a couple of approaches:

• Retrieve the verse records into memory and determine from them which entries are needed in the navigation index. Then print both the index and verse list.

• Figure out all the applicable anchors in advance and construct the index first. The list of chapter numbers can be determined by this statement:

  SELECT DISTINCT cnum FROM kjv WHERE bname = 'Esther' ORDER BY cnum;

  You can use the query result to build the navigation index, then fetch the verses for the chapters later to create the page sections that the index entries point to.

Here's a script, esther1.pl, that uses the first approach. It's an adaptation of one of the nested-list examples shown in Recipe 17.3.

#! /usr/bin/perl -w
# esther1.pl - display book of Esther in a single page, with navigation index

use strict;
use lib qw(/usr/local/apache/lib/perl);
use CGI qw(:standard escape escapeHTML);
use Cookbook;

my $title = "The Book of Esther";

my $page = header ( )
            . start_html (-title => $title, -bgcolor => "white")
            . h3 ($title);

my $dbh = Cookbook::connect ( );

# Retrieve verses from the book of Esther and associate each one with the
# list of verses for the chapter it belongs to.

my $sth = $dbh->prepare (
            "SELECT cnum, vnum, vtext FROM kjv
            WHERE bname = 'Esther'
            ORDER BY cnum, vnum");
$sth->execute ( );
my %verses = ( );
while (my ($cnum, $vnum, $vtext) = $sth->fetchrow_array ( ))
{
    # initialize chapter's verse list to empty array if this
    # is first verse for it, then add verse number/text to array.
    $verses{$cnum} = [ ] unless exists ($verses{$cnum});
    push (@{$verses{$cnum}}, p (escapeHTML ("$vnum. $vtext")));
}

# Determine all chapter numbers and use them to construct a navigation
# index.  These are links of the form <a href="#num>Chapter num</a>, where
# num is a chapter number a'#' signifies a within-page link.  No URL- or
# HTML-encoding is done here (the text that is displayed here doesn't need
# it).  Make sure to sort chapter numbers numerically (use { a <=> b }).
# Separate links by non-breaking spaces.

my $nav_index;
foreach my $cnum (sort { $a <=> $b } keys (%verses))
{
    $nav_index .= "&nbsp;" if $nav_index;
    $nav_index .= a ({-href => "#$cnum"}, "Chapter $cnum");
}

# Now display list of verses for each chapter.  Precede each section with
# a label that shows the chapter number and a copy of the navigation index.

foreach my $cnum (sort { $a <=> $b } keys (%verses))
{
    # add an <a name> anchor for this section of the state display
    $page .= p (a ({-name => $cnum}, font ({-size => "+2"}, "Chapter $cnum"))
                . br ( )
                . $nav_index);
    $page .= join ("", @{$verses{$cnum}});  # add array of verses for chapter
}

$dbh->disconnect ( );

$page .= end_html ( );

print $page;

exit (0);

17.6.5 Creating a Multiple-Page Navigation Index

This example shows a Perl script, esther2.pl, that is capable of generating any of several pages, all based on the verses in the book of Esther stored in the kjv table. The initial page displays a list of the chapters in the book, along with instructions to select a chapter. Each chapter in the list is a hyperlink that reinvokes the script to display the list of verses in the corresponding chapter. Because the script is responsible for generating multiple pages, it must be able to determine which page to display each time it runs. To make that possible, the script examines its own URL for a chapter parameter that indicates the number of the chapter to display. If no chapter parameter is present, or its value is not an integer, the script will display its initial page.

The URL to request the initial page looks like this:

http://apache.snake.net/cgi-bin/esther2.pl

The links to individual chapter pages have the following form, where cnum is a chapter number:

http://apache.snake.net/cgi-bin/esther2.pl?chapter=cnum

esther2.pl uses the CGI.pm param( ) function to obtain the chapter parameter value like so:

my $cnum = param ("chapter");
if (!defined ($cnum) || $cnum !~ /^\d+$/)
{
    # No artist ID was present or it was malformed
}
else
{
    # A valid artist ID was present
}

If no chapter parameter is present in the URL, $cnum will be undef. Otherwise, $cnum is set to the parameter value, which we check to make sure that it's an integer. (That covers the case where a garbage value may have been specified by someone trying to crash the script.)

Here is the entire esther2.pl script:

#! /usr/bin/perl -w
# esther2.pl - display book of Esther over multiple pages, one page per
# chapter, with navigation index

use strict;
use lib qw(/usr/local/apache/lib/perl);
use CGI qw(:standard escape escapeHTML);
use Cookbook;

my $title = "The Book of Esther";

my $page = header ( ) . start_html (-title => $title, -bgcolor => "white");

my ($left_panel, $right_panel);

my $dbh = Cookbook::connect ( );

my $cnum = param ("chapter");
if (!defined ($cnum) || $cnum !~ /^\d+$/)
{
    # Missing or malformed chapter; display main page with a left panel
    # that lists all chapters as hyperlinks and a right panel that provides
    # instructions.
    $left_panel = get_chapter_list ($dbh, 0);
    $right_panel = p (strong ("The Book of Esther"))
                    . p ("Select a chapter from the list at left.");
}
else
{
    # Chapter number was given; display a left panel that lists chapters
    # chapters as hyperlinks (except for current chapter as bold text)
    # and a right panel that lists the current chapter's verses.
    $left_panel = get_chapter_list ($dbh, $cnum);
    $right_panel = p (strong ("The Book of Esther"))
                    . get_verses ($dbh, $cnum);
}

$dbh->disconnect ( );

# Arrange the page as a one-row, three-cell table (middle cell is a spacer)

$page .= table (Tr (
                    td ({-valign => "top", -width => "15%"}, $left_panel),
                    td ({-valign => "top", -width => "5%"}, " "),
                    td ({-valign => "top", -width => "75%"}, $right_panel)
                ));

$page .= end_html ( );

print $page;

exit (0);

# ----------------------------------------------------------------------

# Construct navigation index as a list of links to the pages for each chapter
# in the the book of Esther.  Labels are of the form "Chapter n"; the chapter
# numbers are incorporated into the links as chapter=num parameters

# $dbh is the database handle, $cnum is the number of the chapter for
# which information is currently being displayed.  The label in the chapter
# list corresponding to this number is displayed as static text; the others
# are displayed as hyperlinks to the other chapter pages.  Pass 0 to make
# all entries hyperlinks (no valid chapter has a number 0).

# No encoding is done because the chapter numbers are digits and don't
# need it.

sub get_chapter_list
{
my ($dbh, $cnum) = @_;

    my $nav_index;
    my $ref = $dbh->selectcol_arrayref (
                "SELECT DISTINCT cnum FROM kjv
                WHERE bname = 'Esther' ORDER BY cnum"
            );
    foreach my $cur_cnum (@{$ref})
    {
        my $link = url ( ) . "?chapter=$cur_cnum";
        my $label = "Chapter $cur_cnum";
        $nav_index .= br ( ) if $nav_index;          # separate entries by <br>
        # use static bold text if entry is for current artist,
        # use a hyperlink otherwise
        $nav_index .= ($cur_cnum == $cnum
                            ? strong ($label)
                            : a ({-href => $link}, $label));
    }
    return ($nav_index);
}

# Get the list of verses for a given chapter.  If there are none, the
# chapter number was invalid, but handle that case sensibly.

sub get_verses
{
my ($dbh, $cnum) = @_;

    my $ref = $dbh->selectall_arrayref (
                    "SELECT vnum, vtext FROM kjv
                    WHERE bname = 'Esther' AND cnum = ?",
                    undef, $cnum);
    my $verses = "";
    foreach my $row_ref (@{$ref})
    {
        $verses .= p (escapeHTML ("$row_ref->[0]. $row_ref->[1]"));
    }
    return ($verses eq ""           # no verses?
            ? p ("No verses in chapter $cnum were found.")
            : p ("Chapter $cnum:") . $verses);
}

17.6.6 See Also

esther2.pl examines its execution environment using the param( ) function. Web script parameter processing is discussed further in Recipe 18.6.