I've got the code down for displaying a table in HTML from the MySQL database, however, for the final result I want to be able to sort them in ascending or descending order by clicking on the column header.
I'll just use the example I found, which I tried out, but it doesn't work.
- Code: Select all
// Connecting to MySQL database
$link = mysql_connect($mysql_server, $mysql_user_name, $mysql_user_pass) or die("Could not connect to MySQL database");
print "Connected to MySQL database successfully";
// To read from the database, select it
mysql_select_db("pbx") or die("Could not select database");
print ("<p>Table: " . htmlspecialchars ("pbx") . "</p>\n");
print ("<p>Click on a column name to sort the table by that column.</p>\n");
# Get the name of the column to sort by (optional). If missing, use
# column one. If present, perform simple validation on column name;
# it must consist only of alphanumeric or underscore characters.
$sort_col = get_param_val ("date"); # column name to sort by (optional)
if (!isset ($sort_col))
$sort_col = "1"; # just sort by first column
else if (!ereg ("^[0-9a-zA-Z_]+$", $sort_col))
die (htmlspecialchars ("Column name $sort_col is invalid"));
# Construct query to select records from the named table, optionally sorting
# by a particular column. Limit output to 50 rows to avoid dumping entire
# contents of large tables.
$query = " SELECT clientmatter,date,time,extention,charge,number,duration,location FROM pbx";
$query .= " WHERE date = '2002-12-24' AND charge > '0' ";
$query .= " ORDER BY $sort_col";
$query .= " LIMIT 50";
$result_id = mysql_query ($query, $link);
if (!$result_id)
die (htmlspecialchars (mysql_error ($link)));
# 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");
#@ _HEADER_ROW_
$self_path = get_self_path ();
print ("<tr>\n");
for ($i = 0; $i < mysql_num_fields ($result_id); $i++)
{
$col_name = mysql_field_name ($result_id, $i);
printf ("<th><a href=\"%s?sort=%s\">%s</a></th>\n",
$self_path,
urlencode ($col_name),
htmlspecialchars ($col_name));
}
print ("</tr>\n");
#@ _HEADER_ROW_
while ($row = mysql_fetch_row ($result_id))
{
print ("<tr>\n");
for ($i = 0; $i < mysql_num_fields ($result_id); $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");
}
mysql_free_result ($result_id);
print ("</table>\n");
mysql_close ($link);
?>
Now the only differences are that the db I'll be using is cd_test, the table is cd_info, and the select statement will be slightly different.
I honestly have no clue about what to change and where to change it. I'm teaching myself and learning by example so far... So if anyone could help it would be much appreciated!
