MySQL Cookbook

17.5 Displaying Query Results as Hyperlinks

17.5.1 Problem

You want to use database content to generate clickable hyperlinks.

17.5.2 Solution

Add the proper tags to the content to generate anchor elements.

17.5.3 Discussion

The examples in the preceding sections generate static text, but database content also is useful for creating hyperlinks. If you store web site URLs or email addresses in a table, you can easily convert them to active links in web pages. All you need to do is encode the information properly and add the appropriate HTML tags.

Suppose you have a table that contains company names and web sites, such as the following book_vendor table that lists book sellers and publishers:

mysql> SELECT * FROM book_vendor ORDER BY name;
+-----------------------+------------------+
| name                  | website          |
+-----------------------+------------------+
| Barnes & Noble        | www.bn.com       |
| Bookpool              | www.bookpool.com |
| Borders               | www.borders.com  |
| Fatbrain              | www.fatbrain.com |
| O'Reilly & Associates | www.oreilly.com  |
+-----------------------+------------------+

This table has content that readily lends itself to the creation of hyperlinked text. To produce a hyperlink from a row, add the http:// protocol designator to the website value, use the result as the href attribute for an <a> anchor tag, and use the name value in the body of the tag to serve as the link label. For example, the row for Barnes & Noble can be written like this:

<a href="http://www.bn.com">Barnes &amp; Noble</a>

JSP code to produce a bullet (unordered) list of hyperlinks from the table contents looks like this:

<sql:query var="rs" dataSource="${conn}">
    SELECT name, website FROM book_vendor ORDER BY name
</sql:query>

<ul>
<c:forEach var="row" items="${rs.rows}">
    <li>
        <a href="http://<c:out value="${row.website}" />">
            <c:out value="${row.name}" /></a>
    </li>
</c:forEach>
</ul>

When displayed in a web page, each vendor name in the list becomes an active link that may be selected to visit the vendor's web site. In Python, the equivalent operation looks like this:

query = "SELECT name, website FROM book_vendor ORDER BY name"
cursor = conn.cursor ( )
cursor.execute (query)
items = [ ]
for (name, website) in cursor.fetchall ( ):
    items.append ("<a href=\"http://%s\">%s</a>" \
                % (urllib.quote (website), cgi.escape (name, 1)))
cursor.close ( )

# print items, but don't encode them; they're already encoded
print make_unordered_list (items, 0)

CGI.pm-based Perl scripts produce hyperlinks by invoking the a( ) function as follows:

a ({-href => "url-value"}, "link label")

The function can be used to produce the vendor link list like this:

my $sth = $dbh->prepare (
                "SELECT name, website
                FROM book_vendor
                ORDER BY name");
$sth->execute ( );
my @items = ( );
while (my ($name, $website) = $sth->fetchrow_array ( ))
{
    push (@items, a ({-href => "http://$website"}, escapeHTML ($name)));
}
print ul (li (\@items));

Generating links using email addresses is another common web programming task. Assume that you have a table newsstaff that lists the department, name, and (if known) email address for the news anchors and reporters employed by a television station, WRRR:

mysql> SELECT * FROM newsstaff;
+------------------+----------------+-------------------------+
| department       | name           | email                   |
+------------------+----------------+-------------------------+
| Sports           | Mike Byerson   | mbyerson@wrrr-news.com  |
| Sports           | Becky Winthrop | bwinthrop@wrrr-news.com |
| Weather          | Bill Hagburg   | bhagburg@wrrr-news.com  |
| Local News       | Frieda Stevens | NULL                    |
| Local Government | Rex Conex      | rconex@wrrr-news.com    |
| Current Events   | Xavier Ng      | xng@wrrr-news.com       |
| Consumer News    | Trish White    | twhite@wrrr-news.com    |
+------------------+----------------+-------------------------+

From this you want to produce an online directory containing email links to all personnel, so that site visitors can easily send mail to any staff member. For example, a record for a sports reporter named Mike Byerson with an email address of mbyerson@wrrr-news.com will become an entry in the listing that looks like this:

Sports: <a href="mailto:mbyerson@wrrr-news.com">Mike Byerson</a>

It's easy to use the table's contents to produce such a directory. First, let's put the code to generate an email link into a helper function, because it's the kind of operation that's likely to be useful in several scripts. In Perl, the function might look like this:

sub make_email_link
{
my ($name, $addr) = @_;

    # return name as static text if address is undef or empty
    return (escapeHTML ($name)) if !defined ($addr) || $addr eq "";
    # return a hyperlink otherwise
    return (a ({-href => "mailto:$addr"}, escapeHTML ($name)));
}

The function is written to handle instances where the person has no email address by returning just the name as static text. To use the function, write a loop that pulls out names and addresses and displays each email link preceded by the staff member's department:

my $sth = $dbh->prepare (
            "SELECT department, name, email
            FROM newsstaff
            ORDER BY department, name");
$sth->execute ( );
my @items = ( );
while (my ($dept, $name, $email) = $sth->fetchrow_array ( ))
{
    push (@items, escapeHTML ($dept) . ": " . make_email_link ($name, $email));
}
print ul (li (\@items));

Equivalent email link generator functions for PHP and Python look like this:

function make_email_link ($name, $addr)
{
    # return name as static text if address is unset or empty
    if (!isset ($addr) || $addr == "")
        return (htmlspecialchars ($name));
    # return a hyperlink otherwise
    return (sprintf ("<a href=\"mailto:%s\">%s</a>",
                        $addr, htmlspecialchars ($name)));
}

def make_email_link (name, addr):
    # return name as static text if address is None or empty
    if addr is None or addr == "":
        return (cgi.escape (name, 1))
    # return a hyperlink otherwise
    return ("<a href=\"mailto:%s\">%s</a>" % (addr, cgi.escape (name, 1)))

For a JSP page, you can produce the newsstaff listing as follows:

<sql:query var="rs" dataSource="${conn}">
    SELECT department, name, email
    FROM newsstaff
    ORDER BY department, name
</sql:query>

<ul>
<c:forEach var="row" items="${rs.rows}">
    <li>
        <c:out value="${row.department}" />:
        <c:set var="name" value="${row.name}" />
        <c:set var="email" value="${row.email}" />
        <c:choose>
            <%-- null or empty value test --%>
            <c:when test="${empty email}">
                <c:out value="${name}" />
            </c:when>
            <c:otherwise>
                <a href="mailto:<c:out value="${email}" />">
                    <c:out value="${name}" /></a>
            </c:otherwise>
        </c:choose>
    </li>
</c:forEach>
</ul>