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

Answer 1

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}
      }
    ]

...
}

Answer 2

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.

Answer 3

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 !

Answer 4

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.

Answer 5

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.

Answer 6

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;