CODEWITHSUNDEEP
jqueryjqgridfilterreload
klm10
klm10

Reputation: 387

jqGrid Custom Grid Filter Reload

I'm using jqGrid to maintain a database in mysql through JSON data. I would like to have a filter reload the grid - inactive rows versus active rows. In my data url php file, where I connect to my database and feed it the initial query, I only pull columns where the avail_ind = 'Y' so all the data that is already in the grid are active records. So doing a search filter would not be the solution to the situation as that wouldn't find any records where avail_ind = 'N'.

I found Oleg's demo that something similar to what I'm looking for. I don't need the vertical headers that he demonstrates but mainly the Order Status box that contains check boxes to filter the grid data.

My main concern is since I'm initially pulling active records in the grid how can I feed the grid the parameters where avail_ind = 'N' or 'Y' to show all the possible records in the grid AND somehow link checkboxes or radio buttons to select which kind of records the user wants to view.

Thank you for your help.

UPDATE

jqGrid HTML/JavaScript code: All code wasn't included for brevity

 jQuery(document).ready(function(){ 
      jQuery("#grid").jqGrid("initFontAwesome").jqGrid({
          pager:'#gridpager', 
          url:'getDivisions.php',
          datatype: "json",
          mtype: 'GET',
          height: 300,
          width: 1000,
          showerrors:true,
          debug:true,

          colNames:['Div', 'L1L2', 'L2', 'L1L3', 'L2L3', 'Exec Begin', 'Exec End', 'CSA', 'Area Id','Short Desc','Enabled'],
          colModel:[
                    {name:'div_id',
                        index:'div_id', 
                        width:30,
                        editable:true,
                        sortable:false, 
                        resizable:false,
                        align:"center", 
                        editoptions:{size:2,maxlength:2},
                        editrules:{required:true,number:true},
                        formoptions:{elmprefix:"(*)"}
                     },

                    {name:'l1l2_id',
                        index:'l1l2_id',
                        width:30,
                        editable:true,
                        resizable:false,
                        sortable:false,
                        //formatter: nullFormatter,
                        editoptions:{size:4,maxlength:4},
                        editrules:{required:true,number:true},
                        formoptions:{elmprefix:"(*)"}

                    },

                    {name:'l2_id',
                        index:'l2_id', 
                        width:30,
                        editable:true,
                        resizable:false,
                        sortable:false,
                        align:"center",
                        editoptions:{size:2,maxlength:2},
                        editrules:{required:true,number:true},
                        formoptions:{elmprefix:"(*)"}
                    },
                    {name:'l1l3_id',
                        index:'l1l3_id', 
                        width:30,
                        editable:true,
                        resizable:false,
                        sortable:false,
                        editoptions:{size:5,maxlength:6,readonly:'readonly'},
                        editrules:{number:true}

                    },
                    {name:'l2l3_id',
                        index:'l2l3_id', 
                        width:30,
                        editable:true,
                        resizable:false,
                        sortable:false,
                        editoptions:{size:5,maxlength:4,readonly:'readonly'},
                        editrules:{number:true}
                    },
                    {name:'exec_beg',
                        index:'exec_beg', 
                        width:60,
                        editable:true,
                        resizable:false,
                        sortable:false,
                        editoptions:{size:10,maxlength:8,readonly:'readonly'},
                        editrules:{number:true}
                    },
                    {name:'exec_end',
                        index:'exec_end', 
                        width:50,
                        editable:true,
                        resizable:false,
                        sortable:false,
                        editoptions:{size:10,maxlength:8,readonly:'readonly'},
                        editrules:{number:true}
                    },
                    {name:'csa_id',
                        index:'csa_id', 
                        width:35,
                        editable:true,
                        resizable:false,
                        sortable:false,
                        editoptions:{size:5,maxlength:5},
                        editrules:{required:true},
                        formoptions:{elmprefix:"(*)"}
                    },
                    {name:'area_id',
                        index:'area_id',
                        width:40,
                        editable:true,
                        resizable:false,
                        sortable:false,
                        align:"center",
                        editoptions:{size:2,maxlength:2},
                        editrules:{number:true},
                        formoptions:{elmprefix:"(*)"}
                    },
                    {name:'short_desc',
                        index:'short_desc', 
                        width:60,
                        editable:true,
                        resizable:false,
                        sortable:false,
                        editoptions:{size:7,maxlength:10},
                        editrules:{required:true},
                        formoptions:{elmprefix:"(*)"}
                    },
                    {name:'avail_ind',
                        index:'avail_ind',
                        width:40,
                        editable:true,
                        resizable:false,
                        sortable:false,
                        align:"center",
                        edittype:"select",
                        editoptions:{value:"Y:Y;N:N"}}
                   ],

          viewrecords: true,
          sortorder: "asc",
          sortname: "div_id",
          caption:"Division Codes",
          editurl:'editdivisions.php',
          toppager:true, 
          recordtext:'', 
          hidegrid:false, 
          scroll:true,
          rowNum:"10000"

     }); //jQuery("#grid").jqGrid

GetDivisions.php code: retrieves the data to load the grid 

<?php 
    $dbhost = "localhost"; 
    $dbuser = "root"; 
    $dbpass = "*********"; 
    $dbname = "CodeTable"; 


    $page = $_GET['page']; // get the requested page
    $limit = $_GET['rows']; // get how many rows we want to have into the grid
    $sidx = $_GET['sidx']; // get index row - i.e. user click to sort
    $sord = $_GET['sord']; // get the direction

    if(!$sidx) $sidx =1;

    // connect to the database
    $db = mysql_connect($dbhost, $dbuser, $dbpass) or die("Connection Error: " . mysql_error());

    mysql_select_db($dbname) or die("Error conecting to db.");
    $result = mysql_query('SELECT div_id, l1l2_id, l2_id, l1l3_id, l2l3_id, exec_beg, exec_end, csa_id, area_id, short_desc, avail_ind FROM divcodes where div_id <> "  " and avail_ind = "Y" and active_ind="Y"');
    $row = mysql_fetch_array($result,MYSQL_ASSOC);
    $count = $row['count'];

    if( $count >0 ) 
    {
        $total_pages = cell($count/$limit);
    } 
    else 
    {
            $total_pages = 0;
    }

    if ($page > $total_pages) $page=$total_pages;
    $start = $limit*$page - $limit; // do not put $limit*($page - 1)
    $SQL = 'SELECT div_id, l1l2_id, l2_id, l1l3_id, l2l3_id, exec_beg, exec_end, csa_id, area_id, short_desc, avail_ind FROM divcodes where div_id <> "  " and avail_ind = "Y" and active_ind="Y"';
    $result = mysql_query( $SQL ) or die("Couldn t execute query.".mysql_error());

    $response->page = $page;
    $response->total = $total_pages;
    $response->records = $count;

    $i=0;
    while($row = mysql_fetch_array($result,MYSQL_ASSOC)) {
        $response->rows[$i]['id']=$row[div_id];
        $response->rows[$i]['cell']=array($row[div_id],$row[l1l2_id],$row[l2_id],$row[l1l2_id],$row[l2l3_id],$row[exec_beg],$row[exec_end],$row[csa_id],$row[area_id],$row[short_desc],$row[avail_ind]);
        $i++;
    }        
    echo json_encode($response);

    ?>

Upvotes: 0

Views: 1659

Answers (1)

Mark
Mark

Reputation: 3123

I would suggest you do not hard-code your server side query on your data, but rather let jqGrid tell your server what it is looking for.

This would be accomplished by setting up a radio button element(s) or checkbox, etc. as part of your webpage. Then on the page load you can help form the request jqGrid sends out, and then resends when the element(s) that you have setup change in state.

Ex:

element.ChangeEvent()( function () {
    var grid = $('#gridName');

grid.jqGrid('setGridParam', { search: false });
var postData = grid.jqGrid('getGridParam', 'postData');
    $.extend(postData, { filters: "" });

var f = { groupOp: "AND", rules: [] };

    f.rules.push({ field: "avail_ind", op: "eq", data: $(selectedElementName).val() });
    grid.jqGrid('setGridParam', { search: true });
}
JSON.stringify(f) : "")}

                                        });
    grid.jqGrid().trigger('reloadGrid', [{ page: 1}]);

});

As part of this when you are originally setting up the jqGrid you are setting the search: true and the filter as part of the grid setup so it shows the acurate data to start, and then the changing user preference as the element you setup changes.

Upvotes: 1

Related Questions