Using jQuery To Manipulate and Filter Data

Jun 10th in JavaScript & AJAX by Noah Hendrix

When a webpage is designed to display large tables of data, a great amount of consideration should be dedicated to allowing the user to sort through the data in a structured manner. In this article, I will go over four techniques: hover effects, zebra rows, filtering, and sorting.

Author: Noah Hendrix

Noah is a Computer Science student at the University of Kansas. His interests include web development in PHP/MySQL and Ruby on Rails. He obsessively follows Tech Startups and has been known to have his own entrepreneurial ambitions. His development environment of choice is Mac OSX using a combination of TextMate and Coda. Find out more about him on twitter (@noahhendrix) or facebook.com/noahhendrix.

Setting Up The Tables

There are some important notes we must address before looking at our Javascript code. The HTML table markup will be like any other table you may have created, except we require two tags that many people omit. The head section of the table must be wrapped in <thead></thead>. The body of the table, where all the data we want to display is kept, must be wrapped in <tbody></tbody>. This little caveat will make it easier for us to distinguish between the data and table headers.

<table cellpadding="1" cellspacing="1" id="resultTable">
  <thead>
    <tr>
      <th>First Name</th>
      <th>Last Name</th>
      <th>City</th>
      <th>State</th>
    </tr>
  </thead>
  <tbody>
    <tr>
      <td>Mannix</td>
      <td>Bolton</td>
    </tr>
    ...
  </tbody>
</table>
  

Zebra Rows

Zebra Rows are a very common data organization technique that are both easy to implement and have a powerful impact. Zebra Rows in essence is alternating the style of odd and even rows to make the data easier to read horizontally. This is highly important with multi-column data so that users can look at one column and easily read the associated data on the same row under other headers. In the examples that I will be using through out this tutorial, I have a list of people with four properties: first name, last name, city, and state. Notice how I have the row colors alternating as well as font color to maximize the effect.

Now on to the actual Zebra Rows. The first place to start is an external CSS file linked to the document. The first element to target is the table.

table {
  background-color: white;
  width: 100%;
}
    

This is fairly trivial; we are telling the table background to be white, and to stretch to 100% of the parent element's width. Next we will target the cell elements <td>. Now this might seem odd to some -- why would we target the cells, and not the entire row? Well it turns out that, in terms of cross-browser adoption, it is more effective to target cells when applying background styles.

tbody td {
  background-color: white;
}
tbody td.odd {
  background-color: #666;
  color: white;
}
    

Here we are setting up a class for the "odd" table rows that sets an alternate background-color and font-color. We also set a default style for all td elements which will inherently apply to the "even" rows. This is all the CSS that is required. I told you it was simple! Now let's look at the jQuery code. The power of jQuery selectors makes this just as easy as the CSS code. All we need to do is target the cells and use the addClassName function.

$(document).ready(function() {
  zebraRows('tbody tr:odd td', 'odd');
});


//used to apply alternating row styles
function zebraRows(selector, className)
{
  $(selector).removeClass(className).addClass(className);
}
    

This code, while short, has a few gotchas to consider. First off notice how we abstracted the implementation to a function; this is ideal because if we change the data in the table asynchronously, without a page refresh, then we will want to assure the rows are still alternating style. For the same reason we also invoke the removeClass function so that if a row is initially odd, but becomes even, we are assured that the odd class definition does not remain. This may seem confusing right now, but when we look at filtering later on this will become more clear. For the sake of reusable code we also require that the selector and class name are passed to the function -- so it can be used in projects that have different class names or selector requirements (i.e. a site that has multiple tables only one of which you wish to target). If you review the ready() function, a jQuery function executed when the page is finished loading, you'll see our call to zebraRows(). Here is where we pass in the selector and class name. The selector uses a special jQuery syntax :odd, which will find all odd rows. Then we look for all the child elements of the row that are cells. This code is fairly simple for anyone who has used jQuery previously, but the simplicity should make the code fairly readable to anyone.

