sorting using select box

gowri

Hello everyone,

I want to sort my data table using select box on change.

For example :-

I am having two status "Active","Inactive" or "Both". if i select active i want to see only active status records from server side. I have seen some examples here http://www.datatables.net/release-datatables/examples/api/multi_filter_select.html . But I want only one drop down not for all columns.

my app screenshot is here http://www.diigo.com/item/image/19c1l/d12j.

Please help me to do this

Thanks in advance

Thobias

I did something similar in a recent project, however I was using 2 checkboxes. Here is my code, I hope it helps:

[code]
//Filter for the poll table.
$.fn.dataTableExt.afnFiltering.push(function (oSettings, aData, iDataIndex)
{
//Checkbox indicating if the table should display polls from all users and not just those from the current user.
var showUsers = $("#showAllUsers").prop('checked');

//Checkbox indicating if the table should display expired polls or not.
var showExp = $("#showExpired").prop('checked');

//The expiration date of the poll.
var endDate = new Date(aData[7]);

//Hidden column indicating if the current user is the creator of the poll (1 == true).
var isMe = aData[0];

//Current date.
var now = new Date();

//Both boxes are checked.
if (showUsers && showExp)
{
return true;
}

//Just showExp is checked.
else if (showUsers && endDate > now)
{
return true;
}

//Just showUsers is checked.
else if (isMe == 1 && showExp)
{
return true;
}

//Neither box is checked.
else if (isMe == 1 && endDate > now)
{
return true;
}

return false;
});
[/code]

And here is the code that triggers the filter when checkboxes are clicked:

[code]
//Show all users checkbox is clicked.
$("#showAllUsers").click(function ()
{
table.fnDraw();
});


//Show expired polls checkbox is clicked.
$("#showExpired").click(function ()
{
table.fnDraw();
});

[/code]

MikeS

Can anyone show an example of how I could go about excluding a particular column from having a drop down list of values? I have a situation where the column is long free form text and allowing a drop down list of those values would be unnecessary.

genealogy_guy

I have similar problem to MileS. My table has columns for state, county, town and street. Is it possible to have drop-down list for only 2 columns (state & county).

cdaigle

There is a shortcut to doing this (not really elegant), which involves using the code provided in the link from the original post. (http://www.datatables.net/release-datatables/examples/api/multi_filter_select.html )

The change you want to make is in the following code:
[code]
/* Add a select menu for each TH element in the table footer */
$("tfoot th").each( function ( i ) {
this.innerHTML = fnCreateSelect( oTable.fnGetColumnData(i) );
$('select', this).change( function () {
oTable.fnFilter( $(this).val(), i );
} );
} );
[/code]
All you need to do is put a check for the column(s) you want the select box for (or the other way around, whichever is smaller).
For example, if I take genealogy_guy's table, I might do the following.
[code]
var stateIndex = oTable.fnGetColumnIndex("State"), countryIndex = oTable.fnGetColumnIndex("Country");
$("tfoot th").each( function ( i ) {
if (i == stateIndex || i == countryIndex) {
this.innertHTML = fnCreateSelect( oTable.fnGetColumnData(i));
$('select', this).change(function() {
oTable.fnFilter($(this).val(), i);
} );
}
} );
[/code]
I do something similar, but I do not use the fnGetColumnIndex function (I hardcoded the indices since they never change).

You could also simplify this by replacing the $("tfoot th").each() loop with just a single execution if you only want to do this for one column. (gowri)
i.e.
[code]
var columnIndex = oTable.fnGetColumnIndex("columnName");
$("tfoot th:eq("+columnIndex+")")
.html(fnCreateSelect(oTable.fnGetColumnData(columnIndex)))
.find("select")
.change(function() { oTable.fnFilter($(this).val(), columnIndex); });
[/code]
If you want to put the select above somewhere other than the td, you simply need to change the $("tfoot th....") to reference the object you wish to place the select in.

MikeS, all you have to do is put a check for the column you do not want a select for in the loop and skip it for that column.

Disclaimer: I have not tested this code, it is based off of what I have implemented but may contain errors. It can also likely be optimized, especially for tables with more columns.

Hope this helps someone.

genealogy_guy

[quote]cdaigle said: You could also simplify this by replacing the $("tfoot th").each() loop with just a single execution if you only want to do this for one column. (gowri)

[/quote]

This is very close to what I want. But does the fnGetColumnData function get all possible values that exist for the column in the database table, or only those that have been retrieved? For state, might I end up with a drop-down select containing 49 or less states?

If that were the case, how would I just pre-fill my drop-down list with the 50 states that I know should exist?

cdaigle

If you are using server-side processing then yes, it is possible that not all 50 will show up since fnGetColumnData only applies to the rows that datatables knows about on the client side (what it got from the server that 1 time). To get around this, you can create a function to construct the select box using a predefined array containing all 50 states. The values in the array need to be exactly what will be searched for in the column (You could retrieve these via an ajax call or have them in the JS file at load time).
Let's presume you have one such array called asStates. Using the following function you could get the select you need.
[code]
function fnCreateStateSelect() {
var sHTML = "";
for (var idx = 0; idx < asStates.length; idx++) {
sHTML += ""+asStates[idx]+"";
}
sHTML += "
return sHTML;
}
[/code]
So, instead of calling fnCreateSelect, you call this function instead. It also may be easier just to have the select html created beforehand as a string (rather than looping through an array at load time) and just put that instead of fnCreateStateSelect. (i.e. $("...").html("TexasFlorida...").find("select").onchange(...)).

Hope this helps.

genealogy_guy

[quote]cdaigle said: It also may be easier just to have the select html created beforehand as a string (rather than looping through an array at load time) and just put that instead of fnCreateStateSelect. (i.e. $("...").html("TexasFlorida...").find("select").onchange(...)).

[/quote]

I know I am very new to this but I know that this is exactly what I need. But where do I place this code exactly. At present I have code included for multi-column selects as follows:

[code]
$("tfoot th").each( function ( i ) {
this.innerHTML = fnCreateSelect( oTable.fnGetColumnData(i) );
$('select', this).change( function () {
oTable.fnFilter( $(this).val(), i );
} );
} );
[/code]

I know this code is working on all columns, but I only need a drop-down on 1 (first) column. How do I change it to make it apply to only 1, and what change is required.

[code]
$("...").html("TexasFlorida...").find("select").onchange(...)).
[/code]

The first ... is the name? of the column, I think. The second ... is the other 48 states. The last ... is the action to take on selection of a state, that should be a call to the database to display those addresses in that state.

But exactly where should I place this code, and what should I write in the .onchange function?

cdaigle

I was incorrect when i put .onchange(), that should be .change().

Here is what you need (assuming state is the first column and sStateSelectHTML is the string of html representing the select for the state):

[code]
$("tfoot th:eq(0)").html(sStateSelectHTML).find("select").change(function() { oTable.fnFilter($(this).val(), 0); });
[/code]

Essentially I just took the loop that you had and changed it to apply to just 1 column.

So in your code, replace the first code block from your post with the one I just gave. Provided that the jQuery syntax is right (I think it is), that should work. The oTable.fnFilter will tell dataTables to filter and that should internally trigger an ajax call for new data (passing the filters).

Hope this helps,
Chris.