MySQL Cookbook, 2nd Edition

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 ...