Forum


11:41

17/02/2010

How this jqGrid is actually performing search, add, edit, delete to DB? I can't get it working, I can only select.
If I add, edit, or delete, it just update the UI / gridview.
Here is my table:
CREATE TABLE `vip_queue` (
`id` INT(11) NOT NULL AUTO_INCREMENT,
`username` VARCHAR(64) DEFAULT NULL,
`group_id` SMALLINT(3) DEFAULT NULL,
PRIMARY KEY (`id`),
UNIQUE KEY `username` (`username`)
) ENGINE=INNODB AUTO_INCREMENT=65 DEFAULT CHARSET=utf8
Here is my Javascript:
<script type="text/javascript">
jQuery(document).ready(function(){
jQuery("#list").jqGrid({
url:'admin.vip.queue.process.php',
datatype: "json",
mtype: "POST",
colNames:['vip_id','username', 'group_id'],
colModel:[
{name:'vip_id', index:'vip_id', width:100, editable:false, editoptions:{readonly:true}, editrules:{required:false}},
{name:'username', index:'username', width:300, editable:true, edittype:'text'},
{name:'group_id', index:'group_id', width:200, editable:true, edittype:'text'}
],
pager: '#pagernav',
rowNum:50,
rowList:[10,50,100,200,500,1000],
sortname: 'vip_id',
sortorder: "asc",
viewrecords: true,
caption: "Manage VIP Queue",
editurl: "admin.vip.queue.process.php",
height: 400
});
jQuery("#list").jqGrid('navGrid','#pagernav',
{}, //options
{mtype:"POST",closeAfterEdit:true,reloadAfterSubmit:true}, // edit options
{mtype:"POST",closeAfterAdd:true,reloadAfterSubmit:true}, // add options
{mtype:"POST",reloadAfterSubmit:true}, // del options
{} // search options
);
});
</script>
Here is my php script:
require_once('class/db.php');
$db= new db();
$writeConn = $db->getWriteConnection();
$debug = false;
$page = $_POST['page']; // get the requested page
$limit = $_POST['rows']; // get how many rows we want to have into the grid
$sidx = $_POST['sidx']; // get index row – i.e. user click to sort
$sord = $_POST['sord']; // get the direction
if(!$sidx) $sidx =1;
$sql = "SELECT COUNT(id) as count FROM vip";
$result = $db->query($sql, $debug, $writeConn);
$row = mysql_fetch_array($result);
$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;
$sql = "SELECT id as 'vip_id', username, group_id FROM vip ORDER BY ".$sidx." ". $sord." LIMIT ".$start." , ".$limit;
$result = $db->query($sql, $debug, $writeConn);
$response->page = $page;
$response->total = $total_pages;
$response->records = $count;
$i = 0;
while($row = mysql_fetch_array($result))
{
$response->rows[$i]['id']=$row['vip_id'];
$response->rows[$i]['cell']=array($row['vip_id'],$row['username'],$row['group_id']);
$i++;
}
echo json_encode($response);
$db->close($writeConn);
Please help. Thx
11:59

Moderators
30/10/2007

Hello,
Please do not douple post in all forums!
For professional UI suites for Java Script and PHP visit us at our commercial products site - guriddo.net - by the very same guys that created jqGrid.
12:38

02/12/2009

Hello
I'm newbie in jqGrid use, but I think that the same url in url and editurl is not correct, as editurl is used to process add, edit and del operations, and your php script don't act for that.
I have found this script in this forum :
if($_GET[oper]=='sel'){
// ADDS NEW APP RECORD – I have 5 columns, and these are the titles
} elseif($_POST[oper]=='add') {
$fname = $_POST['fname'];
$lname = $_POST['lname'];
$userid = $_POST['userid'];
$start = $_POST['start'];
$note = $_POST['note'];
$ins = "INSERT INTO appid (fname,lname,userid,start,note) VALUES ($fname,$lname,$userid,$start,$note)";
@mysql_query($ins) or die("failed");
// MODIFIES USER RECORD
} elseif($_POST[oper]=='edit') {
$id = $_POST['id'];
$fname = $_POST['fname'];
$lname = $_POST['lname'];
$userid = $_POST['userid'];
$start = $_POST['start'];
$note = $_POST['note'];
$upd = "UPDATE appid SET (fname,lname,userid,start,note) VALUES ($fname,$lname,$userid,$start,$note) WHERE id=$id";
@mysql_query($upd) or die("failed");
// DELETES USER RECORD AND ASSOCIATED ACCESS RELATIONSHIPS
} elseif($_POST[oper]=='del') {
$id = $_POST['id'];
$SQL = "DELETE FROM appid WHERE id=$id";
$res = @mysql_query($SQL) or die("failed");
}
}
I hope this will help you.
Cheers
JiheL
PS sorry for my bad english
15:46

12/02/2010

