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

In last week's post we looked at dynamically sorting HTML data tables. But we only talked about sorting the columns in either ascending or descending. If the user is looking for a last name that appears near the end of the alphabet and the column is sorted from A to Z, they may have a lot of names to go through before finding the one they want. Instead we could provide an option for sorting in both descending and ascending order.

Background

So let's take final code from last week:

<?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>';
?>

If you haven't done so already, you may want to review last week's post (Sorting HTML Data Tables Using the Header Row with PHP) which briefly describes what each part of the code does. If you're still unsure about something, feel free to mention it in the comments section below…since there doesn't seem to be any questions, let's get rolling.

Updating the Code

To sort the columns in both ascending and descending order, we'll need a few more values for the order by flag. Let's start by adding them to the first name heading.

<?php
print '<th scope="col">';
if($_GET['orderBy'] == 'first_name')          { print '<a href="/registrants.php?orderBy=first_name_desc">First Name<img src="arrowUp.gif" alt="Ascending Order (A to Z)" /></a>'; }
elseif($_GET['orderBy'] == 'first_name_desc') { print '<a href="/registrants.php?orderBy=first_name">First Name<img src="arrowDown.gif" alt="Descending Order (Z to A)" /></a>'; }
else                                          { print '<a href="/registrants.php?orderBy=first_name">First Name</a>'; }
print '</th>';
?>

The table heading will now take one of three states:

1. If sorting first name in ascending order, display a link to sort in descending order
2. Else…if sorting first name in descending order, display a link to sort in ascending order
3. Else…display a link to sort by whatever the default order is for that column which will be ascending order for the first name

Note that the first two states contain an image that indicates the direction the column is being sorted. The third doesn't have an image since the data table is sorted by another column. Next we'll modify the other columns so they have the same setup.

<?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 '<a href="/registrants.php?orderBy=first_name_desc">First Name<img src="arrowUp.gif" alt="Ascending Order (A to Z)" /></a>'; }
elseif($_GET['orderBy'] == 'first_name_desc') { print '<a href="/registrants.php?orderBy=first_name">First Name<img src="arrowDown.gif" alt="Descending Order (Z to A)" /></a>'; }
else                                          { print '<a href="/registrants.php?orderBy=first_name">First Name</a>'; }
print '</th>';
print '<th scope="col">';
if($_GET['orderBy'] == 'last_name')           { print '<a href="/registrants.php?orderBy=last_name_desc">Last Name<img src="arrowUp.gif" alt="Ascending Order (A to Z)" /></a>'; }
elseif($_GET['orderBy'] == 'last_name_desc')  { print '<a href="/registrants.php?orderBy=last_name">Last Name<img src="arrowDown.gif" alt="Descending Order (Z to A)" /></a>'; }
else                                          { print '<a href="/registrants.php?orderBy=last_name">Last Name</a>'; }
print '</th>';
print '<th scope="col">';
if($_GET['orderBy'] == 'date')                { print '<a href="/registrants.php?orderBy=date_desc">Date Registered<img src="arrowUp.gif" alt="Ascending Order (Oldest to Newest)" /></a>'; }
elseif($_GET['orderBy'] == 'date_desc')       { print '<a href="/registrants.php?orderBy=date">Date Registered<img src="arrowDown.gif" alt="Descending Order (Newest to Oldest)" /></a>'; }
else                                          { print '<a href="/registrants.php?orderBy=date_desc">Date Registered</a>'; }
print '</th>';
print '</tr>';
print $registrantList;
print '</table>';
?>

We'll also need to modify the code for processing the order by flag.

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

Now when the flag is set to "date", "first_name", or "last_name"; the SQL query sorts the data by the selected column. But if the flag is set to "date_desc", "first_name_desc", or "last_name_desc"; it sorts by the column in descending order. Note that the substr() function is there to get the column name by chopping off the last five characters ("_desc"). The rest of the code is the same as last week.

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 'date':
     case 'first_name':
     case 'last_name':
          $sql_orderBy = $_GET['orderBy'];
          break;
 
     case 'date_desc':
     case 'first_name_desc':
     case 'last_name_desc':
          $sql_orderBy = substr($_GET['orderBy'], 0, -5) . ' DESC';
          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 '<a href="/registrants.php?orderBy=first_name_desc">First Name<img src="arrowUp.gif" alt="Ascending Order (A to Z)" /></a>'; }
elseif($_GET['orderBy'] == 'first_name_desc') { print '<a href="/registrants.php?orderBy=first_name">First Name<img src="arrowDown.gif" alt="Descending Order (Z to A)" /></a>'; }
else                                          { print '<a href="/registrants.php?orderBy=first_name">First Name</a>'; }
print '</th>';
print '<th scope="col">';
if($_GET['orderBy'] == 'last_name')           { print '<a href="/registrants.php?orderBy=last_name_desc">Last Name<img src="arrowUp.gif" alt="Ascending Order (A to Z)" /></a>'; }
elseif($_GET['orderBy'] == 'last_name_desc')  { print '<a href="/registrants.php?orderBy=last_name">Last Name<img src="arrowDown.gif" alt="Descending Order (Z to A)" /></a>'; }
else                                          { print '<a href="/registrants.php?orderBy=last_name">Last Name</a>'; }
print '</th>';
print '<th scope="col">';
if($_GET['orderBy'] == 'date')                { print '<a href="/registrants.php?orderBy=date_desc">Date Registered<img src="arrowUp.gif" alt="Ascending Order (Oldest to Newest)" /></a>'; }
elseif($_GET['orderBy'] == 'date_desc')       { print '<a href="/registrants.php?orderBy=date">Date Registered<img src="arrowDown.gif" alt="Descending Order (Newest to Oldest)" /></a>'; }
else                                          { print '<a href="/registrants.php?orderBy=date_desc">Date Registered</a>'; }
print '</th>';
print '</tr>';
print $registrantList;
print '</table>';
?>

Example Table

With a few style sheet (CSS) declarations and a couple images, here is what our table might look like:

Figure 1. First Name Sorted in Ascending Order

Figure 2. Last Name Sorted in Descending Order