Author Topic: Populate select list from DB  (Read 13568 times)

queensgambit9

  • Pro Ultimate
  • Sr. Member
  • *
  • Posts: 341
    • View Profile
Populate select list from DB
« 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...
« Last Edit: March 22, 2018, 09:07:35 pm by queensgambit9 »

paramvir

  • Administrator
  • Hero Member
  • *****
  • Posts: 6275
    • View Profile
Re: Populate select list from DB
« Reply #1 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();
« Last Edit: March 22, 2018, 09:40:20 pm by paramquery »

queensgambit9

  • Pro Ultimate
  • Sr. Member
  • *
  • Posts: 341
    • View Profile
Re: Populate select list from DB
« Reply #2 on: March 22, 2018, 09:41:56 pm »
Hmm...still don't work...I get no error but select is not populated...
« Last Edit: March 22, 2018, 09:43:41 pm by queensgambit9 »

paramvir

  • Administrator
  • Hero Member
  • *****
  • Posts: 6275
    • View Profile
Re: Populate select list from DB
« Reply #3 on: March 23, 2018, 12:31:52 pm »
What's the full definition of column.filter and please share a jsfiddle if possible.

queensgambit9

  • Pro Ultimate
  • Sr. Member
  • *
  • Posts: 341
    • View Profile
Re: Populate select list from DB
« Reply #4 on: March 23, 2018, 02:05:22 pm »
Not sure what I did wrong, but works now.
Thanks for help.

queensgambit9

  • Pro Ultimate
  • Sr. Member
  • *
  • Posts: 341
    • View Profile
Re: Populate select list from DB
« Reply #5 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"
}]


paramvir

  • Administrator
  • Hero Member
  • *****
  • Posts: 6275
    • View Profile
Re: Populate select list from DB
« Reply #6 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.
« Last Edit: March 23, 2018, 09:59:57 pm by paramquery »

queensgambit9

  • Pro Ultimate
  • Sr. Member
  • *
  • Posts: 341
    • View Profile
Re: Populate select list from DB
« Reply #7 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...
« Last Edit: June 07, 2018, 02:01:29 pm by queensgambit9 »

paramvir

  • Administrator
  • Hero Member
  • *****
  • Posts: 6275
    • View Profile
Re: Populate select list from DB
« Reply #8 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, ... }

queensgambit9

  • Pro Ultimate
  • Sr. Member
  • *
  • Posts: 341
    • View Profile
Re: Populate select list from DB
« Reply #9 on: June 11, 2018, 12:58:11 am »
Thanks.
Do you have a short example of this? (JS, PHP)

Thanks in advance.

queensgambit9

  • Pro Ultimate
  • Sr. Member
  • *
  • Posts: 341
    • View Profile
Re: Populate select list from DB
« Reply #10 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.

« Last Edit: August 16, 2018, 08:20:04 pm by queensgambit9 »

paramvir

  • Administrator
  • Hero Member
  • *****
  • Posts: 6275
    • View Profile
Re: Populate select list from DB
« Reply #11 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;
« Last Edit: August 17, 2018, 05:32:01 pm by paramquery »

queensgambit9

  • Pro Ultimate
  • Sr. Member
  • *
  • Posts: 341
    • View Profile
Re: Populate select list from DB
« Reply #12 on: August 17, 2018, 06:30:21 pm »
So in PHP loop through the array to build up the response with distinct queries...?


« Last Edit: August 17, 2018, 06:38:22 pm by queensgambit9 »

queensgambit9

  • Pro Ultimate
  • Sr. Member
  • *
  • Posts: 341
    • View Profile
Re: Populate select list from DB
« Reply #13 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.

queensgambit9

  • Pro Ultimate
  • Sr. Member
  • *
  • Posts: 341
    • View Profile
Re: Populate select list from DB
« Reply #14 on: August 24, 2018, 02:06:53 am »
Solved.