Sorting HTML Data Tables Using the Header Row with PHP

When displaying a 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 show a list of order by date; showing the newest order first. But what if the user wants the data organized differently? We could let them choose the column to sort by.

The Example

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

The problem is that the conference organizers receive requests asking if a specific customer has registered. Having the information sorted by date makes it more difficult to find the customer in question. That's where providing an option to temporarily change the sort order can help. First, we'll need to look at the existing code.

Note that this post won't be showing the entire script and assumes that you have some familiarity to working with HTML, PHP, and MySQL. If you have any question, feel free to post them in the comments section below.

The Existing Code

First the registration information is 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 need to 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.

Dynamically Changing the Sort Order

As mentioned earlier, we want conference organizers to 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

• Sorting HTML Data Tables Part 2: Dynamically Sort in Ascending and Descending Order