Database Reference
In-Depth Information
To retrieve the table and display its contents as an HTML table, use the techniques
discussed in
Recipe 19.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>
rec_id
</th>
<th>
name
</th>
<th>
trav_date
</th>
<th>
miles
</th>
</tr>
To make the headings active links that reinvoke the script to produce a display sorted
by a given column name, we must produce a header row that looks like this:
<tr>
<th><a
href=
"
script_name
?sort=rec_id">rec_id
</a></th>
<th><a
href=
"
script_name
?sort=name">name
</a></th>
<th><a
href=
"
script_name
?sort=trav_date">trav_date
</a></th>
<th><a
href=
"
script_name
?sort=miles">miles
</a></th>
</tr>
To generate such headings, the script must know the names of the columns in the table,
as well as its own URL. Recipes
10.6
and
20.1
show how to obtain this information using
statement metadata and information from 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
getColumnMeta(
i
)
returns metadata for column
i
:
print
(
'<tr>'
);
$ncols
=
$sth
->
columnCount
();
for
(
$i
=
0
;
$i
<
$ncols
;
$i
++
)
{
$col_info
=
$sth
->
getColumnMeta
(
$i
);
$col_name
=
$col_info
[
'name'
];
printf
(
'<th><a href="%s?sort=%s">%s</a></th>'
,
$_SERVER
[
'PHP_SELF'
],
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, 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 several possibilities:
