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:

user','password') or die(mysql_error( ));
mysql_select_db('dbname') or die(mysql_error( ));
if (!$_GET[greek] && !$_GET[letter]){
 echo "

Great Greeks

"; $sql = " 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"; $cursor = mysql_query($sql) or die(mysql_error( )); while ($line = mysql_fetch_array($cursor,MYSQL_ASSOC)){ printf(" <a href="?letter=%s">%s</a> from %s to %s (%d entries)
", $line{letter},$line{letter},$line{fst},$line{lst},$line{cnt}); } } if ($_GET[letter]){ $sql = sprintf("SELECT name FROM greeks WHERE name LIKE '%s%%'", mysql_real_escape_string($_GET[letter])); $cursor = mysql_query($sql) or die(mysql_error( )); while ($line = mysql_fetch_array($cursor,MYSQL_ASSOC)){ printf(" <a href="?greek=%s">%s</a>
", urlencode($line{name}), $line{name}); } } if ($_GET[greek]){ printf("

Greeks beginning with %s

",$_GET{letter}); $sql = sprintf("SELECT name FROM greeks WHERE name='%s'", mysql_real_escape_string($_GET[greek])); $cursor = mysql_query($sql) or die(mysql_error( )); while ($line = mysql_fetch_array($cursor,MYSQL_ASSOC)){ echo $line{name} . " Found
"; } } ?>

6.3.5. Hacking the Hack

A user interface typically has 25 or 30 rows that can be displayed without scrolling. If you use columns you can display a great deal more in one screen, but you have to get the order right.

When presenting data alphabetically in columns, the order should be top to bottom and then left to right, as in a telephone book. You can get SQL to return data in the correct order to support this, as shown in Figure 6-6.

Figure 6-6. Alphabetic ordering: top to bottom, left to right

Let's say you want exactly 30 rows in your output. That means the first row (0) contains items 1, 31, 61, 92, and 121. The second row includes items 2, 32, 62, 92, and 122.

In general, element number n should be in row (n 1) % 30. Here % is the modulus operator. In Oracle, you would use MOD(n 1, 30).

If you have a table named ngreeks that includes i, the index number for every item, the following query returns the data in the right order:

mysql> SELECT name, (i-1) % 60 AS r, i FROM ngreeks
 -> ORDER BY (i-1) % 60, i;
+--------------+------+------+
| name | r | i |
+--------------+------+------+
| Achelous | 0 | 1 |
| Cadmus | 0 | 61 |
| Eurystheus | 0 | 121 |
| Marsyas | 0 | 181 |
| Phlegethon | 0 | 241 |
| Triton | 0 | 301 |
| Acheron | 1 | 2 |
| Calliope | 1 | 62 |
| Euterpe | 1 | 122 |
| Medea | 1 | 182 |
| Phosphor | 1 | 242 |
| Turnus | 1 | 302 |
| Achilles | 2 | 3 |
| Calypso | 2 | 63 |

This is the right order for the data if you want to put this it into a simple HTML table. It is useful to have the row value returned, because you can use a change in that value to indicate the end of each row. Here is the PHP code to display these names in columns of up to 30 rows each:

user','password') or die(mysql_error( ));
mysql_select_db('dbname') or die(mysql_error( ));

 echo "

Great Greeks

"; $sql = " SELECT name, (i-1) % 30 AS r FROM ngreeks ORDER BY (i-1) % 30, i "; $cursor = mysql_query($sql) or die(mysql_error( )); $row = 0; print ""; while ($line = mysql_fetch_array($cursor,MYSQL_ASSOC)){ if ($row!=$line{row}){ print " "; $row=$line{row}; } printf("", $line{name}); } print "

%s

"; ?>

If you do not have a column that gives the index of each row, "Calculate Rank" [Hack #40] or "Generate Sequential or Missing Data" [Hack #82] can help you get this value.