Jqgrid + CodeIgniter

Question

I tried to make jqgrid work with codeigniter, but I could not do it, I only want to show the data from the table in json format... but nothing happens.. but i dont know what i am doing wrong, i cant see the table with the content i am calling.

my controller

class Grid extends Controller
{

  public function f()
  {

       $this->load->model('dbgrid');
       $var['grid'] =  $this->dbgrid->getcontentfromtable();

       foreach($var['grid'] as $row) {
        $responce->rows[$i]['id']=$row->id;
        $responce->rows[$i]['cell']=array($row->id,$row->id_catalogo);

       }
     $json = json_encode($responce);
       $this->load->view('vgrid',$json);



  } 


  function load_view_grid()
  {

    $this->load->view('vgrid');


  }


}

my model

class Dbgrid extends Model{

function getcontentfromtable()
{


  $sql = 'SELECT * FROM anuncios';
  $query = $this->db->query($sql);
  $result = $query->result();


  return $result;   
}

my view(script)

$(document).ready(function() { 
 jQuery("#list27").jqGrid({
        url:'http://localhost/sitio/index.php/grid/f',
        datatype: "json",
        mtype: "post",
        height: 255,
        width: 600,
        colNames:['ID','ID_CATALOGO'],
        colModel:[
            {name:'id',index:'id', width:65, sorttype:'int'},
            {name:'id_catalogo',index:'id_catalogo', sorttype:'int'}


        ],
        rowNum:50,
        rowTotal: 2000,
        rowList : [20,30,50],
        loadonce:true,
        rownumbers: true,
        rownumWidth: 40,
        gridview: true,
        pager: '#pager27',
        sortname: 'item_id',
        viewrecords: true,
        sortorder: "asc",
        caption: "Loading data from server at once" 
    });

}); 

hope someone help me

Answer 1

Please be warned the example code of triand used here many times has SQL-injection risk. before generating $where string u should escape your searchString like;

replace this:

$where = "$searchField $ops '$searchString' "; 

with this:

$searchString = mysql_real_escape_string($searchString);
$where = "$searchField $ops '$searchString' "; 

Answer 2

I am a new programmer in code igniter. I am trying to integrate jqgrid with code igniter and after seven hours i came to a successful point where jqgrid and code igniter is fully integrated with search option.

At first, write a model in your application/model directory. The code is......

class JqgridSample extends CI_Model {

  function getAllData($start,$limit,$sidx,$sord,$where){
    $this->db->select('id,name,email,passport,phone,fax,address');
    $this->db->limit($limit);
    if($where != NULL)
        $this->db->where($where,NULL,FALSE);
    $this->db->order_by($sidx,$sord);
    $query = $this->db->get('info',$limit,$start);

    return $query->result();
  }
} 

Then, write a controller in your application/controller directory. The code is

class Demo extends CI_Controller {

function __construct() {
    parent::__construct();
    $this->load->model('JqgridSample');
}

function jqGrid(){
$this->load->view('showGrid');
}

function loadData(){
    $page = isset($_POST['page'])?$_POST['page']:1; 
    $limit = isset($_POST['rows'])?$_POST['rows']:10; 
    $sidx = isset($_POST['sidx'])?$_POST['sidx']:'name'; 
    $sord = isset($_POST['sord'])?$_POST['sord']:'';         
    $start = $limit*$page - $limit; 
    $start = ($start<0)?0:$start; 

    $where = ""; 
    $searchField = isset($_POST['searchField']) ? $_POST['searchField'] : false;
    $searchOper = isset($_POST['searchOper']) ? $_POST['searchOper']: false;
    $searchString = isset($_POST['searchString']) ? $_POST['searchString'] : false;

    if ($_POST['_search'] == 'true') {
        $ops = array(
        'eq'=>'=', 
        'ne'=>'<>',
        'lt'=>'<', 
        'le'=>'<=',
        'gt'=>'>', 
        'ge'=>'>=',
        'bw'=>'LIKE',
        'bn'=>'NOT LIKE',
        'in'=>'LIKE', 
        'ni'=>'NOT LIKE', 
        'ew'=>'LIKE', 
        'en'=>'NOT LIKE', 
        'cn'=>'LIKE', 
        'nc'=>'NOT LIKE' 
        );
        foreach ($ops as $key=>$value){
            if ($searchOper==$key) {
                $ops = $value;
            }
        }
        if($searchOper == 'eq' ) $searchString = $searchString;
        if($searchOper == 'bw' || $searchOper == 'bn') $searchString .= '%';
        if($searchOper == 'ew' || $searchOper == 'en' ) $searchString = '%'.$searchString;
        if($searchOper == 'cn' || $searchOper == 'nc' || $searchOper == 'in' || $searchOper == 'ni') $searchString = '%'.$searchString.'%';

        $where = "$searchField $ops '$searchString' "; 

    }

    if(!$sidx) 
        $sidx =1;
    $count = $this->db->count_all_results('info'); 
    if( $count > 0 ) {
        $total_pages = ceil($count/$limit);    
    } else {
        $total_pages = 0;
    }

    if ($page > $total_pages) 
        $page=$total_pages;
    $query = $this->JqgridSample->getAllData($start,$limit,$sidx,$sord,$where); 
    $responce->page = $page;
    $responce->total = $total_pages;
    $responce->records = $count;
    $i=0;
    foreach($query as $row) {
        $responce->rows[$i]['id']=$row->id;
        $responce->rows[$i]['cell']=array($row->name,$row->email,$row->passport,$row->phone,$row->fax,$row->address);
        $i++;
    }

    echo json_encode($responce);
}
}

And finally you write a view in your application/views directory..

<head>

