Populate select list from DB

[queensgambit9]

Checked existing threads but didn't really understand how it works.
I use remote pagination and filtering. Would like to populate select options from db (without the limits from pagination).

I use in main object:
create: function (evt, ui) {
                var grid = this,
                    column;
                $.getJSON("test.php", function (response) {
                    column = grid.getColumn({ dataIndx: 'test2' });
                    // response is ["test1", "test2"]
column.filter.options = response;
                });
}
but select is not populated...

[paramvir]

It's required to clear the cache before setting new options.

Code: [Select]

  column.filter.cache = null
  column.filter.options = response;
  grid.refreshHeader();


[queensgambit9]

Hmm...still don't work...I get no error but select is not populated...

[paramvir]

What's the full definition of column.filter and please share a jsfiddle if possible.

[queensgambit9]

Not sure what I did wrong, but works now.
Thanks for help.

[queensgambit9]

Seems like the select options are treated as strings (sorting) even though column is of datatype "integer". How can I set the correct datatype/sorting?
Example response from PHP which is loaded as options into select:

Code: [Select]

[{
"test": "1"
},
{
"test": "11"
},
{
"test": "2"
}]

[paramvir]

options in select list are not sorted or sortable, they are displayed as such.

If you need to display them in sorting order, then you have to sort the options in remote script or sort them with js array sort method just before assigning to column.filter.options.

[queensgambit9]

What would be a good approach to get all filter options and then populate the selects once?

Currently I use:

Code: [Select]

var arrCol = ["col1","col2"];
              var grid = this, column;
               
                arrCol.forEach(function (value) {

                $.getJSON("/php/test.php", "col=" + value, function (response) {
                    column = grid.getColumn({ dataIndx: value });
                    column.filter.cache = null;
                    column.filter.options = response;
                    grid.refreshHeader();                   
                });   
                });
But that will make a request for each item...I would like to send all items (cols) and get a total repsonse and then populate selects...

[paramvir]

Serialise the col array to string while sending to server

and get response as json object.
{col1: options1, col2: options2, ... }

[queensgambit9]

Thanks.
Do you have a short example of this? (JS, PHP)

Thanks in advance.

[queensgambit9]

Request:

Code: [Select]

data.php?col=col1,col2
Using PHP/MySQL, how do I get the distinct values for each column, output the response correctly and then assign the values to correct column in grid?

Thanks.

[paramvir]

Request:

Code: [Select]

data.php?di=["country", "city"]

"select distinct" or "group by" query can be used to get unique values of a column from db and serialize the resultset to json.

Code: [Select]

  country: [{"country": "argentina"}, ...], city: [{city: ''}, ... ]

json response from remote script can be assigned to column.filter.options, when it's received as ajax response.

Code: [Select]

grid.getColumn({ dataIndx: 'country' }).filter.options = response.country;
grid.getColumn({ dataIndx: 'city' }).filter.options = response.city;


[queensgambit9]

So in PHP loop through the array to build up the response with distinct queries...?

[queensgambit9]

Would it be possible to get a short example of this?...not sure how to approach it...
Perhaps it could be useful to have it on the demo section aswell?
Thanks in advance.

[queensgambit9]

Solved.