Generating Click to Sort Table Headings

2017-11-03 09:05:05

18.12.1 Problem

You want to display a query result in a web page as a table that allows the user to select which column to sort the table rows by.

18.12.2 Solution

Make each column heading a hyperlink that redisplays the table, sorted by the corresponding column.

18.12.3 Discussion

When a web script runs, it can determine what action to take by querying its environment to find out what parameters are present and what their values are. In many cases these parameters come from a user, but there's no reason a script cannot add parameters to URLs itself. This is one way a given invocation of a script can send information to the next invocation. The effect is that the script communicates with itself by means of URLs that it generates to cause specific actions. An application of this technique is for showing the result of a query such that a user can select which column of the result to use for sorting the display. This is done by making the column headers active links that redisplay the table, sorted by the selected column.

The examples here use the mail table, which looks like this:

To retrieve the table and display its contents as an HTML table, you can use the techniques discussed in Recipe 17.4. Here we'll use those same concepts but modify them to produce "click to sort" table column headings.

A "plain" HTML table would include a row of column headers consisting only of the column names:

t srcuser srchost dstuser dsthost size

To make the headings active links that reinvoke the script to produce a display sorted by a given column name, we need to produce a header row that looks like this:

<a href="<TT>script_name</TT>?sort=t">t</a> <a href="<TT>script_name</TT>?sort=srcuser">srcuser</a> <a href="<TT>script_name</TT>?sort=srchost">srchost</a> <a href="<TT>script_name</TT>?sort=dstuser">dstuser</a> <a href="<TT>script_name</TT>?sort=dsthost">dsthost</a> <a href="<TT>script_name</TT>?sort=size">size</a>

To generate such headings, the script needs to know the names of the columns in the table, as well as its own URL. Recipe 9.6 and Recipe 18.2 show how to obtain this information using query metadata and information in the script's environment. For example, in PHP, a script can generate the header row for the columns in a given query like this:

$self_path = get_self_path ( ); print (" "); for ($i = 0; $i < mysql_num_fields ($result_id); $i++) { $col_name = mysql_field_name ($result_id, $i); printf ("<a href="">%s</a> ", $self_path, urlencode ($col_name), htmlspecialchars ($col_name)); } print (" ");

The following script, clicksort.php, implements this kind of table display. It checks its environment for a sort parameter that indicates which column to use for sorting. The script then uses the parameter to construct a query of the following form:

SELECT * FROM $tbl_name ORDER BY $sort_col LIMIT 50

(If no sort parameter is present, the script uses ORDER BY 1 to produce a default of sorting by the first column.) The LIMIT clause is simply a precaution to prevent the script from dumping huge amounts of output if the table is large.

Here's what the script looks like:

Категории