You 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.
Make each column heading a hyperlink that redisplays the table, sorted by the corresponding column.
When 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 Displaying Query Results as Tables. 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
and 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> "); $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> ", $self_path, urlencode ($col_name), htmlspecialchars ($col_name)); } print ("</tr> ");
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:
You can retrieve the results unsorted.
You can hardwire one of the column names into the script as the default.
You can look up the column names from INFORMATION_SCHEMA
and use one of them
(such as the first) as the default. To look up the name on the
fly, use this statement:
SELECT COLUMN_NAME FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_SCHEMA = "cookbook" AND TABLE_NAME = "mail" AND ORDINAL_POSITION = 1;
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> "); print ("<p>Click on a column name to sort by that column.</p> "); # 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"> "); $self_path = get_self_path (); print ("<tr> "); $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> ", $self_path, urlencode ($col_name), htmlspecialchars ($col_name)); } print ("</tr> "); while ($row =& $result->fetchRow ()) { print ("<tr> "); 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> ", $val); } print ("</tr> "); } $result->free (); print ("</table> "); $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 Displaying Query Results as Tables).
3.138.135.80