    <link rel="stylesheet" type="text/css" href="<?php echo base_url()?>application/views/css/custom-theme/jquery-ui-1.8.16.custom.css" />

    <link type="text/css" href="<?php echo base_url()?>application/views/css/ui.jqgrid.css" rel="stylesheet" />

    <link type="text/css" href="<?php echo base_url()?>application/views/css/plugins/searchFilter.css" rel="stylesheet" />

    <style>
        html, body {
            margin: 0;
            padding: 0;
            font-size: 75%;
        }
    </style>

    <script type="text/javascript" src="<?php echo base_url(); ?>application/views/js/jquery-1.5.2.min.js"></script>

    <script type="text/javascript" src="<?php echo base_url(); ?>application/views/js/i18n/grid.locale-en.js"></script>

    <script type="text/javascript" src="<?php echo base_url(); ?>application/views/js/jquery.jqGrid.min.js"></script>


    <title>Codeigniter With JQGrid</title>
</head>
<body>
    <center>
        <h1>Codeigniter With JQGrid</h1>
    <?php
        $ci =& get_instance();
        $base_url = base_url();
    ?>
    <table id="list"></table><!--Grid table-->
    <div id="pager"></div>  <!--pagination div-->
    </center>
</body>


<script type="text/javascript">
        $(document).ready(function (){
            jQuery("#list").jqGrid({
                url:'<?=$base_url.'index.php/demo/loadData'?>',      //another controller function for generating data
                mtype : "post",             //Ajax request type. It also could be GET
                datatype: "json",            //supported formats XML, JSON or Arrray
                colNames:['Name','Email','Passport','Phone','Fax','Address'],       //Grid column headings
                colModel:[
                    {name:'name',index:'name', width:100, align:"left"},
                    {name:'email',index:'email', width:150, align:"left"},
                    {name:'passport',index:'passport', width:100, align:"right"},
                    {name:'phone',index:'phone', width:100, align:"right"},
                    {name:'fax',index:'fax', width:100, align:"right"},
                    {name:'address',index:'address', width:100, align:"right"},
                ],
                rowNum:10,
                width: 750,
                //height: 300,
                rowList:[10,20,30],
                pager: '#pager',
                sortname: 'id',
                viewrecords: true,
                rownumbers: true,
                gridview: true,
                caption:"List Of Person"
            }).navGrid('#pager',{edit:false,add:false,del:false});
        });
    </script>

For the view file for myself i create two folder in views directory js and css. and in js foder i place the jquery-1.5.2.min.js, grid.locale-en.js(views/js/i18n/), jquery.jqGrid.min.js which you find in jqgrid package.

In a same way jquery-ui-1.8.16.custom.css, ui.jqgrid.css needed that also is available in jqgrid package.

for running full process you have to create a database named jqgrid_sample and in the database create a table named info contains fields...

id

name

email

passport

phone

fax

address

thats it. enjoy. good bye.

Answer 3

These Solution is Cover full JqGrid Data Load and CRUD. This is Very Simple Task. Just go through the following steps and enjoy.

1. Write a Model just like below

