Sorting HTML Data Tables Using the Header Row with PHP

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 processing the registrations easier, 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 conference_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 conference_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

12 Comments

  • #12 Iftekhar Bhuiyan on 05.13.16 at 2:41 pm

    Great snippet. It was really helpful for my upcoming project. Thanks.

  • #11 chris on 08.10.14 at 6:01 pm

    Well put together lesson. Many thanks for your time and efforts.

  • #10 Patrick Nichols on 06.06.14 at 10:35 pm

    @Piyush – Sorry, I'm not sure what you mean.

  • #9 Piyush Jadhav on 06.03.14 at 12:50 pm

    Hello Sir,
    i am building a website in wordpress and using php to add dynamic content. your help would be very appreciated if i could know the procedure to retreive html table row data(or complete row for further processing) after i check a checkbox that is present as the last column of the table.

  • #8 Patrick Nichols on 01.10.14 at 5:14 am

    @Student – Sorry for the delayed response.

    Since mysql_ functions have been deprecated and will eventually be removed, I have switched to MySQLi. The code I use to connect with the database is discussed here:
    End PHP Scripts Gracefully After a Failed Database Connection

    If you import that connection script, the example for sorting tables could be modified to use MySQLi by changing the following lines:
    $result = mysql_query($sql);
    while($row = mysql_fetch_array($result)) {

    They would be changed to
    $result = $connect->dbsObject->query($sql);
    while($row = $result->fetch_array()) {

    Or, if you prefer, you could use mysql_connect(). More information on the function (including examples) can be found here:
    http://www.php.net/function.mysql-connect

  • #7 Student on 01.06.14 at 11:07 pm

    How did you connect to the database e.g. (mysql_connect("127.0.0.1","root","","customerdb") or die (mysql_error());)

  • #6 Patrick Nichols on 05.03.13 at 8:30 pm

    @Durga – The rest of the code, which sorts both in ascending and descending, can be found here:
    Sorting HTML Data Tables Part 2: Dynamically Sort in Ascending and Descending Order

    If you're looking for something else, please let me know.

  • #5 Durga Prasanna Acharya on 05.02.13 at 4:13 am

    sir/madam ..
    i want the complete code of sorting .

    thank you..

  • #4 Patrick Nichols on 04.24.13 at 6:46 am

    Sorry, I'm not sure I follow. MySQL is used for the data and sorting.

  • #3 simple php sortable table on 04.23.13 at 4:25 am

    the data can be pulled from mysql too,and thus, you just have to enter mysql data correctly.

  • #2 Patrick Nichols on 08.20.12 at 4:40 pm

    @joe – Sorry about that…I forgot to add the Related Posts section after writing part two. The code for clicking the header to sort in ascending and descending order can be found here:
    Sorting HTML Data Tables Part 2: Dynamically Sort in Ascending and Descending Order

  • #1 joe on 08.20.12 at 3:04 pm

    finally some code I can actually use and get working! thank you! can you tell us a way where after clicking a header sorts the data ascending where clicking that header again directly after would sort the data descending?

Leave a Comment