jQuery Grid Plugin - jqGrid

var lastSel;
  var ap_id = $("#no").val();
  var action = $("#action").val();

jQuery("#claim").jqGrid({
  url:'form/claim_data.php?apid='+ap_id,
  datatype: "json",
  colNames:['Claim Detail ID','Item','Claim ID', 'Date', 'Reference', 'Amount',  'Site ID','Acc ID'],    
  colModel:[
      {name:'int_claimdetailid',index:'int_claimdetailid', editable:true, align:"center", width:110, hidden:true },
      {name:'txt_item', index:'txt_item', editable:true,  align:"center", width:150, stype:'text' },
      {name:'int_claimid',index:'int_claimid asc, txt_item', editable:true, align:"center", width:50},
      {name:'dt_date',index:'dt_date', width:80, align:"center", editable:true, sorttype:"date"},
      {name:'int_reference',index:'int_reference', width:60, align:"center", editable:true, editoptions:{size:"20",maxlength:"30"}},
      {name:'cur_amount',index:'cur_amount', width:110, align:"right",editable:true, formatter:'currency', formatoptions:{decimalSeparator:".", thousandsSeparator: ",", decimalPlaces: 2, prefix: "RM ", suffix: "  "}},
      {name:'int_siteid',index:'int_siteid', width:100, align:"center", editable:true, edittype:"select", editoptions:{dataUrl:'form/populating.php'}},
      {name:'int_accid',index:'int_accid', width:50, align:"center", editable:true, sortable:false }    
          ],

  pager: '#pagerclaim', 
  rowNum:10,
  rowList:[10,20,30],
  height:'auto',
  sortname: 'int_claimdetailid',
  /*scrollOffset:0,*/ //scrollarea
  hidegrid: false,  
  sortorder: 'desc',
  viewrecords: true, //display the number of total records
  editurl:"form/claim_edit.php?mode=edit",
  loadtext: "tgh load tuk, jap lok…",
  rownumbers:true, // add row numbers on left side
  caption: '   Claim Detail',
 
 ondblClickRow: function(id){
     if(id && id!==lastSel){
       jQuery('#claim').restoreRow(lastSel);
       lastSel=id;
    }
    jQuery('#claim').editRow(id, true);
 }
 
}); /* end of jqgrid */

if (action == "new" || action == "edit" ) {
      
    jQuery("#claim").jqGrid('navGrid','#pagerclaim',{edit:false, add:false, view:false, del:true, search:false},
      {}, // use default settings for edit
      {}, // use default settings for add
      {url: 'form/claim_edit.php?mode=delete'},  // delete instead that del:false we need this
      /*{multipleSearch : true}, // enable the advanced searching*/
      {closeOnEscape:true}
      );  //end on navgrid
   }

function pickdates(id){ /* datepicker */
     jQuery("#"+id+"_dt_date","#claim").datepicker({dateFormat:"yy-mm-dd"}); }

include('../Connections/mycon.php');
include("../includes/JSON.php");
$json = new Services_JSON();
$apid=$_REQUEST['apid'];
$page = $_REQUEST['page'];
$limit = $_REQUEST['rows'];
$sidx = $_REQUEST['sidx'];
$sord = $_REQUEST['sord'];

if(!$sidx) $sidx =1;

$db = mysql_connect($hostname_mycon, $username_mycon, $password_mycon)
or die("Connection Error: " . mysql_error());
 
mysql_select_db($database_mycon) or die("Error conecting to db.");
 
$result = mysql_query("SELECT COUNT(int_claimdetailid) AS count FROM tbl_claimdetail WHERE int_claimid=".$apid."");
$row = mysql_fetch_array($result,MYSQL_ASSOC);
$count = $row['count'];

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

if ($page > $total_pages) $page=$total_pages;

$start = $limit*$page – $limit; // do not put $limit*($page – 1)
 
if($start <0) $start = 0;

// the actual query for the grid data
$SQL = "SELECT int_claimdetailid, txt_item, int_claimid, dt_date, int_reference, cur_amount, int_siteid, int_accid FROM tbl_claimdetail WHERE int_claimid=".$apid." ORDER BY $sidx $sord LIMIT $start, $limit";
$result = mysql_query( $SQL ) or die("Couldn t execute query.".mysql_error());

// query for GRAND total
$squely = "SELECT int_claimdetailid, txt_item, int_claimid, dt_date, int_reference, cur_amount, int_siteid, int_accid FROM tbl_claimdetail  WHERE int_claimid=".$apid."";
$resulto = mysql_query( $squely ) or die("Couldn t execute query.".mysql_error());

$responce->page = $page;
$responce->total = $total_pages;
$responce->records = $count;
$i=0;

while($row = mysql_fetch_array($result,MYSQL_ASSOC)) {
    $responce->rows[$i]['id']=$row[int_claimdetailid];
    $responce->rows[$i]['cell']=array($row[int_claimdetailid],$row[txt_item],$row[int_claimid],$row[dt_date],$row[int_reference],$row[cur_amount],$row[int_siteid],$row[int_accid]);
    $i++;
}

$amttot =0;
while($rowto = mysql_fetch_array($resulto,MYSQL_ASSOC)) {
    $amttot += $rowto[cur_amount];
}

$responce->userdata['cur_amount'] = $amttot;
$responce->userdata['int_reference'] = 'Totals:';
 
echo $json->encode($responce);

