Recipe 19.11. Generating Click to Sort Table Headings
Recipe 19.11. Generating "Click to Sort" Table HeadingsProblemYou want to display a query result in a web page as a table that enables the user to select which column to sort the table rows by. SolutionMake each column heading a hyperlink that redisplays the table, sorted by the corresponding column. DiscussionWhen a web script runs, it can determine what action to take by examining 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 has the following contents: mysql> SELECT * FROM mail; +---------------------+---------+---------+---------+---------+---------+ | t | srcuser | srchost | dstuser | dsthost | size | +---------------------+---------+---------+---------+---------+---------+ | 2006-05-11 10:15:08 | barb | saturn | tricia | mars | 58274 | | 2006-05-12 12:48:13 | tricia | mars | gene | venus | 194925 | | 2006-05-12 15:02:49 | phil | mars | phil | saturn | 1048 | | 2006-05-13 13:59:18 | barb | saturn | tricia | venus | 271 | | 2006-05-14 09:31:37 | gene | venus | barb | mars | 2291 | | 2006-05-14 11:52:17 | phil | mars | tricia | saturn | 5781 | | 2006-05-14 14:42:21 | barb | venus | barb | venus | 98151 | | 2006-05-14 17:03:01 | tricia | saturn | phil | venus | 2394482 | | 2006-05-15 07:17:48 | gene | mars | gene | saturn | 3824 | | 2006-05-15 08:50:57 | phil | venus | phil | venus | 978 | | 2006-05-15 10:25:52 | gene | mars | tricia | saturn | 998532 | | 2006-05-15 17:35:31 | gene | saturn | gene | mars | 3856 | | 2006-05-16 09:00:28 | gene | venus | barb | mars | 613 | | 2006-05-16 23:04:19 | phil | venus | barb | venus | 10294 | | 2006-05-17 12:49:23 | phil | mars | tricia | saturn | 873 | | 2006-05-19 22:21:51 | gene | saturn | gene | venus | 23992 | +---------------------+---------+---------+---------+---------+---------+ To retrieve the table and display its contents as an HTML table, you can use the techniques discussed in Section 18.3. Here we'll use those same concepts but modify them to produce "click to sort" table column headings. A "plain" HTML table includes a row of column headers consisting only of the column names: <tr> <th>t</th> <th>srcuser</th> <th>srchost</th> <th>dstuser</th> <th>dsthost</th> <th>size</th> </tr> 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: <tr> <th><a href="script_name?sort=t">t</a></th> <th><a href="script_name?sort=srcuser">srcuser</a></th> <th><a href="script_name?sort=srchost">srchost</a></th> <th><a href="script_name?sort=dstuser">dstuser</a></th> <th><a href="script_name?sort=dsthost">dsthost</a></th> <th><a href="script_name?sort=size">size</a></th> </tr> To generate such headings, the script needs to know the names of the columns in the table, as well as its own URL. Recipes Section 9.6 and Section 19.1 show how to obtain this information using statement 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 statement as follows, where tableInfo( ) returns an array containing metadata for a query result. $info[ i ] contains information about column i and $info[ i ][ "name" ] contains the column's name. $self_path = get_self_path (); print ("<tr>\n"); $info =& $conn->tableInfo ($result, NULL); if (PEAR::isError ($info)) die (htmlspecialchars ($result->getMessage ())); for ($i = 0; $i < $result->numCols (); $i++) { $col_name = $info[$i]["name"]; printf ("<th><a href=\"%s?sort=%s\">%s</a></th>\n", $self_path, urlencode ($col_name), htmlspecialchars ($col_name)); } print ("</tr>\n"); 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 statement of the following form: SELECT * FROM $tbl_name ORDER BY $sort_col LIMIT 50 There is a small bootstrapping problem for this kind of script. The first time you invoke it, there is no sort column name in the environment, so the script doesn't know which column to sort by initially. What should you do? There are a couple possibilities:
The following script looks up the name from INFORMATION_SCHEMA. It also uses a LIMIT clause when retrieving results as a precaution that prevents the script from dumping huge amounts of output if the table is large. <?php # clicksort.php - display query result as HTML table with "click to sort" # column headings # Rows from the database table are displayed as an HTML table. # Column headings are presented as hyperlinks that reinvoke the # script to redisplay the table sorted by the corresponding column. # The display is limited to 50 rows in case the table is large. require_once "Cookbook.php"; require_once "Cookbook_Webutils.php"; $title = "Table Display with Click-To-Sort Column Headings"; ?> <html> <head> <title><?php print ($title); ?></title> </head> <body bgcolor="white"> <?php # ---------------------------------------------------------------------- # names for database and table and default sort column; change as desired $db_name = "cookbook"; $tbl_name = "mail"; $conn =& Cookbook::connect (); if (PEAR::isError ($conn)) die ("Cannot connect to server: " . htmlspecialchars ($conn->getMessage ())); print ("<p>" . htmlspecialchars ("Table: $db_name.$tbl_name") . "</p>\n"); print ("<p>Click on a column name to sort by that column.</p>\n"); # Get the name of the column to sort by: If missing, use the first column. $sort_col = get_param_val ("sort"); if (!isset ($sort_col)) { $stmt = "SELECT COLUMN_NAME FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_SCHEMA = ? AND TABLE_NAME = ? AND ORDINAL_POSITION = 1"; $result =& $conn->query ($stmt, array ($db_name, $tbl_name)); if (PEAR::isError ($result)) die (htmlspecialchars ($result->getMessage ())); if (!(list ($sort_col) = $result->fetchRow ())) die (htmlspecialchars ($result->getMessage ())); $result->free (); } # Construct query to select records from the table, sorting by the # named column. (The column name comes from the environment, so quote # it to avoid an SQL injection attack.) # Limit output to 50 rows to avoid dumping entire contents of large tables. $stmt = "SELECT * FROM $db_name.$tbl_name"; $stmt .= " ORDER BY " . $conn->quoteIdentifier ($sort_col); $stmt .= " LIMIT 50"; $result =& $conn->query ($stmt); if (PEAR::isError ($result)) die (htmlspecialchars ($result->getMessage ())); # Display query results as HTML table. Use query metadata to get column # names, and display names in first row of table as hyperlinks that cause # the table to be redisplayed, sorted by the corresponding table column. print ("<table border=\"1\">\n"); $self_path = get_self_path (); print ("<tr>\n"); $info =& $conn->tableInfo ($result, NULL); if (PEAR::isError ($info)) die (htmlspecialchars ($result->getMessage ())); for ($i = 0; $i < $result->numCols (); $i++) { $col_name = $info[$i]["name"]; printf ("<th><a href=\"%s?sort=%s\">%s</a></th>\n", $self_path, urlencode ($col_name), htmlspecialchars ($col_name)); } print ("</tr>\n"); while ($row =& $result->fetchRow ()) { print ("<tr>\n"); for ($i = 0; $i < $result->numCols (); $i++) { # encode values, using for empty cells $val = $row[$i]; if (isset ($val) && $val != "") $val = htmlspecialchars ($val); else $val = " "; printf ("<td>%s</td>\n", $val); } print ("</tr>\n"); } $result->free (); print ("</table>\n"); $conn->disconnect (); ?> </body> </html> The $sort_col value comes from the sort parameter of the environment, so it should be considered dangerous: An attacker might submit a URL with a sort parameter designed to attempt an SQL injection attack. To prevent this, $sort_col should be quoted when you construct the SELECT statement that retrieves rows from the displayed table. You cannot use a placeholder to quote the value because that technique applies to data values. ($sort_col is used as an identifier, not a data value.) However, some MySQL APIs, PEAR DB among them, provide an identifier-quoting function. clicksort.php uses quoteIdentifier( ) to make the $sort_col value safe for inclusion in the SQL statement. Another approach to validating the column name is to check the COLUMNS table of INFORMATION_SCHEMA. If the sort column is not listed there, it is invalid. The clicksort.php script shown here does not do that. However, the recipes distribution contains a Perl counterpart script, clicksort.pl, that does perform this kind of check. Have a look at it if you want more information. The cells in the rows following the header row contain the data values from the database table, displayed as static text. Empty cells are displayed using so that they display with the same border as nonempty cells (see Section 18.3). |
