This example demostrates how we can manipulate data at
client side. To synchronize the manipulation on server
we must write custom code.
Get data from selected row
Delete row 12
Update amounts in row 11
Add row with id 99
HTML
<![CDATA[
...
<table id="list5" class="scroll" cellpadding="0" cellspacing="0"></table>
<div id="pager5" class="scroll" style="text-align:center;"></div>
<table id="list5" class="scroll" cellpadding="0" cellspacing="0"></table>
<div id="pager5" class="scroll" style="text-align:center;"></div>
<br />
<a href="#" id="a1">Get data from selected row</a>
<br />
<a href="#" id="a2">Delete row 2</a>
<br />
<a href="#" id="a3">Update amounts in row 1</a>
<br />
<a href="#" id="a4">Add row with id 99</a>
<script src="manipex.js" type="text/javascript"> </script>
<br />
]]>
Java Scrpt code
<![CDATA[
...
jQuery("#list5").jqGrid({
url:'server.php?q=2',
datatype: "json",
colNames:['Inv No','Date', 'Client', 'Amount','Tax','Total','Notes'],
colModel:[
{name:'id',index:'id', width:55},
{name:'invdate',index:'invdate', width:90},
{name:'name',index:'name', width:100},
{name:'amount',index:'amount', width:80, align:"right"},
{name:'tax',index:'tax', width:80, align:"right"},
{name:'total',index:'total', width:80,align:"right"},
{name:'note',index:'note', width:150, sortable:false}
],
rowNum:10,
rowList:[10,20,30],
imgpath: gridimgpath,
pager: jQuery('#pager5'),
sortname: 'id',
viewrecords: true,
sortorder: "desc",
caption:"Simple data manipulation",
editurl:"someurl.php"
}).navGrid("#pager5",{edit:false,add:false,del:false});
jQuery("#a1").click( function(){
var id = jQuery("#list5").getGridParam('selrow');
if (id) {
var ret = jQuery("#list5").getRowData(id);
alert("id="+ret.id+" invdate="+ret.invdate+"...");
} else { alert("Please select row");}
});
jQuery("#a2").click( function(){
var su=jQuery("#list5").delRowData(12);
if(su) alert("Succes. Write custom code to delete row from server"); else alert("Allready deleted or not in list");
});
jQuery("#a3").click( function(){
var su=jQuery("#list5").setRowData(11,{amount:"333.00",tax:"33.00",total:"366.00",note:"<img src='images/user1.gif'/>"});
if(su) alert("Succes. Write custom code to update row in server"); else alert("Can not update");
});
jQuery("#a4").click( function(){
var datarow = {id:"99",invdate:"2007-09-01",name:"test3",note:"note3",amount:"400.00",tax:"30.00",total:"430.00"};
var su=jQuery("#list5").addRowData(99,datarow);
if(su) alert("Succes. Write custom code to add data in server"); else alert("Can not update");
});
]]>
PHP with MySQL
<![CDATA[
...
$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, $dbpassword)
or die("Connection Error: " . mysql_error());
mysql_select_db($database) or die("Error conecting to db.");
$result = mysql_query("SELECT COUNT(*) AS count FROM invheader a, clients b WHERE a.client_id=b.client_id");
$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)
$SQL = "SELECT a.id, a.invdate, b.name, a.amount,a.tax,a.total,a.note FROM invheader a, clients b WHERE a.client_id=b.client_id ORDER BY $sidx $sord LIMIT $start , $limit";
$result = mysql_query( $SQL ) 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[id];
$responce->rows[$i]['cell']=array($row[id],$row[invdate],$row[name],$row[amount],$row[tax],$row[total],$row[note]);
$i++;
}
echo json_encode($responce);
...
]]>