jqGrid, how to populate select list from query

Question

I got a basic jqGrid working in my coldfusion project. One of my fields in jqGrid is a combo box. Currently the editoption values are hard coded just like below.

colModel :
[
  {
   name:'seqnum',index:'seqnum', width:100,resizable:true,   
   align:"left",sorttype:"text",editable:true,edittype:"select",editoptions:   
   { value:"1:one;2:two"},editrules:{required:true}
  }
]

I am trying to figure out a way to populate the drop-down from a query/url.

Any help would be greatly appreciated.

Thanks in advance

score 6 · Answer 1 · answered Apr 26 '10 at 16:33

The $.getJSON / getSequenceNumbers() answer does not work as presented. There is no way to return data from a callback as the return value for getSequenceNumbers() because the callback is asynchronous. You either need to use the dataURL method suggested by Martin or setup the jqGrid inside of the $.getJSON callback.

$(document).ready(function() {
 $.getJSON("GetURL", function(data) {
  setupGrid(data);
 });
});

function setupGrid(data) {
...
    colModel :
    [
      {
       name:'seqnum',index:'seqnum', width:100,resizable:true,   
       align:"left",sorttype:"text",editable:true,edittype:"select",editoptions:
       { value:data},editrules:{required:true}
      }
    ]

...
}

score 6 · Answer 2 · answered May 18 '09 at 12:59

Create a function that uses json to query the url. This function should return a string in the format "1:one;2:two".

For example:

    colModel :
    [
      {
       name:'seqnum',index:'seqnum', width:100,resizable:true,   
       align:"left",sorttype:"text",editable:true,edittype:"select",editoptions:
       { value:getSequenceNumbers()},editrules:{required:true}
      }
    ]

    function getSequenceNumbers(){
        $.getJSON("yourUrl", null, function(data) {
            if (data != null) {
                 //construct string.  
                 //(or the server could return a string directly)
            }
        });
    }

I suppose you could put the function inline as well, but I think it would be harder to read.

score 5 · Answer 3 · edited Sep 19 '09 at 14:58

5

Use dataUrl... (see the wiki here).

Currently dataUrl generates a GET but if one pulls the code from Github the GET can be changes to a POST with no apparent side effects.

edited Sep 19 '09 at 14:58

sth

222,467
53
283
367

answered Sep 18 '09 at 21:09

Martin

51
1
3

1

This sort of works, but only if you don't want to decode the first value being shown as the ajax to get the list only fires when you edit the cell, hence formatter:'select' is incompatible with this. – Cruachan Jul 27 '11 at 12:14
Is it possible to pass some parameters into dataUrl-method? As parameter I would like to send some values from the selected row ( $('#grid').getRowData(id)['colName']) – Anatoliy Aug 22 '12 at 08:26

score 4 · Answer 4 · answered Jul 27 '11 at 12:36

It's worth noting that you can sidestep the issue completely if you're using a server-side scripting language. For example with PHP you might use

{name:'myselectdata', index:'myselectdata', width:160, formatter:'select', editable:true, edittype:"select", cellsubmit:"clientArray", editoptions:{ <? echo getData() ?>}  },

Then just setup the PHP function getData() further up the page to return a suitable string, for example

'value:"1:one;2:two"';

Maybe not as elegant or as portable as handling everything in jQuery, but conceptually easier I think.

correct data format would be {value:{1:'One',2:'Two'}} } according to this http://www.trirand.com/jqgridwiki/doku.php?id=wiki:common_rules — buzibuzi, Sep 25 '13 at 15:33

score 3 · Answer 5 · answered Oct 10 '12 at 19:43

Lets say that in your ColModel you have a column like this:

{name:'id_UDM', index:'id_UDM', width:150, editable:true, edittype:"select", editoptions:{dataUrl:'filename.php'}}

You must first declare that its a select element with this:

edittype:"select"

Then, in the editoptions parameter add a dataUrl like this:

editoptions:{dataUrl:'filename.php'}

The filename.php must return a "select" element with it's options, here's an example:

<?php
    include("connectionToMyDatabase.php");
    $query1 = "SELECT * FROM table WHERE 
    $result1 = mysql_query($query1);
    $response ='<select>';
    while($row = mysql_fetch_array($result1)) {
         $response .= '<option value="'.$row['value'].'">'.$row['name'].'</option>';
    }
    $response .= '</select>';

    echo $response;

 mysql_close($db);  
?>

Hope this helps.

score 1 · Answer 6 · answered Nov 28 '12 at 20:32

I know this is an old question, but it I've find the same problem.
I solve this by combination of dataUrl and ajaxSelectOptions.

colModel:[
    //class_id
    $.extend(true,
    {
        name:'class_id'
        ,index:'class_id'
        ,edittype:'select'
        ,formatter:'select'
        ,editoptions: { dataUrl:"db.php?ajaxOp=getClassesOptions" } //to send dynamic parameter, combine with ajaxSelectOptions
    }
    ,{}
    )

Note that dataUrl string ARE static, means you cannot send different parameters each time add/edit occurs. Below code WILL NOT WORK !

,editoptions: { dataUrl:"db.php?ajaxOp=getClassesOptions" + "&id="+selected_id }

To send parameters such id, you can use ajaxSelectOptions .

ajaxSelectOptions:      //use this for combination with dataUrl for formatter:select
{       
    data: {
      id: function () {
          return selected_id;
      }
    }
},

The function which return selected_id will be executed each time the add/edit occurs. Hope this helps !

score 0 · Answer 7 · answered Sep 08 '14 at 19:34

I adapted the code for searchoptions instead of editoptions. However it was automatically selecting the first option and filtering the list so I added the following to alleviate that.

 $response ='<select>';
 $response .= '<option value=""></option>';
 while($row = $stmt->fetch(PDO::FETCH_NUM)) {
      $response .= '<option value="'.$row[0].'">'.$row[0].'</option>';
 }
 $response .= '</select>';

 echo $response;

jqGrid, how to populate select list from query

7 Answers7

Linked