Copyright © 2012 Tom Coote. All Rights Reserved. Powered by WordPress themed by bavotasan.com.
I built this jQuery plugin for a quick way of allowing table columns to be filtered by the user. It will add text box’s to the top of each column in a table, by typing into the text box’s you can narrow the number of rows in the table down to those that match your search text. It allows for wild cards and a NOT operator (ie show me everything that doesn’t match my search text).
$('#filterTable').columnFilters();
To use this script you need to download jQuery (included in the download below). Each table you apply the script to must be well formed; containing atleast a tbody and a thead. The script doesn’t yet compensate for any cells that have a colspan greater than 1. Below are a list of the variables that can be changed and there defaults. Please also check out the demo!
- wildCard: (default ‘*’) Allow you to search for any characters. eg ‘*m’ or ‘*12*3′
- notCharacter: (default ‘!’) Allow you to NOT the results. eg ‘!T’ would return all rows that don’t start with ‘T’
- caseSensitive: (default ‘false’) Are results case sensitive.
- minSearchCharacters: (default ‘1′) The minimum amount of characters that each search string must contain before the filter is applied.
- excludeColumns: (default ‘[]‘) Which columns, if any, do not require a filter. This array is zero indexed. eg ‘[0,1]‘
- alternateRowClassNames: (default ‘[]‘) An array of alternating class names for each row in order to keep odd and even row styling on tables. Maximum of two items. eg ['rowa','rowb']
- underline (default ‘false’) Set to true if the script is to underline the search text whilst filtering is in progress… good for tables with lots of rows where the filter may take a second. (not in Opera)
One last thing, the row created containing the text box’s is given a class name of ‘filterColumns’ so that you may style that row using CSS.
Download the source
Visit plugin page at jQuery
Demo Page
Tested to work in the following browsers;
- IE 7 & 6
- FireFox 2.0.0.14
- Opera 9.24
- Safari for windows
FAQ
I get asked this question way to often so I thought I’d post the answer on this page. “How do I change the size of the filter input boxes?”. The answer is to use CSS, the plugin offers no styling as this should be done in your style sheet. The filter boxes are given the class name of “_filterText” and the whole table row is given a class name of “filterColumns”. Set them to 100% wide if you want them to be the same width as your columns are without filter boxes.
June 1, 2010 at 1:39 am
Great post, thank you so much for sharing.
June 9, 2010 at 11:44 am
Awesome!
June 11, 2010 at 4:28 pm
How do you change the width of the Input tags?
June 11, 2010 at 4:38 pm
You can change the width of inputs using CSS. The best way is to set them to 100% because then they don’t push the columns wider. The inputs are given class names that you can apply styles to but I can’t remember what they are off the top of my head. Use firefug to find out what the class names are.
June 11, 2010 at 5:08 pm
I’m not sure that works. I found that all the inputs have the class name “_filterText”. My css is below.
._filterText {
width:100%;
}
This doesn’t work… Any suggestions?
June 11, 2010 at 5:13 pm
Yeah that’s it. That is how I style them when I use the plugin. If you are able to make your work available on the net then I could take a look for you.
June 24, 2010 at 3:01 am
Hi,
Great plugin. Works great. Especially with small data sets.
But for data sets of 1500 to 2000, the grid takes a whole 3:30 minutes to 4 minutes. And if you do a very general filter on the column like just type “a” or “as” .. something like that .. ie 6 does not respond. So do you have any suggestions on improving the performance to use it for large data sets in IE 6.
Thanks.
June 24, 2010 at 8:14 am
I don’t use it on tables that have above 300 – 500 rows because of this. I would recommend a different solution for larger grids. I have a custom solution that filters data on the server then sends back the new table rows.
July 1, 2010 at 3:11 pm
Hi,
thank you for your reply, i am interested in your custom solution. how can i know more details about it?
July 5, 2010 at 8:36 am
Hi. I’m interested in your custom solution too. I have a table of about 1500 items, but like your filtering idea. It’s just too slow, though.
July 5, 2010 at 12:59 pm
Hi, it’s not very complicated, however a solution on the server will be different per server technology that you use. I basically take all the text input into the filter boxes when the user is typing and send them to the server as a JSON string via AJAX using jQuery. Then the server parses the filter text and rebuilds rows for the table, usually from a cached set of data. When the server returns the result I replace the table rows in the table with the new ones. It isn’t possibly to detail the code in these comments and as I’ve said the server side part would differ depending on technology. Best way is to try it.
September 20, 2010 at 6:43 am
Hi ,
I’m using column filter for the table , which is working only when preceded by the wild char (*) before the search string.
September 20, 2010 at 12:39 pm
If using a wild card then I think the search from the start of the line. Try commenting out line 105 in the plugin js file and see if that resolves it. Has been a long time since I built that though so I’m not sure what the consequences will be.
October 20, 2010 at 6:07 am
This is awesome.I am using this in my ColdFusion Project.
October 22, 2010 at 2:48 pm
I would very much like to use this on one of my client’s websites with Cubecart, the only problem is that I want to use it to filter over 1000 items… aside from your “server-side” JSON solution, is there anything else available that would be worth looking into? I could only find this on Google. =(
October 22, 2010 at 2:54 pm
I don’t know of anything. To be honest, once you start getting into loops of JavaScript in the thousands then you’re going to get a performance hit no matter what. That’s why I move the task to a server for that much data.
October 25, 2010 at 8:54 am
It’s a really powerful and useful script, thanks a lot for making it.
I just have a few questions:
1) How can I make it use wildcards automatically? For example, if a table column contians lots of data like “COLOUR ITEM”, one could simply search for “ITEM” without searching for “* ITEM” or “*ITEM”, in order to get the same results…?
2) I want to exclude column 0 and place the word “filter :” into it… so that it is obvious to the people who use it, what purpose those empty input boxes serve… how do I go about this?
Thx.
October 25, 2010 at 9:20 am
Hi Jo, have a look at the demo page from the link in the post above. There is a property called excludeColumns which you can use to not put a filter box on certain table columns. There is also a property called wildCard which if you set to an empty string will get the results you’re after.
October 25, 2010 at 9:54 am
Thanks Tom, I got the Wildcard sorted, that’s just absolutely brilliant, but what I meant with regards to excluding the column, I actually want to be able to use it… so what would be a text filter input is actually just some text…
col0head | col1head | col2head
customtext | [col2input] | [col3input]
datacol1 | datacol2 | datacol3
I have excluded col0 in this example, and I want to replace the empty cell with “customtext” as in the example above…
October 25, 2010 at 10:37 am
You just need to select that cell once the column filters plugin has finished and populate it with text. i.e. $(‘#tableID tr.filterColumns td:first’).html(‘custom text’);
October 25, 2010 at 10:54 am
Excellent, cheers Tom. Absolutely perfect. Really good job on this.
October 29, 2010 at 7:50 am
Hi, Tom! Good work! Very good and useful script!
Tom, is that possible to do a drop-down list that sorted table by selecting one of the options?
I want to do a data row drop-down list BEFORE the table who sorted table, like your text boxes and I don’t know how to do it.
November 10, 2010 at 1:27 pm
I am needing to readjust the column size due to the text boxes in the header. When I applied your tool, it expanded my gridview into a scrolling page which is what I did not need to happen. Do you have a work around for this issue? Thanks again for the super cool tool.
Raymond
November 10, 2010 at 4:48 pm
Hi Raymond, use CSS to style elements. See the FAQ section on this page, above these comments.
December 24, 2010 at 5:03 pm
I am trying to figure out how to get the rows to filter in a specific way. If I have information in one box, I need the filter to keep that information and apply it to the rest of the grid. I am using a gridview on a databind. My problem is that when information is being entered in one column, the data filters but when I add information into an additional column, all rows are refiltered. Any ideas? Thanks!
Raymond
May 6, 2011 at 7:33 pm
I have a column and I wish to use a drop down menu instead of a text field.
can this be accomplished?
great plugin!
May 6, 2011 at 7:40 pm
It certainly can be accomplished. This plugin doesn’t have any functionality for that though. There may be others around on the net that do. Alternatively you could use my plugin as a basis and enhance it to do what you need to.
May 13, 2011 at 10:35 pm
You did a wonderful job. I am really impressed. Since I am not a UI developer, the css has been a little unique. Is there a way to set the column width based on the header instead of the column data?
May 14, 2011 at 9:26 am
The plugin gives the filter column fee classes which you can use to style. see the faq on this page for some info on this question.
June 9, 2011 at 5:54 pm
Hi Tom! Great plugin. Is it possible to exclude certain rows from being searched? Your has issues with this other Jquery plugin JExpand
http://www.jankoatwarpspeed.com/post/2009/07/20/Expand-table-rows-with-jQuery-jExpand-plugin.aspx
it searches the hidden fields and expands them
Thanks!
June 9, 2011 at 6:00 pm
I see what you’re trying to do. You’ll have to modify my plugin to get this working. If you find the bit that loops through each table row then it’ll be pretty easy to exclude certain rows. Perhaps give the rows to exclude a class name that you can use in my plugin.
June 9, 2011 at 6:13 pm
I tried that out Tom, no luck:
I trid this on line 108:
$(‘tbody:first tr not(.nosearch) ‘, obj).each(…..
Any other clues?
Thanks
June 9, 2011 at 6:16 pm
You have a bug on line 136, I think it should read:
$(this).css(‘text-decoration’,'underline’); instead of
$(this).css(‘text-decoration’,”);
June 9, 2011 at 6:20 pm
At a guess id say line 139 is removing an underline not adding one but I’ve not looked at the code for ages so can’t say for sure.
The selector you are using isn’t right. It should be;
$(‘tbody:first tr:not(.nosearch)’, obj).each
June 9, 2011 at 6:27 pm
You are the man! It works now. Great work Tom
August 9, 2011 at 12:28 pm
Any reason why you don’t trim() the text of the cell?
August 9, 2011 at 2:18 pm
Not that I can think of. It would probably work better with a trim if a wild card is being used because of the way the regular expression is constructed.
August 29, 2011 at 3:55 pm
This looks great, however, I am wondering if anyone has solved the problem with Flexigrid not rendering correctly in IE. You can see the problem if you go to:
http://glolar.com/loginsite/main.php
Log in with username = user02, password = password
In firefox, everything looks great. In IE7, 8 or 9, the toolbar across the top does not render. Also, if you click the Quick Search icon in the lower left of the flexigrid, the search parameters do not render in IE either.
Thanks.
November 24, 2011 at 10:46 am
Greate plugin, but how i can find sum of column from filtered values?
November 24, 2011 at 12:26 pm
Thanks. Not quite sure what you mean?
November 28, 2011 at 5:24 am
I mean, that when one column in my table conteins numeric values and i want find sum of this values every time when change filter.
November 28, 2011 at 9:26 am
Ah right. You would need to implement a callback function into the plugin. Put it as one of the options then call it after the filter has run. Then you can run the calculation in that function as it’ll be called after the rows have been updated. It would be something like.
var total = 0;
$(‘table tbody tr:visible’).each(function() {
total += parseInt($(‘td:eq(1)’, this).text(), 10);
});
November 29, 2011 at 11:17 am
Thank’s working greate!
November 25, 2011 at 9:31 am
Hello Tom,
great plugin. I am a newbie in javascript, could help me out in adding the “” operators? This would be super cool to filter data columns (i.e. the customers that bough more than 1000).
November 28, 2011 at 9:31 am
You would need to implement that in the plugin. In the function named runFilters you would need to test for those characters in the sFilterTxt variable. For example;
var gt = /^ *>/ig.test(sFilterTxt);
Then if the user wants greater than a number you’d have to change the test for a cell match to parsing the value to an integer and then running the comparison.
November 29, 2011 at 12:58 pm
Tom, good time of day!
I apologize for my English.
Could you please describe in detail how to add code to find the sum of the column of the filtered values
I am the second day of learning Javascript, and, unfortunately, was unable to practice your response to Nikolay
I would be very grateful for your response.
December 22, 2011 at 1:30 pm
Если кому интересно
$(document).ready(function() {
$(‘table#filterTable1′).columnFilters({}, function() {
var sum = 0;
$(‘#filterTable1 td.sss:visible’).each(function() {
sum += parseInt($(this).html());
});
$(‘#filterTable1 td.sum’).html(sum);
});
});
December 22, 2011 at 1:31 pm
Работает прекрасно!
Спасибо.
November 29, 2011 at 4:20 pm
Hi Tom,
I love the filters example but what happens when you want to search inside a column which has html in it.
For example, how do you get the filter to search on a value say 123 when the cell itself contains the value: 123?
Any ideas?
Thanks in advance,
Michael