   class gridAction_model extends CI_Model {
   
   public function __construct() {
       $this->load->database();
   }
   
   function getAllTeacherDesignation($start, $limit, $sidx, $sord, $where) {
   
       $this->db->select('DesignationId,DesignationName,Description,Status');
       $this->db->limit($limit);
       if ($where != NULL)
           $this->db->where($where, NULL, FALSE);
       $this->db->order_by($sidx, $sord);
       $query = $this->db->get('TeacherDesignation', $limit, $start);
   
       return $query->result();
   }
   
   function insert_teacherDesignation($data) {
       return $this->db->insert('TeacherDesignation', $data);
   }
   
   function update_teacherDesignation($id, $data) {
       $this->db->where('DesignationId', $id);
       return $this->db->update('TeacherDesignation', $data);
   }
   
   function delete_teacherDesignation($id) {
       $this->db->where('DesignationId', $id);
       $this->db->delete('TeacherDesignation');
   }
   

   }

2. Now Add the following Method to the Controller Class

       class grid_action extends CI_Controller {
   
   public function __construct() {
       parent::__construct();
       $this->load->helper("form");
       $this->load->model("gridAction_model");
   }
   
   public function loadTeacherDesignationData() {
   
       $page = isset($_POST['page']) ? $_POST['page'] : 1;
       $limit = isset($_POST['rows']) ? $_POST['rows'] : 10;
       $sidx = isset($_POST['sidx']) ? $_POST['sidx'] : 'DesignationName';
       $sord = isset($_POST['sord']) ? $_POST['sord'] : '';
       $start = $limit * $page - $limit;
       $start = ($start < 0) ? 0 : $start;
   
       $where = "";
       $searchField = isset($_POST['searchField']) ? $_POST['searchField'] : false;
       $searchOper = isset($_POST['searchOper']) ? $_POST['searchOper'] : false;
       $searchString = isset($_POST['searchString']) ? $_POST['searchString'] : false;
   
       if ($_POST['_search'] == 'true') {
           $ops = array(
               'eq' => '=',
               'ne' => '<>',
               'lt' => '<',
               'le' => '<=',
               'gt' => '>',
               'ge' => '>=',
               'bw' => 'LIKE',
               'bn' => 'NOT LIKE',
               'in' => 'LIKE',
               'ni' => 'NOT LIKE',
               'ew' => 'LIKE',
               'en' => 'NOT LIKE',
               'cn' => 'LIKE',
               'nc' => 'NOT LIKE'
           );
           foreach ($ops as $key => $value) {
               if ($searchOper == $key) {
                   $ops = $value;
               }
           }
           if ($searchOper == 'eq')
               $searchString = $searchString;
           if ($searchOper == 'bw' || $searchOper == 'bn')
               $searchString .= '%';
           if ($searchOper == 'ew' || $searchOper == 'en')
               $searchString = '%' . $searchString;
           if ($searchOper == 'cn' || $searchOper == 'nc' || $searchOper == 'in' || $searchOper == 'ni')
               $searchString = '%' . $searchString . '%';
   
           $where = "$searchField $ops '$searchString' ";
       }
   
       if (!$sidx)
           $sidx = 1;
       $count = $this->db->count_all_results('TeacherDesignation');
       if ($count > 0) {
           $total_pages = ceil($count / $limit);
       } else {
           $total_pages = 0;
       }
   
       if ($page > $total_pages)
           $page = $total_pages;
   
       $query = $this->gridAction_model->getAllTeacherDesignation($start, $limit, $sidx, $sord, $where);
   
       $responce = new stdClass;
   
       $responce->page = $page;
       $responce->total = $total_pages;
       $responce->records = $count;
       $i = 0;
   
       foreach ($query as $row) {
           $responce->rows[$i]['id'] = $row->DesignationId;
           $responce->rows[$i]['cell'] = array($row->DesignationId, $row->DesignationName, $row->Description, $row->Status);
           $i++;
       }
       echo json_encode($responce);
   }
   
