JQGrid with CI

June 24, 2016

JQGrid is one the best Grid solutions available out there. In another blog entry here we displayed how we can use JQGrid in asp.net application.

This article covers how we can integrate JQGrid in CI application.

At very high level, we need to perform following steps:

Fetch data from database into standard class object inside model
Send it to the controller
Send it to the view from controller

Object format: The snippet below displays the StdClass Object format.

e.g. {"page":"1",
"total":17,
"records":170,
"start":0,
"limit":"10",
"rows":[{id:"1",name:"test1",note:"note23",amount:"200.00"},
    {id:"2",name:"test2",note:"note2",amount:"300.00"},
    {id:"3",name:"test3",note:"note32",amount:"400.00"},
    {id:"4",name:"test23",note:"note423",amount:"700.00"},
    {id:"5",name:"test2",note:"note2",amount:"300.00"},
    {id:"6",name:"test333",note:"note344",amount:"500.00"}]}

CSS : In your view, you would have to include JQuery UI and JQGrid CSS files. Please note that JQGrid’s basic styling is dependent on JQuery UI.

    <link type="text/css" rel="stylesheet" href="http://code.jquery.com/ui/1.8.13/themes/base/jquery-ui.css" />
    <link type="text/css" rel="stylesheet" href="http://www.trirand.com/blog/jqgrid/themes/ui.jqgrid.css" />

JS : In your view include following files. Please make sure that you include the locale-en.js (or some other locale file), or else your grid would not work.

    <script type="text/javascript" src="http://code.jquery.com/jquery-1.7.2.min.js"></script>
    <script type="text/javascript" src="http://ajax.aspnetcdn.com/ajax/jquery.ui/1.8.13/jquery-ui.js"></script>
    <script type="text/javascript" src="http://www.trirand.com/blog/jqgrid/js/jquery.jqGrid.min.js"></script>
    <script type="text/javascript" src="http://www.trirand.com/blog/jqgrid/js/i18n/grid.locale-en.js"></script>

Model Code : In the model class, lets put this simple code which reads few parameters from the request and fetches the data from the DB.

public function get_grid_data($request_data){
    try{
        $page = $request_data['page']; // get the requested page
        $limit = $request_data['rows']; // get how many rows we want to have into the grid
        $totalrows = isset($request_data['totalrows']) ? $request_data['totalrows']: false;
        if($totalrows) {
            $limit = $totalrows;
        }

        $this->db->select("*",false);
        $this->db->from('test t');
        $total_rows = $this->db->get()->result();

        $count = count($total_rows);
        $response = new stdClass();
        if( $count >0 ) {
            $total_pages = ceil($count/$limit);
        } else {
            $total_pages = 0;
        }
        if ($page > $total_pages){
            $page = $total_pages;
        }
        if ($limit <= 0){
            $limit = 0;
        }
        $start = $limit * $page - $limit;
        if ($start<=0){
            $start = 0;
        }
        $response->page = $page;
        $response->total = $total_pages;
        $response->records = $count;
        $response->start = $start;
        $response->limit = $limit;

        $eligible_rows = array_slice($total_rows, $start, $limit);

        $i = 0;
        foreach($eligible_rows as $row) {
            $response->rows[$i]['id'] = $row->id;
            $response->rows[$i++]['cell'] = array($row->id, $row->name, $row->note, $row->amount);
        }
        return $response;
    }catch (Exception $e){
        $this->handle_exception($e);
    }
}

Controller Code : In the controller, add following code. All this code would do is, invoke model method mentioned above and return the response as JSON.

public function jqgrid_show(){
    try{
        $response = $this->model_name->get_grid_data($_REQUEST);
        header('Content-type: application/json');
        echo json_encode($response);
    }catch (Exception $e){
        $this->handle_controller_exception($e);
    }
}

View code: In your view, you need to add following 2 placeholders (1 for grid and 1 for pager).

    <table id="tblJQGrid"></table>
        <div id="divPager"></div>

JQuery code: Now add the JQuery code mentioned below in your view.

<script language="javascript" type="text/javascript">
$(document).ready(function() {
    createUserGrid();
});

function createUserGrid() {
    $("#tblJQGrid").jqGrid({
        url:path_to_the_controller/jqgrid_show,
        datatype: "json",
        height: 231,
        width: 937,
        colNames:['Number', 'Name', 'Amount', 'Notes'],
        colModel:[
            {name:'id',index:'id', width:60, sorttype:"int"},
            {name:'name',index:'name', width:100},
            {name:'amount',index:'amount', width:80, align:"right",sorttype:"float"},    
            {name:'note',index:'note', width:150, sortable:false}        
        ],
        //multiselect: true,
        rowNum:10,
        rowList:[10,20,30],
        loadonce: false,
        pager: '#divPager',
        caption: "Users Data" 
    });
}
</script>

If everything goes fine, you should see a nice shining JQGrid in your view 🙂