Sort html table

[kstarkey]

Hi,
I am using PHP and mysql to display database contents in an html table.
I am displaying the contents by one sort option and I want to add additional sort options for the table.
For example, I want each table heading to be a hyperlink that will resort the table. Similar to the sort options on the forums on this site.
Can this be done?
Thanks in advance for your help.
Katy

[Mandibal]

Yes. Easy solution is to make the hyperlink like

<a href="displayTable.php?sort=columnName">Column Name</a>

displayTable.php is the name of the page the table is displaying from so it calls itself and you can just add the check to see if the $_GET['sort'] variable is set. Then just add the $_GET['sort'] to your query in the order by clause. As long as column names in tables match (db/html).

[Messiah]

Mandibal is correct. Passing the column name through the query string is the quickest and easiest method. Just make sure to check the value of the passed variable before executing the query to avoid some ugly errors. Setting a default sort is always a good idea as well. For example:

// if sort isn't set
if ( empty ( $_GET['sort'] ) )
{
     // define the default sort column
     $_GET['sort'] = "columnName";
} 

[r937]

"Resubmit the page? Just to sort data we already have? I'm sure we can do better than that."

the above quote is from sorttable: Make all your tables sortable

not sure how you would modify this so that for non-javascript users, the urls would actually resubmit the page...

[Mandibal]

The only way to do it w/o resubmitting is with javascript. This is a PHP board so we gave PHP answers. IMO javascript is riskier because of the inconsistant implementation across browsers. Course that doesnt mean you cant or shouldnt do it. But with the PHP way its not going to be buggy in different browsers. To be fair, once Javascript is consistant no matter the browser this would be the best way w/o a doubt.

[r937]

but wait!! there's more!!

Table Sort in very well-written tutorial format

but alas, again, no specifics on how to handle non-javascript users

[Mandibal]

But wait your excluding users.

[kstarkey]

Thanks to all!
Mandibal and Messiah your suggestions did the trick! I knew there had to be a simple solution.
This is the code:


<h2>Security <br>
Bad Check List and RFCU Offender List</h2>

<table border =1 align="center">


<tr>
<th><a href="security_index.php?sort=date">Date Added</a></th>
<th><a href="security_index.php?sort=name">Name</a></th>
<th><a href="security_index.php?sort=ssn">SSN</a></th>
<th><a href="security_index.php?sort=license">ID</a></th>
<th><a href="security_index.php?sort=dob">DOB</a></th>
<th><a href="security_index.php?sort=category_name">Category</a></th>
</tr>
<?php

// If $_GET['sort'] is set, assign its value to the ORDER BY clause. Else, sort by default.

if(isset($_GET['sort']))
{
$sort = $_GET['sort'];
}
else
{
$sort = 'date DESC';
}


$result = @mysql_query("SELECT date, name, ssn, license, dob, category_name FROM members, category_list WHERE cid = category_list.list_id ORDER BY $sort");

if (!$result) {
die("<p>Error performing query: " . mysql_error().
"</p>");
}
//Display the name of each offender in a paragraph
while ($row = mysql_fetch_array($result)) {
$adate = $row['date'];
$name = $row['name'];
$ssn = $row['ssn'];
$license = $row['license'];
$dob = $row['dob'];
$cat = $row['category_name'];
echo("<tr><td width = 60 >$adate</td><td>$name</td><td width =75>$ssn</td> <td>$license</td><td width = 60>$dob</td><td width = 75>$cat</td></tr>");
}
?>
Thanks again!
Katy

[Messiah]

Katy,

Glad you got it working to your satisfaction. The only thing I would suggest changing is the following:

 if(isset($_GET['sort']))
 {
 $sort = $_GET['sort'];
 }
 else
  {
  $sort = 'date DESC';
  } 

To:

    if ( !empty ( $_GET['sort'] ) )
    {
        $sort = $_GET['sort'];
    }
    else
    {
        $sort = 'date DESC';
    } 

Its better to check using empty() rather than isset(). Checking a value of a variable using isset can be misleading. What if the variable is set, yet has no value? Your query will error. Just a suggestion.