JiheL said:
Hello
I'm newbie in jqGrid use, but I think that the same url in url and editurl is not correct, as editurl is used to process add, edit and del operations, and your php script don't act for that.
I have found this script in this forum :
// FETCHES THE 'USERS' TABLE DATA
if($_GET[oper]=='sel'){// ADDS NEW APP RECORD – I have 5 columns, and these are the titles
} elseif($_POST[oper]=='add') {
$fname = $_POST['fname'];
$lname = $_POST['lname'];
$userid = $_POST['userid'];
$start = $_POST['start'];
$note = $_POST['note'];
$ins = "INSERT INTO appid (fname,lname,userid,start,note) VALUES ($fname,$lname,$userid,$start,$note)";
@mysql_query($ins) or die("failed");// MODIFIES USER RECORD
} elseif($_POST[oper]=='edit') {$id = $_POST['id'];
$fname = $_POST['fname'];
$lname = $_POST['lname'];
$userid = $_POST['userid'];
$start = $_POST['start'];
$note = $_POST['note'];$upd = "UPDATE appid SET (fname,lname,userid,start,note) VALUES ($fname,$lname,$userid,$start,$note) WHERE id=$id";
@mysql_query($upd) or die("failed");// DELETES USER RECORD AND ASSOCIATED ACCESS RELATIONSHIPS
} elseif($_POST[oper]=='del') {$id = $_POST['id'];
$SQL = "DELETE FROM appid WHERE id=$id";
$res = @mysql_query($SQL) or die("failed");
}
}I hope this will help you.
Cheers
JiheL
PS sorry for my bad english
Hi ,
Do you have any example of the response code from the editurl? I having hard time on it as well as I can't find any examples on what it's expecting after a sucess/failed submission.
hopefully someone will add it on wiki, it should be like:
1) after submission does it suceed or failed?
2) what is the expected response structure of json/xml from the server.
3) How to load/insert on on grid incase of add/edit, refresh in grid in case of delete.
Thank you in advance,
lupin
19:46

17/02/2010

I was having this same problem and just discovered a solution.
Here's the code for my grid:
$("#userlist").jqGrid({
…
url:'userdata.php',
datatype: "json",
colNames:['User','Password'],
colModel:[
{name:'userid',index:'userid', key:true,editable:true,...},
{name:'pswd',index:'pswd',hidden:true,editable:true,edittype:"password",editrules:{edithidden:true},...}
],
editurl:"userdata_edit.php" ,
…
});
$("#user_edit").click(function(){
var uid = jQuery("#userlist").jqGrid('getGridParam','selrow');
var options = { modal: true, reloadAfterSubmit:true, … };
if(uid != null ) jQuery("#userlist").jqGrid('editGridRow',uid,options);
else alert("Please Select Row");
});
$("#user_add").click(function(){
var options = { modal: true, reloadAfterSubmit:true, … };
jQuery("#userlist").jqGrid('editGridRow',"new",options);
});
$("#user_del").click(function(){
var uid = jQuery("#userlist").jqGrid('getGridParam','selrow');
var options = { modal: true, reloadAfterSubmit:true, … };
if(uid != null ) jQuery("#userlist").jqGrid('delGridRow', uid, options );
else alert("Please Select Row");
});
There may be a simpler way to handle the various button definitions, but this works for me.
And here's is the php script specified in the 'editurl' parameter of the grid:
<?php
// value is one of [ add, edit, del ] – automatically provided by jqgrid
$action = (isset($_REQUEST['oper']) ? trim($_REQUEST['oper'] ): null);
// only valid for oper in [ edit, del ] – automatically provided by jqgrid
$id = (isset($_REQUEST['id']) ? trim($_REQUEST['id'] ): null);
// all the other fields are only included if oper in [ edit, add ]
$userid = (isset($_REQUEST['userid']) ? trim($_REQUEST['userid'] ): null);
$pswd = (isset($_REQUEST['pswd']) ? trim($_REQUEST['pswd'] ): null);
switch ($action) {
case "add" : // the "id" value = "_empty", which is useless to us
$sql = "INSERT INTO Users " …
db_query($sql, $userid, $pswd);
break;
case "del" : //need to use provided "id" value, since no other values are passed in
$sql = "DELETE FROM Administration " …
db_query($sql, $id);
break;
case "edit" :
$sql = "UPDATE Administration SET " …
db_query($sql, $pswd, $id);
break;
}
// generate json data object to show result of query
$data->page = 1;
$data->total = 1;$data->records = 1;
$data->rows[0]['id']=$SQL_ERRNO;
$data->rows[0]['cell']=array($SQL_ERRNO, $SQL_ERROR, $sql);
print(json_encode($data));
?>
I don't know if the json data I'm displaying for the result is what jqgrid is expecting, but that's my next task to figure out – I'll try to post my findings if someone else doesn't post something first.
I hope this helps.
-Mitch
03:50

17/02/2010

Thanks guys. I finally got it working. There is also one good example (with codes) can be downloaded from this blog http://suddendevelopment.com/?p=38.
The idea is the same as the sample codes you guys posted above. You will need to write your own CRUD codes. This is somehow different from the commercial version, because it's simpler, the library handles that.
Anyways, the blog above should help you figure out how the CRUD works.
Cheers!
Most Users Ever Online: 715
Currently Online:
55 Guest(s)
Currently Browsing this Page:
1 Guest(s)
Top Posters:
OlegK: 1255
markw65: 179
kobruleht: 144
phicarre: 132
YamilBracho: 124
Renso: 118
Member Stats:
Guest Posters: 447
Members: 11373
Moderators: 2
Admins: 1
Forum Stats:
Groups: 1
Forums: 8
Topics: 10592
Posts: 31289
Newest Members:
, razia, Prankie, psky, praveen neelam, greg.valainis@pa-tech.comModerators: tony: 7721, Rumen[Trirand]: 81
Administrators: admin: 66