   public function crudTeacherDesignation() {
   
       $oper = $this->input->post('oper');
       $id = $this->input->post('id');
       $DesignationId = $this->input->post('DesignationId');
       $DesignationName = $this->input->post('DesignationName');
       $Description = $this->input->post('Description');
       $Status = $this->input->post('Status');
   
       switch ($oper) {
           case 'add':
               $data = array('DesignationId' => $DesignationId, 'DesignationName' => $DesignationName, 'Description' => $Description, 'Status' => $Status);
               $this->gridAction_model->insert_teacherDesignation($data);
               break;
           case 'edit':
               $data = array('DesignationId' => $DesignationId, 'DesignationName' => $DesignationName, 'Description' => $Description, 'Status' => $Status);
               $this->gridAction_model->update_teacherDesignation($DesignationId, $data);
               break;
           case 'del':
               $this->gridAction_model->delete_teacherDesignation($DesignationId);
               break;
       }
   }  
   

   }

3. Add the Script at View

    <table id="gridDesignation"> </table>
   
    <div id="pager"> </div>
   
   
   
   
   
           $(document).ready(function () {
   
       jQuery("#gridDesignation").jqGrid({
           url:'<?php echo base_url(); ?>grid_action/loadTeacherDesignationData',                
           mtype : "post",              //Ajax request type. It also could be GET
           datatype: "json",            //supported formats XML, JSON or Arrray
           colNames:['Designation ID','Designation Name','Description','Status'],       //Grid column headings
           colModel:[
               {name:'DesignationId',index:'DesignationId', width:100, align:"left", editable:true, editrules:{required:true}},
               {name:'DesignationName',index:'DesignationName', width:150, align:"left",editable:true,editrules:{required:true}},
               {name:'Description',index:'Description', width:100, align:"left", sortable:false, editable:true,editrules:{required:true}},
               {name:'Status',index:'Status', width:100, align:"right",editable:true,editrules:{required:true}, 
                   edittype:'select', editoptions:{value:"1:Active;0:InActive"}
               }
           ],
           rownumbers: true,
           rowNum:10,
           width: 750,
           height: "100%",
           rowList:[10,20,30],
           pager: jQuery('#pager'),
           sortname: 'DesignationName',
           autowidth: true,
           viewrecords: true,            
           gridview: true,  
           ondblClickRow: function(id){              
               $("#gridDesignation").editGridRow(id, {closeAfterEdit:true,mtype:'POST'});                
           },
           sortorder: "desc",       
           editurl: '<?php echo base_url() ?>grid_action/crudTeacherDesignation', //URL Process CRUD
           multiselect: false,
           caption:"List Of Teacher Designation"
       }).navGrid('#pager',
       {view:true,edit:true,add:true,del:true},
       {closeOnEscape:true},
       {closeOnEscape:true},
       {closeOnEscape:true},
       {closeOnEscape:true},
       {
           closeOnEscape:true,closeAfterSearch:false,multipleSearch:false, 
           multipleGroup:false, showQuery:false,
           drag:true,showOnLoad:false,sopt:['cn'],resize:false,
           caption:'Search Record', Find:'Search', 
           Reset:'Reset Search'
       }
   );        
   
   });
   

Answer 4

The data produced by the server which you post in the comment

{"rows":{"":{"id":"11","cell":["11","225101503"]}}} 

have wrong format. The output should looks like

{ 
  "total": "xxx", 
  "page": "yyy", 
  "records": "zzz",
  "rows" : [
    {"id" :"1", "cell" :["cell11", "cell12", "cell13"]},
    {"id" :"2", "cell":["cell21", "cell22", "cell23"]},
      ...
  ]
}

(see http://www.trirand.com/jqgridwiki/doku.php?id=wiki:retrieving_data#json_data). So it should be at least like

{"rows":[{"id":"11","cell":["11","225101503"]}]}

In general if you define a jsonReader, you will be able to read almost any data. The data which you produce can be readed only by jsonReader defined with the functions (see http://www.trirand.com/jqgridwiki/doku.php?id=wiki:retrieving_data#jsonreader_as_function and jquery with ASP.NET MVC - calling ajax enabled web service). The simplest way for you will be to change your server code to produce the data in a standard formet (see above), which can be readed by the standard jsonReader.

And one more small remark. Using of sorttype has no effect for datatype: "json". Parameter sorttype works only with sorting of local data. In case of datatype: "json" the server will be responsible for correct data sorting. jqGrid send to the server only the name of column, which user choose for the data sorting.