Forum

November 2nd, 2014
A A A
Avatar

Lost password?
Advanced Search

— Forum Scope —




— Match —





— Forum Options —





Minimum search word length is 3 characters - maximum search word length is 84 characters

The forums are currently locked and only available for read only access
sp_Feed Topic RSS sp_TopicIcon
Can't Add, Edit, Delete records in Database
17/02/2010
11:41
Avatar
richsoe
Member
Members
Forum Posts: 9
Member Since:
17/02/2010
sp_UserOfflineSmall Offline

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

17/02/2010
11:59
Avatar
tony
Sofia, Bulgaria
Moderator
Members

Moderators
Forum Posts: 7721
Member Since:
30/10/2007
sp_UserOfflineSmall Offline

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.

17/02/2010
12:38
Avatar
JiheL
France
Member
Members
Forum Posts: 19
Member Since:
02/12/2009
sp_UserOfflineSmall Offline

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

17/02/2010
12:38
Avatar
JiheL
France
Member
Members
Forum Posts: 19
Member Since:
02/12/2009
sp_UserOfflineSmall Offline
17/02/2010
15:46
Avatar
lupin
Member
Members
Forum Posts: 28
Member Since:
12/02/2010
sp_UserOfflineSmall Offline

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

17/02/2010
19:46
Avatar
mstone42
New Member
Members
Forum Posts: 2
Member Since:
17/02/2010
sp_UserOfflineSmall Offline

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

18/02/2010
03:50
Avatar
richsoe
Member
Members
Forum Posts: 9
Member Since:
17/02/2010
sp_UserOfflineSmall Offline

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!

19/02/2010
23:11
Avatar
joaohcoruripe
New Member
Members
Forum Posts: 1
Member Since:
19/02/2010
sp_UserOfflineSmall Offline

Hi,

you guys can help me to find a link with a PHP and .js example file  with navGrid search configuration?

I have a grid of database firebird and I can't do the search.

Sorry my English

Forum Timezone: Europe/Sofia

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.com

Moderators: tony: 7721, Rumen[Trirand]: 81

Administrators: admin: 66

Comments are closed.
Privacy Policy   Terms and Conditions   Contact Information