Recipe 18.4. Displaying Query Results as Hyperlinks

Problem

You want to use database content to generate clickable hyperlinks.

Solution

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

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 that you have a table that contains company names and web sites, such as the following book_vendor table that lists booksellers and publishers:

mysql> SELECT * FROM book_vendor ORDER BY name; +----------------+------------------+ | name           | website          | +----------------+------------------+ | Amazon.com     | www.amazon.com   | | Barnes & Noble | www.bn.com       | | Bookpool       | www.bookpool.com | | O'Reilly Media | 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 bulleted (unordered) list of hyperlinks from the table contents looks like this:

<sql:query dataSource="${conn}" var="rs">   SELECT name, website FROM book_vendor ORDER BY name </sql:query> <ul> <c:forEach items="${rs.rows}" var="row">   <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:

stmt = "SELECT name, website FROM book_vendor ORDER BY name" cursor = conn.cursor () cursor.execute (stmt) 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, False) 

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

Ruby scripts use the cgi module a method to produce hyperlinks:

stmt = "SELECT name, website FROM book_vendor ORDER BY name" list = "" dbh.execute(stmt) do |sth|   sth.fetch do |row|     list << cgi.li {               cgi.a("href" => "http://#{row[1]}") {                 CGI.escapeHTML(row[0].to_s)               }             }   end end list = cgi.ul { list } 

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   | [e-mail]  | | Sports           | Becky Winthrop | [e-mail] | | Weather          | Bill Hagburg   | [e-mail]  | | Local News       | Frieda Stevens | NULL                    | | Local Government | Rex Conex      | [e-mail]    | | Current Events   | Xavier Ng      | [e-mail]       | | Consumer News    | Trish White    | [e-mail]    | +------------------+----------------+-------------------------+ 

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 row for a sports reporter named Mike Byerson with an email address of [e-mail] will become an entry in the listing that looks like this:

Sports: <a href="mailto:[e-mail]">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) = @_;   $name = escapeHTML ($name);   # return name as static text if address is undef or empty   return $name if !defined ($addr) || $addr eq "";   # return a hyperlink otherwise   return a ({-href => "mailto:$addr"}, $name); } 

The function handles 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 $stmt = "SELECT department, name, email FROM newsstaff             ORDER BY department, name"; my $sth = $dbh->prepare ($stmt); $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 Ruby, PHP, and Python look like this:

def make_email_link(name, addr = nil)   name = CGI.escapeHTML(name.to_s)   # return name as static text if address is nil or empty   return name if addr.nil? or addr == ""   # return a hyperlink otherwise   return "<a href=\"mailto:#{addr}\">#{name}</a>" end function make_email_link ($name, $addr = NULL) {   $name = htmlspecialchars ($name);   # return name as static text if address is NULL or empty   if ($addr === NULL || $addr == "")     return ($name);   # return a hyperlink otherwise   return (sprintf ("<a href=\"mailto:%s\">%s</a>", $addr, $name)); } def make_email_link (name, addr = None):   name = cgi.escape (name, 1)   # return name as static text if address is None or empty   if addr is None or addr == "":     return name   # return a hyperlink otherwise   return "<a href=\"mailto:%s\">%s</a>" % (addr, name) 

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

<sql:query dataSource="${conn}" var="rs">   SELECT department, name, email   FROM newsstaff   ORDER BY department, name </sql:query> <ul> <c:forEach items="${rs.rows}" var="row">   <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>