Database Reference
In-Depth Information
• Retrieve the results unsorted.
• Hardwire one of the column names into the script as the default.
• Retrieve the column names from
INFORMATION_SCHEMA
and use one of them (such
as the first) as the default:
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
LIM
IT
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_Utils.php"
;
require_once
"Cookbook_Webutils.php"
;
$title
=
"Table Display with Click-To-Sort Column Headings"
;
?>
<html>
<head><title>
<?php
print
(
$title
);
?>
</title></head>
<body>
<?php
# names for database and table and default sort column; change as desired
$db_name
=
"cookbook"
;
$tbl_name
=
"driver_log"
;
$dbh
=
Cookbook
::
connect
();
print
(
"<p>"
.
htmlspecialchars
(
"Table:
$db_name
.
$tbl_name
"
)
.
"</p>"
);
print
(
"<p>Click 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 = ?