Jqgrid + CodeIgniter

Question

I tried to get jqgrid to work with codeigniter, but I couldn’t do it, I want to show the data from the table in json format ... but nothing happens .. but I don’t know what I'm doing wrong, I can not see the table with the contents I'm 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 look (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

Data created by the server that you post in the comment

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

have the wrong format. The result should look 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 ). Therefore, it should be at least

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

In general, if you define jsonReader , you can read almost any kind of data. The data you produce can only be read using jsonReader defined using functions (see http://www.trirand.com/jqgridwiki/doku.php?id=wiki:retrieving_data#jsonreader_as_function and jquery with ASP.NET MVC - ajax-enabled web service call ). The easiest way for you is to change the server code to create data in a standard form (see above) that can be read by the standard jsonReader .

And one more small remark. Using sorttype does not affect datatype: "json" . The sorttype parameter sorttype works with sorting local data. In the case of datatype: "json" server will be responsible for sorting the data correctly. jqGrid sends to the server only the column name that the user selects to sort the data.

Answer 2

I am a new programmer in code igniter. I am trying to integrate jqgrid with a code igniter, and seven hours later I came to a successful point where jqgrid and code igniter are fully integrated with the search option.

First write the model in your application / model directory. The code...

 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 the controller in the directory of your application / controller. The code

 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 the view in your application / view 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 folders in the view directory js and css. and in js foder put jquery-1.5.2.min.js, grid.locale-en.js (views / js / i18n /), jquery.jqGrid.min.js, which you will find in the jqgrid package.

In the same way, you need jquery-ui-1.8.16.custom.css, ui.jqgrid.css, which is also available in the jqgrid package.

To start the full process, you need to create a database named jqgrid_sample, and in the database create a table named info that contains the fields ...

ID

name

Email

Passport

telephone

fax machine

address

here it is. enjoy. bye.

Answer 3

These solutions are a complete download of JqGrid and CRUD data. This is a very simple task. Just follow these steps and enjoy.

• Write the model as 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'); } 

  }

• Now add the following class 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; } } 

  }

• Add Script to 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

Please be careful, the triand example code used here many times has the risk of SQL injection . before generating $where string u should escape from your searchString, for example:

replace this:

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

with this:

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