Displaying Query Results as Hyperlinks

2017-11-03 09:05:05

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:

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 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 & Noble</a>

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

SELECT name, website FROM book_vendor ORDER BY name

<a href="http://<c:out value=">"> </a>

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="">%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:

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="">%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="">%s</a>" % (addr, cgi.escape (name, 1)))

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

SELECT department, name, email FROM newsstaff ORDER BY department, name

: <%-- null or empty value test --%> <a href="mailto:<c:out value=">"> </a>

Категории