<?php
include('../Connections/mycon.php');
include("../includes/JSON.php");
$json = new Services_JSON();
 $apid=$_REQUEST['apid'];
$page = $_REQUEST['page'];
$limit = $_REQUEST['rows'];
$sidx = $_REQUEST['sidx'];
$sord = $_REQUEST['sord'];

if(!$sidx) $sidx =1;

$wh = "";
$searchOn = Strip($_REQUEST['_search']);
if($searchOn=='true') {
    $searchstr = Strip($_REQUEST['filters']);
    $wh= constructWhere($searchstr);
    //echo $wh;
}
function constructWhere($s){
    $qwery = "";
    //['eq','ne','lt','le','gt','ge','bw','bn','in','ni','ew','en','cn','nc']
    $qopers = array(
                  'eq'=>" = ",
                  'ne'=>" <> ",
                  'lt'=>" < ",
                  'le'=>" <= ",
                  'gt'=>" > ",
                  'ge'=>" >= ",
                  'bw'=>" LIKE ",
                  'bn'=>" NOT LIKE ",
                  'in'=>" IN ",
                  'ni'=>" NOT IN ",
                  'ew'=>" LIKE ",
                  'en'=>" NOT LIKE ",
                  'cn'=>" LIKE " ,
                  'nc'=>" NOT LIKE " );
    if ($s) {
        $jsona = json_decode($s,true);
        if(is_array($jsona)){
            $gopr = $jsona['groupOp'];
            $rules = $jsona['rules'];
            $i =0;
            foreach($rules as $key=>$val) {
                $field = $val['field'];
                $op = $val['op'];
                $v = $val['data'];
                if($v && $op) {
                    $i++;
                    // ToSql in this case is absolutley needed
                    $v = ToSql($field,$op,$v);
                    if ($i == 1) $qwery = " AND ";
                    else $qwery .= " " .$gopr." ";
                    switch ($op) {
                        // in need other thing
                        case 'in' :
                        case 'ni' :
                            $qwery .= $field.$qopers[$op]." (".$v.")";
                            break;
                        default:
                            $qwery .= $field.$qopers[$op].$v;
                    }
                }
            }
        }
    }
    return $qwery;
}
function ToSql ($field, $oper, $val) {
    // we need here more advanced checking using the type of the field – i.e. integer, string, float
    switch ($field) {
        case 'int_claimdetailid':
        case 'int_claimid':
            return intval($val);
            break;
        case 'cur_amount':

        case 'total':
            return floatval($val);
            break;
        default :
            //mysql_real_escape_string is better
            if($oper=='bw' || $oper=='bn') return "'" . addslashes($val) . "%'";
            else if ($oper=='ew' || $oper=='en') return "'%" . addcslashes($val) . "'";
            else if ($oper=='cn' || $oper=='nc') return "'%" . addslashes($val) . "%'";
            else return "'" . addslashes($val) . "'";
    }
}

// connect to the MySQL database server
$db = mysql_connect($hostname_mycon, $username_mycon, $password_mycon)
or die("Connection Error: " . mysql_error());

// select the database
mysql_select_db($database_mycon) or die("Error conecting to db.");

// calculate the number of rows for the query. We need this to paging the result
$result = mysql_query("SELECT COUNT(int_claimdetailid) AS count

FROM tbl_claimdetail WHERE int_claimid=".$apid."".$wh."");
$row = mysql_fetch_array($result,MYSQL_ASSOC);
$count = $row['count'];

// calculation of total pages for the query
if( $count >0 ) {
    $total_pages = ceil($count/$limit);
} else {
    $total_pages = 0;
}

// if for some reasons the requested page is greater than the total
// set the requested page to total page
if ($page > $total_pages) $page=$total_pages;

// calculate the starting position of the rows
$start = $limit*$page – $limit; // do not put $limit*($page – 1)
// if for some reasons start position is negative set it to 0
// typical case is that the user type 0 for the requested page
if($start <0) $start = 0;

// the actual query for the grid data
$SQL = "SELECT int_claimdetailid, txt_item, int_claimid, dt_date, int_reference, cur_amount, int_siteid, int_accid FROM tbl_claimdetail WHERE int_claimid=".$apid."".$wh." ORDER BY ".$sidx." ".$sord. " LIMIT ".$start." , ".$limit;
$result = mysql_query( $SQL ) or die("Couldn t execute query.".mysql_error());

// query for GRAND total
$squely = "SELECT int_claimdetailid, txt_item, int_claimid, dt_date, int_reference, cur_amount, int_siteid, int_accid FROM tbl_claimdetail  WHERE int_claimid=".$apid."".$wh."";
$resulto = mysql_query( $squely ) or die("Couldn t execute query.".mysql_error());

$responce->page = $page;
$responce->total = $total_pages;
$responce->records = $count;
$i=0;

while($row = mysql_fetch_array($result,MYSQL_ASSOC)) {
    $responce->rows[$i]['id']=$row[int_claimdetailid];
    $responce->rows[$i]['cell']=array($row[int_claimdetailid],$row[txt_item],$row[int_claimid],$row[dt_date],$row[int_reference],$row[cur_amount],$row[int_siteid],$row[int_accid]);
    $i++;
}

$amttot =0;
while($rowto = mysql_fetch_array($resulto,MYSQL_ASSOC)) {
    $amttot += $rowto[cur_amount];
}

$responce->userdata['cur_amount'] = $amttot;
$responce->userdata['int_reference'] = 'Totals:';

// return the formated data
echo $json->encode($responce);

?>