Note: While using jQuery to apply alternating row colors is a simple solution, it is not degradable if a user has JavaScript disabled. I would recommend applying the odd class on the server either in PHP code or the static HTML, though this is beyond the scope of this article.

Hover Effect

A really nice effect for users is to highlight the row they are currently hovering upon. This is a great way to single out specific data that they might be interested in. This is dead simple to implement using jQuery, but first a little CSS.

...
td.hovered {
  background-color: lightblue;
  color: #666;
}
...
    

This is all the CSS we need, basically when we hover over a row, we want to make all the cells in that row have a light blue background and a grey font color. The jQuery to make this happen is just as simple.

...
$('tbody tr').hover(function(){
  $(this).find('td').addClass('hovered');
}, function(){
  $(this).find('td').removeClass('hovered');
});
...
    

We are making use of the hover() function in the jQuery library. It takes two arguments that are functions we want executed when the mouse hovers over and when the mouse moves off the element, respectively. When they hover over a row we want to find all the cells within the row and add the hovered class to them. When the mouse leaves the element we want to then remove that class. This is all we have to do to get the hover effect, go try it out!

Filtering Data

Now the meaty stuff - actually manipulating the data that is shown. If a website calls for many records of data to be displayed, in my example 1000 rows, then it is more than appropriate to offer the user a way to sift through the data. One particularly effective way that has sprung up on the web in recent years as part of the Web2.0/AJAX movement is filtering. This is also something that Apple pushes heavily in there applications such as iTunes. The goal for us is to allow the user to type a search query into a standard text input and live filter the table rows below only showing the ones that contain matching text. This is arguably more advanced then the alternating row styles, but in all reality requires minimal code, due to jQuery's built-in functionality.

First we will write a generic function that takes a selector and a string of text. This function will then search all elements matching that selector looking for the string. If it finds the string, it will show the element and apply a class name of visible to the element, otherwise it hide the element. Why are we applying the class of visible? Well once the items are sorted we will want to run the zebraRows function again, but we need to tell jQuery to ignore the hidden rows, and the best way I have found to do that is apply a class of visible.

The actual searching is done by the JavaScript function, aptly named, search(). Though due to the way the DOM works, if we don't employ the jQuery function, text(), the box will also look at any HTML tags that happen to be in the table row, such as <td>. We will employ a little more functionality by not just searching for the exact string the user has typed, but rather if any of the words in the query are in a row. This is ideal because it allows for "lazy searching", the user isn't required to remember an exact string but rather just parts of it. The search() function takes a regular expression as its parameter, and so we must strip all white space from the beginning and end of our query and put "|" characters in between each word to achieve the OR functionality we desire. Regular expressions are a very complicated topic, and so you will have to take my code at face value or I can refer you to the Regular Expressions for Dummies video series on the ThemeForest blog.

//filter results based on query
function filter(selector, query) {
  query	=	$.trim(query); //trim white space
  query = query.replace(/ /gi, '|'); //add OR for regex query

  $(selector).each(function() {
    ($(this).text().search(new RegExp(query, "i")) < 0) ? $(this).hide().removeClass('visible') : $(this).show().addClass('visible');
  });
}
    

The sixth line is where the magic happens, and probably requires a bit of explanation. Starting at line 5, we are telling the code to loop through all the elements that match the selector, i.e. the rows, and then we want to execute the code on line 6 using each one. Line 6 is a bit complicated if you are new to programming, but it is fairly easy to grasp if we split it up. Think of everything before the question mark as being a question, if the answer to that question is true then execute the code to the left of the colon, but after the question mark. If the answer is false then execute the code after the colon. This is essentially an if statement but in a more concise form known as a ternary operator, and would be no different than writing:

...
  if ($(this).text().search(new RegExp(query, "i")) < 0) {
    $(this).hide().removeClass('visible')
  } else {
   $(this).show().addClass('visible'); 
  }
...
    

The reason we ask if search() returns "< 0" is because it returns the position in the string where query is, and -1 if nothing is matched. Because -1 is always less than zero, we test that condition. In theory there is nothing wrong with checking if it returns (==) -1, but it in practice it is safer to just assure it is less than zero.

