Hack 43. Add Navigation Features to Web Applications

Sometimes you want to present users with a list of values, but the list is too large to fit comfortably on a single web page. You could let users search for the item, but sometimes it is better to let them browse for it.

One of the worst crimes against interface design is the search box that almost always says "no," which you can see in Figure 6-3.

Figure 6-3. The search box that almost always says "no"

If the user does not get the spelling exactly right, no useful feedback is given. Another big drawback is that if this interface is the only way to access your pages, search engine spiders will never index your site. About the only advantage of this style of input is that it is easy to code.

Instead of presenting this search box, you could present a list with one row for each letter of the alphabet, using a single SQL statement, as shown in Figure 6-4 and Figure 6-5.

Figure 6-4. Great Greeks index

The first list will never be more than about 26 lines longthis should fit neatly into a corner of your web page, or you could show it in a drop-down menu. It gives your readers a much better idea of the database's scale, and search engines will be able to get to your content.

You can get the list of letters by doing a GROUP BY based on the first character of each name. In MySQL, you can use the SUBSTRING function:

mysql> SELECT SUBSTRING(name,1,1) AS ltr, -> MIN(name) AS fst, -> MAX(name) AS lst, -> COUNT(*) AS cnt -> FROM greeks GROUP BY SUBSTRING(name,1,1) -> ORDER BY 1; +--------+-------------+----------+-----+ | letter | fst | lst | cnt | +--------+-------------+----------+-----+ | A | Achelous | Avernus | 56 | | B | Bellerophon | Briseis | 4 | | C | Cadmus | Cyclopes | 28 | ... | W | Winds | Winds | 1 | | Z | Zephyrus | Zeus | 2 | +--------+-------------+----------+-----+ 21 rows in set (0.00 sec)

Figure 6-5. Great Greeks beginning with B

6.3.1. SQL Server and PostgreSQL

The commands shown will work unchanged in SQL Server and PostgreSQL.

6.3.2. Oracle

In Oracle, the corresponding function is SUBSTR, as in SUBSTR(name,1,1).

6.3.3. Access

Microsoft Access has the string functions from Basic. The MID function does the same job as SUBSTRING. So you can use MID(name,1,1).

6.3.4. Running the Hack

Here is the code in PHP: