Sorting HTML Data Tables Using the Header Row with PHP(1)

The Article By Patrick Nichols

When displaying data tables, how do you usually sort the information? Do you sort by the field that you think is the most important to the visitor? For example, it's perfectly acceptable to list entries chronologically; where the newest is shown first. But what if the user wants the data organized differently? We could let them choose.

Background

Let's say we have a MySQL table containing the conference registrations and a solution for conference organizers to access the information. To make it easier for processing the registrations, the entries are listed by the submission date in reverse-chronological order. That way the newest orders appear on top.

The conference organizers, however, might need to know if a specific customer is registered. Having the information sorted by date increases the difficulty of finding the customer. That's where letting the user change the sort order can help.

Note: this post won't show the entire script and assumes you're familiar with HTML, PHP, and MySQL. If you have question, please post them in the comments below.

Existing Code

First, the registrations are pulled from the database, processed, and stored in the $registrantList variable to be displayed later.

<?php
//GET THE LIST OF REGISTRANTS
$registrantList = '';
$sql = "SELECT first_name, last_name, date FROM conference_registrants ORDER BY date DESC";
$result = mysql_query($sql);
while($row = mysql_fetch_array($result)) {
     $registrantList .= '<tr><td>' . htmlentities($row['first_name']) . '</td><td>' . htmlentities($row['last_name']) . '</td><td>' . $row['date'] . '</td></tr>';
}
?>

Note that the information is sorted by the "date" field in descending order. We'll figure out how to change this on the fly later. For now, the data is displayed by date.

<?php
//DISPLAY THE CONFERENCE REGISTRANTS
print '<table cellpadding="3" cellspacing="1" border="1">';
print '<tr>';
print '<th scope="col">First Name</th>';
print '<th scope="col">Last Name</th>';
print '<th scope="col">Date Registered</th>';
print '</tr>';
print $registrantList;
print '</table>';
?>

The existing code is fairly straight forward, so hopefully everything makes sense. Let's move ahead to dynamically changing the sort order.

Changing Sort Order Dynamically

As mentioned earlier, conference organizers should be able to sort the registrants as needed. To do that, we'll pass a flag through the program that indicates the column to sort by. So, let's add some links to the HTML table that displays the registrants. The "First Name" heading will look like:

<?php
print '<th scope="col">';
print '<a href="/registrants.php?orderBy=first_name">First Name</a>';
print '</th>';
?>

The link points to the same page we're currently modifying and passes a GET variable called "orderBy" when clicked. Links are also needed for the other headings.

<?php
//DISPLAY THE CONFERENCE REGISTRANTS
print '<table cellpadding="3" cellspacing="1" border="1">';
print '<tr>';
print '<th scope="col">';
print '<a href="/registrants.php?orderBy=first_name">First Name</a>';
print '</th>';
print '<th scope="col">';
print '<a href="/registrants.php?orderBy=last_name">Last Name</a>';
print '</th>';
print '<th scope="col">';
print '<a href="/registrants.php?orderBy=date_desc">Date Registered</a>';
print '</th>';
print '</tr>';
print $registrantList;
print '</table>';
?>

With the flag being passed, the script needs to be modified to process it. But we should probably make sure the flag exists first. If it doesn't, the value would be defaulted to the date in descending order.

<?php
//IF THE FLAG HASN'T BEEN SET YET, SET THE DEFAULT
if(!isset($_GET['orderBy'])) {
     $_GET['orderBy'] = 'date_desc';
}
?>

Since the GET variable could have been tampered with, we need to make sure the value is valid. Otherwise, the program may not function as expected or worse—damage the database.

<?php
//FIGURE OUT HOW TO SORT THE TABLE
switch($_GET['orderBy']) {
     case 'first_name':
     case 'last_name':
          $sql_orderBy = $_GET['orderBy'];
          break;
 
     default:
          $_GET['orderBy'] = 'date_desc';
          $sql_orderBy     = 'date DESC';
}
?>

In addition to the GET variable being sanitized, we now have a variable ($sql_orderBy) which will be used for the order by clause in the following SQL query:

<?php
//GET THE LIST OF REGISTRANTS
$registrantList = '';
$sql = "SELECT first_name, last_name, date FROM 2011_registrants ORDER BY $sql_orderBy";
$result = mysql_query($sql);
while($row = mysql_fetch_array($result)) {
     $registrantList .= '<tr><td>' . htmlentities($row['first_name']) . '</td><td>' . htmlentities($row['last_name']) . '</td><td>' . $row['date'] . '</td></tr>';
}
?>

With all the code in place, visitors can now sort the data by clicking the column headings. But there's still one more thing to do. When sorting by first name, for example, the "First Name" heading doesn't need to be clickable any more. That can be fixed by hiding the link if the orderBy flag is set to "first_name".

<?php
print '<th scope="col">';
if($_GET['orderBy'] == 'first_name') { print 'First Name'; }
else                                 { print '<a href="/registrants.php?orderBy=first_name">First Name</a>'; }
print '</th>';
?>

As an added bonus, hiding the links provides a hint to which column the data is sorted by. We just need to do the same with the other columns.

<?php
//DISPLAY THE CONFERENCE REGISTRANTS
print '<table cellpadding="3" cellspacing="1" border="1">';
print '<tr>';
print '<th scope="col">';
if($_GET['orderBy'] == 'first_name') { print 'First Name'; }
else                                 { print '<a href="/registrants.php?orderBy=first_name">First Name</a>'; }
print '</th>';
print '<th scope="col">';
if($_GET['orderBy'] == 'last_name')  { print 'Last Name'; }
else                                 { print '<a href="/registrants.php?orderBy=last_name">Last Name</a>'; }
print '</th>';
print '<th scope="col">';
if($_GET['orderBy'] == 'date_desc')  { print 'Date Registered'; }
else                                 { print '<a href="/registrants.php?orderBy=date_desc">Date Registered</a>'; }
print '</th>';
print '</tr>';
print $registrantList;
print '</table>';
?>

Final Code

In the end, here is what the completed code looks like all together:

<?php
//IF THE FLAG HASN'T BEEN SET YET, SET THE DEFAULT
if(!isset($_GET['orderBy'])) {
     $_GET['orderBy'] = 'date_desc';
}
 
//FIGURE OUT HOW TO SORT THE TABLE
switch($_GET['orderBy']) {
     case 'first_name':
     case 'last_name':
          $sql_orderBy = $_GET['orderBy'];
          break;
 
     default:
          $_GET['orderBy'] = 'date_desc';
          $sql_orderBy = 'date DESC';
}
 
//GET THE LIST OF REGISTRANTS
$registrantList = '';
$sql = "SELECT first_name, last_name, date FROM 2011_registrants ORDER BY $sql_orderBy";
$result = mysql_query($sql);
while($row = mysql_fetch_array($result)) {
     $registrantList .= '<tr><td>' . htmlentities($row['first_name']) . '</td><td>' . htmlentities($row['last_name']) . '</td><td>' . $row['date'] . '</td></tr>';
}
 
//DISPLAY THE CONFERENCE REGISTRANTS
print '<table cellpadding="3" cellspacing="1" border="1">';
print '<tr>';
print '<th scope="col">';
if($_GET['orderBy'] == 'first_name') { print 'First Name'; }
else { print '<a href="/registrants.php?orderBy=first_name">First Name</a>'; }
print '</th>';
print '<th scope="col">';
if($_GET['orderBy'] == 'last_name') { print 'Last Name'; }
else { print '<a href="/registrants.php?orderBy=last_name">Last Name</a>'; }
print '</th>';
print '<th scope="col">';
if($_GET['orderBy'] == 'date_desc') { print 'Date Registered'; }
else { print '<a href="/registrants.php?orderBy=date_desc">Date Registered</a>'; }
print '</th>';
print '</tr>';
print $registrantList;
print '</table>';
?>

Related Posts

•  http://www.cnblogs.com/macall/p/3622816.html
  

Ps.The Origin Source：http://www.cyberscorpion.com/2011-11/sorting-html-data-tables-using-the-header-row-with-php/