Alright now that we have a complete filter function, let's use jQuery events to hook it up to the input. To achieve the live effect we desire the event we want to watch for is when user releases a key while they are focused on the text box, known as keyup in JavaScript. It is important that we set the ID attribute of the input so we can target it using jQuery. Back in our ready function we need to add code after our call to zebraRows().

      <label for="filter">Filter</label>
      <input type="text" name="filter" value="" id="filter" />
    

And the jQuery code:

...
  //default each row to visible
  $('tbody tr').addClass('visible');
  
  $('#filter').keyup(function(event) {
    //if esc is pressed or nothing is entered
    if (event.keyCode == 27 || $(this).val() == '') {
      //if esc is pressed we want to clear the value of search box
      $(this).val('');
			
      //we want each row to be visible because if nothing
      //is entered then all rows are matched.
      $('tbody tr').removeClass('visible').show().addClass('visible');
    }

    //if there is text, lets filter
    else {
      filter('tbody tr', $(this).val());
    }

    //reapply zebra rows
    $('.visible td').removeClass('odd');
    zebraRows('.visible:odd td', 'odd');
...
    

This code is by far the most complex we have seen thus far so we will step through it line by line.

1. Starting at the addClass('visible') line we are adding a class of visible to each row, because by default they are all visible.
2. The next line is your standard selector, which in my case is targeting my filter text box and says each time a key is released to execute the following function. Notice that we pass in a parameter called event which has various information about what the user just did, like the key they pressed.
3. Consequently, the next line of code uses that event parameter, we have an if statement that is checking if the user pressed the esc key. It is important to note that each key is mapped to a number and that is how our code can determine which key the user pressed. This is a nice feature so that users can easily cancel the filter and see all the data again. Many applications with filter boxes utilize this sort of feature, and we want to be sure our application stays inline with what is expected.
4. In this same if statement we are also taking care of the special case when the value of the filter box is empty (they just hit backspace to remove all characters). In this case we want the user to see all the rows which seems obvious, but we have to explicitly provide for this because the filter function we wrote earlier would look for a row that has no contents and we will hide all the rows that have any content, the exact opposite of what we want!
5. If either of these conditions are met we want to set the value of the filter box to blank if they pressed esc, it is also executed if the value is blank which doesn't really matter to us.
6. Next we show all the rows as we wanted to and add a class of visible to all of them. Again we are using the safe practice of first removing any lingering visible class declarations to avoid double setting it. If the value of the filter box is neither empty and the user didn't press escape we want to actually filter the rows.
7. So after the else statement we call our filter function from earlier providing the rows in our table body to query against.
8. Finally after we have hidden and shown the appropriate rows, we want to reapply zebraRows to the remaining visible rows. First we remove any lingering odd class declarations to take care of the cases where a row was odd and becomes even. The call to zebraRows is the exact same as the first one on page load, except we only care about the ones that are currently visible and odd.

Note: A good note might be to use CSS to hide the filter box, and right above the keyup call to show it, so users with JavaScript disabled are not confused when they attempt to sort the data, this would look like:

...
#filter { display: none; }
...
    

...
$('#filter').show();
...
    

Wow that was a lot of code, feel free to take a tea/coffee break before we move on to sorting...

Column Sorting

Alright all ready? Good, lets go!

As the final task, we are going to allow sorting the table by any of the column headers. This is very standard practice that users anticipate known as click to sort. When the user clicks one of the headers we want to sort the table ascending and if they click again we want to sort descending by that column. This code is quite advanced and not for the faint at heart. The initial concept came from Learning jQuery 1.3. I have re-engineered it to better fit our needs of simplicity though, however if you desire more fine grain control I will refer you to chapter 7 of the book where tables and jQuery are discussed in great detail.

Before we really dive into the actual code it is important that we discuss the concept of how we plan to tackle this problem. We will use JavaScript's internal sort() method that is designed to take an array and sort it using a custom function supplied by the code. In our case we simply want to sort alphabetically and numerically, so we will just compare the two items it supplies and return which order the two should go in based on that design. Because we want to sort both ascending and descending, we will use a CSS class declaration to see what the current state of the sort by that column is and reverse it if necessary. Once we have our array in order we will use the order to re-insert the rows into the table one-by-one. This sounds like a lot, but because of how blazing fast JavaScript is, it will be very seamless to the user. All of this will be tied to the click event of the column headers in the table.

As per usual, let's get the CSS code out of the way, as it is the most straightforward.

th.sortable {
	color: #666;
	cursor: pointer;
	text-decoration: underline;
}
th.sortable:hover { color: black; }
th.sorted-asc, th.sorted-desc  { color: black; }
  

All of our sortable headers will have a class of sortable, and the hover modifier in CSS makes it emulate a hyperlink for users. We also are taking advantage of the CSS class we mentioned about sorted-asc and sorted-desc so that we can show the user the current column that is sorting the table. I didn't include it but this would be a good place to put background images of arrows pointing up and down as a further visual cue to the user. Now we move on the JavaScript code and the complexity of sorting, thankfully made easier with jQuery. The code below belongs in the ready() function we started way back at the beginning. Placing this right above the end of the function is best.

//grab all header rows
$('thead th').each(function(column) {
  $(this).addClass('sortable').click(function(){
    var findSortKey = function($cell) {
      return $cell.find('.sort-key').text().toUpperCase() + ' ' + $cell.text().toUpperCase();
    };
    var sortDirection = $(this).is('.sorted-asc') ? -1 : 1;

    //step back up the tree and get the rows with data
    //for sorting
    var $rows = $(this).parent().parent().parent().find('tbody tr').get();

    //loop through all the rows and find 
    $.each($rows, function(index, row) {
      row.sortKey = findSortKey($(row).children('td').eq(column));
    });

    //compare and sort the rows alphabetically
    $rows.sort(function(a, b) {
    	if (a.sortKey < b.sortKey) return -sortDirection;
    	if (a.sortKey > b.sortKey) return sortDirection;
    	return 0;
    });

    //add the rows in the correct order to the bottom of the table
    $.each($rows, function(index, row) {
    	$('tbody').append(row);
    	row.sortKey = null;
    });

    //identify the column sort order
    $('th').removeClass('sorted-asc sorted-desc');
    var $sortHead = $('th').filter(':nth-child(' + (column + 1) + ')');
    sortDirection == 1 ? $sortHead.addClass('sorted-asc') : $sortHead.addClass('sorted-desc');

    //identify the column to be sorted by
    $('td').removeClass('sorted')
    			.filter(':nth-child(' + (column + 1) + ')')
    			.addClass('sorted');

    $('.visible td').removeClass('odd');
    zebraRows('.visible:even td', 'odd');
  });
});
  

Woo, that is a lot of code. Let's break that down into sizable bits. The first bit of code is grabbing all the headers and looping through them. The first thing it does is add a class of of sortable, and starts to the click bind.

...
//grab all header rows
$('thead th').each(function(column) {
  $(this).addClass('sortable').click(function(){
...
  

Note that this can easily be changed to allow only certain columns to be sortable by removing the addClass() call and changing the selector from 'thead th' to something like 'thead th.sortable'. Of course this requires you to manually specify which of your columns are sortable by adding class="sortable" to the appropriate headers in the HTML code.

The next bit of code is a function declaration tied to a variable. This might seem a bit weird to those unfamiliar to programming, but it is common practice. This allows us to easily reference the function specifically in the context of the header we are working on. That explanation probably is a bit confusing, but the precise reasoning kind of oversteps the scope of this article. The point of the findSortKey function is determine which column we are sorting by, we can do this because we know the element they clicked on is the same index in the table for all the columns we will compare. For instance if they click the third header we want to look at the third column of each row to compare what order to place the rows. After we declare this function we then determine the sort order, ascending or descending. This is done by looking for the class name of 'sorted-asc' in the table header if it is there we know that it is currently sorted as ascending and we need to make descending, otherwise use the default of ascending. This takes care of the case where it is descending and we need to make it ascending again. This bit of code returns 1 or -1, we will explain why later.

...
var findSortKey = function($cell) {
  return $cell.find('.sort-key').text().toUpperCase() + ' ' + $cell.text().toUpperCase();
};
var sortDirection = $(this).is('.sorted-asc') ? -1 : 1;
...
  

Now we want to get that specific column from each row and put it in an array, this is done using the jQuery method of get() which takes the rows and puts them in an array which the sort() function can understand. Because the current selector was the table head we have to step back up the DOM tree 3 places to find table>tbody>tr>td. Seems a little complex, but in reality it is simple. After that we loop through each of the rows we just found and find the column we want to using in sorting. This is done by checking if its index (the number of places starting at 0 from the first column in the table) is equal to the index of the clicked header. This is then passed into the findSortKey function so we can then set a custom attribute called sortKey that contains the column header we are sorting by and the text of the current column we are looking both of which are set to uppercase so the sort is case-insensitive. This is a way we streamline the sorting so that we make it more responsive for large amounts of data.

...
    //for sorting
    var $rows = $(this).parent().parent().parent().find('tbody tr').get();

    //loop through all the rows and find 
    $.each($rows, function(index, row) {
      row.sortKey = findSortKey($(row).children('td').eq(column));
    });
...
  

Next comes the actual sort() function that I have been going on about. This is called on the array of rows we created using get(). The only parameter we pass is the function we want to determine the sorting. That function receives two attributes to compare and returns 1 if the first is greater, -1 if the second is great, and 0 if they are equal. This is where the sortDirection variable comes into play because the way it works is that we set 1 or -1 to it and then multiply either the 1 or -1 the function should return by sortDirection, achieving the ascending/descending affect we desire.

...
//compare and sort the rows alphabetically
$rows.sort(function(a, b) {
	if (a.sortKey < b.sortKey) return -sortDirection;
	if (a.sortKey > b.sortKey) return sortDirection;
	return 0;
});
...
  

The next bit of code simply adds each row from the now sorted array back into the DOM structure. This is done with the append function which is nice because it does not copy the row and place it at the end it actually removes it from the current place in the DOM and places where we tell it, in this case at the end of the table. After it has done this for each element in the array it will have moved each row to its new place. Also to do a bit of cleanup we remove the sortKey attribute we set earlier.

...
//add the rows in the correct order to the bottom of the table
$.each($rows, function(index, row) {
	$('tbody').append(row);
	row.sortKey = null;
});
...
  

We are now moving in to the cleanup stage of our function since all the heavy lifting has been done. Next we grab all the cells in the table body, remove any lingering sorted attributes in the class declarations, and then filter out all but the columns that are the same index as our sorted header and apply the 'sorted' class to them. This is nice for CSS targeting if for instance we wanted to make the column we sort by a different color we could declare this CSS:

...
.sorted { background-color: green; }
...
  

The final thing we do is remove any 'odd' CSS declarations and reapply the Zebra Rows just like we did in the filter part.

...
$('.visible td').removeClass('odd');
zebraRows('.visible:even td', 'odd');
...
  

That is how we do very simple sorting. It is important to note that this will only sort items alphabetically or numerically and does not work with dates or currency for instance. That requires more specialized handling that is beyond our goals of simple table manipulation.

Wrap Up

In this article, we learned how to roll our own table manipulation code using jQuery. This is very convenient for both the user and us. The user gets the expected controls for sorting and filtering the data and we have code that is both small and easy to understand. Because we wrote this ourselves, we can now extend it in our own ways. Our method is great for simple manipulation, but if you require the kitchen sink, I recommend taking a look at the Data Tables plugin for jQuery. I'd love to answer any questions in the comments or on Twitter (@noahendrix). Thanks for reading!

• Follow us on Twitter, or subscribe to the NETTUTS RSS Feed for more daily web development tuts and articles.