Preloading dropdowns with all possible (unique) options

StevieDC

I'm sorry I can't link to a test case as I am developing locally.

I would like to use Ajax datatables to display a subset of columns from my table. I would also like to have some dropdown menus above the datatable to filter on columns (more dropdowns than the number of columns being displayed).
So far I have found an example of adding dropdowns under the datatable columns but [1] I need dropdowns for other fields not being displayed and [2] the dropdowns are only being populated with what is actually displayed not all the unique options from the whole MySql table.

Is this possible please?
Thanks, Steve.

kthorngren

Yes its all possible. Take this example as a base to work from.

datatable to filter on columns (more dropdowns than the number of columns being displayed).

You can use columns.visible to hide the columns you don't want to show but want to filter.

[1] I need dropdowns for other fields not being displayed

Same as above.

[2] the dropdowns are only being populated with what is actually displayed

Use the column-selector of {page: 'current'} to iterate only the columns on the page. I updated the linked example to show this:
http://live.datatables.net/zuqewuwu/1/edit

Note the addition of var api = this.api(); as the first line in initComplete. Also changed the column().data() loop to look like this:

api.column(column, {page: 'current'}).data().unique().sort().each( function ( d, j ) {
    console.log(d)
} );

Change the console.log(d) to place the options list where you like. Was too lazy to build out the inputs, etc

You can use the draw event to re-write the select inputs for each table draw (sorting, searching or paging) instead of using initComplete which only runs once.

Kevin

StevieDC

Thank you so much Kevin. Much appreciated.

StevieDC

Hi Kevin,
I didn't explain the 2nd issue very well. I need the menu to display all the values in the mysql table for that column. That is working perfectly unless I have serverSide set to true - which is what I'd prefer. In this case the dataTable is only loading the values it knows about. I guess I need load/pre-load the menu myself with all the possible values (?)

kthorngren

That is working perfectly unless I have serverSide set to true

Forget about the {page: 'current'} stuff

The recommended solution is to have the server script look for the draw parameter and if it equals 1, which is the initial table load, then fetch the unique options from MySql and return an extra object with the options. The second parameter of the initComplete function is the returned JSON. You can access the options object and build your selects.

Kevin

StevieDC

Excellent. Thanks again Kevin.