ParamQuery

General Category => Help for ParamQuery Pro => Topic started by: queensgambit9 on March 22, 2018, 08:59:52 pm

Title: Populate select list from DB
Post by: queensgambit9 on March 22, 2018, 08:59:52 pm
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...
Title: Re: Populate select list from DB
Post by: paramvir on March 22, 2018, 09:32:39 pm
It's required to clear the cache before setting new options.

Code: [Select]
  column.filter.cache = null
  column.filter.options = response;
  grid.refreshHeader();
Title: Re: Populate select list from DB
Post by: queensgambit9 on March 22, 2018, 09:41:56 pm
Hmm...still don't work...I get no error but select is not populated...
Title: Re: Populate select list from DB
Post by: paramvir on March 23, 2018, 12:31:52 pm
What's the full definition of column.filter and please share a jsfiddle if possible.
Title: Re: Populate select list from DB
Post by: queensgambit9 on March 23, 2018, 02:05:22 pm
Not sure what I did wrong, but works now.
Thanks for help.
Title: Re: Populate select list from DB
Post by: queensgambit9 on March 23, 2018, 09:08:15 pm
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"
}]

Title: Re: Populate select list from DB
Post by: paramvir on March 23, 2018, 09:42:28 pm
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.
Title: Re: Populate select list from DB
Post by: queensgambit9 on June 07, 2018, 01:59:13 pm
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...
Title: Re: Populate select list from DB
Post by: paramvir on June 08, 2018, 05:24:49 pm
Serialise the col array to string while sending to server

and get response as json object.
{col1: options1, col2: options2, ... }
Title: Re: Populate select list from DB
Post by: queensgambit9 on June 11, 2018, 12:58:11 am
Thanks.
Do you have a short example of this? (JS, PHP)

Thanks in advance.
Title: Re: Populate select list from DB
Post by: queensgambit9 on August 16, 2018, 08:04:39 pm
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.

Title: Re: Populate select list from DB
Post by: paramvir on August 17, 2018, 05:30:16 pm
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;
Title: Re: Populate select list from DB
Post by: queensgambit9 on August 17, 2018, 06:30:21 pm
So in PHP loop through the array to build up the response with distinct queries...?


Title: Re: Populate select list from DB
Post by: queensgambit9 on August 22, 2018, 12:50:29 pm
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.
Title: Re: Populate select list from DB
Post by: queensgambit9 on August 24, 2018, 02:06:53 am
Solved.
Title: Re: Populate select list from DB
Post by: paramvir on August 24, 2018, 10:34:36 am
Sounds great!
Title: Re: Populate select list from DB
Post by: queensgambit9 on August 24, 2018, 03:12:42 pm
Was a bit quick there... :)
Still having issues with the format of the json response. Currently I have:

Code: [Select]
{ {"country": [{"country":"aaa"}, {"country":"bbb"}]}, {"city": [{"city":"ccc"}, {"city":"ddd"}]} }
Won't work...guess format is wrong?

Also, how can I use a variable when assigning the options like:

Code: [Select]
arrCol.forEach(function (value) {
  column = grid.getColumn({ dataIndx: value });
  column.filter.cache = null;
  column.filter.options = response.[_USE_VARIABLE_VALUE_];
});

Title: Re: Populate select list from DB
Post by: paramvir on August 24, 2018, 03:29:52 pm
Please remove extra {}

Response could be:

Code: [Select]
{
    "country": [{"country":"aaa"}, {"country":"bbb"}], 
    "city": [{"city":"ccc"}, {"city":"ddd"}]     
}

Since response is an object, so use for loop:

Code: [Select]
for(var key in response){
  grid.getColumn({ dataIndx: key }).filter.options = response[ key ];
}
Title: Re: Populate select list from DB
Post by: queensgambit9 on August 24, 2018, 06:03:01 pm
Thanks. Adjusted JSON response, but still can't get it to work...

Code: [Select]
var arrCol = ["col1", "col2"];
    var grid = this;
    $.getJSON("/php/data.php", "cols=" + JSON.stringify(arrCol), function (response) {
        // all keys looks correct in response           
        for(var key in response){
            grid.getColumn({ dataIndx: key }).filter.options = response[ key ];
        }
       
        grid.refreshHeader();
    });
Title: Re: Populate select list from DB
Post by: paramvir on August 24, 2018, 07:59:13 pm
Did you check for any error in the browser console and did you use a debugger inside the for loop to ensure every filter.options are assigned correctly.

Could you share json response in an attachment file.
Title: Re: Populate select list from DB
Post by: queensgambit9 on August 24, 2018, 08:12:45 pm
No error in console. The options are assigned when I check the filter options using the debugger in console.
But the options do not appear.
Response is attached.
Title: Re: Populate select list from DB
Post by: queensgambit9 on August 27, 2018, 01:39:38 am
Update: Seems to be working now, didn't have the
Code: [Select]
column.filter.cache = null;...
Thanks for your help.
Title: Re: Populate select list from DB
Post by: paramvir on August 27, 2018, 10:43:02 am
Oh ok, that means you are using older version <= 5.1.0.

cache null is not required in >= 5.2.0
Title: Re: Populate select list from DB
Post by: queensgambit9 on August 27, 2018, 12:21:04 pm
You are correct, was using 5.1.0.