jqGrid : "All in One" approach width jqGridEdit Class > how to set a composite primary key ?

Question

Hello,

How to set a composite primary key for a "All in One" approach (grid defined in JS file, and data using jqGridEdit Class in php file) ? Please, for me a composite primary key of a table T, is a elementary primary key that is defined with some fields belong to this table T !

Here is my test, but i get no data and cannot use the CRUD operations :

• In my JS file i have this lines code:

  ...
  colModel:[
     {name:"index",index:"index",label:"index"}, // <= THAT'S JUST THE INDEX OF MY TABLE         
     {name:"user",index:"user",label:"user",key:true}, // <= A PART OF MY COMPOSITE PRIMARY KEY
     {name:"pwd",index:"pwd",label:"pwd",key:true},    // <= A PART OF MY COMPOSITE PRIMARY KEY
     {name:"state",index:"state",label:"state",key:true}, // <= A PART OF MY COMPOSITE PRIMARY KEY
     ... <= AND SO ON
     url:"mygrid_crud.php",
     datatype:"json",
     jsonReader:{repeatitems:false},
     editurl: "mygrid_crud.php",
     prmNames:{"id":"index"} // <= WHAT I NEED TO WRITE HERE ???
    ...
  

• In my php file (mygrid_crud.php) :

  ...
  $grid = new jqGridEdit($conn);
  $query = "SELECT * FROM mytable WHERE user='$user' and pwd='$pwd' and state='$state'..."; // <= SELECT * it's ok or i need to specify all fields i need ? 
  $grid->SelectCommand = $query;
  $grid->dataType = "json";
  $grid->table = 'mytable';
  $grid->setPrimaryKeyId('index'); // <= WHAT I NEED TO WRITE HERE ???
  ...
  $grid->editGrid();
  

Please, say me what is wrong, and how to do to set a composite primary key in this approach !?

Thank you so much for tour responses.

Answer 1

first of all it is something strange in the definition of colModel which you post. For example, the text like colModel":[ is definitively wrong. It is difficult to say something about the code if it contain a lot of errors (probably errors of formating, but a reader don't know your original code). If you have problems to format the data, just post what you have and other people will be able reformat your question.

Now about your main question. Usage of key:true in more as one row is wrong way. On http://www.trirand.com/jqgridwiki/doku.php?id=wiki:colmodel_options you can find in the description of key parameter following:

In case if there is no id from server, this can be set as as id for the unique row id. Only one column can have this property. If there are more than one key the grid finds the first one and the second is ignored.

jqGrid need have an id to distinguish one row of grid from another one. You can fill jqGrid with any ids like 1,2,3 etc which are not your real ids. If all columnt which have information which compose a composite primary key have option editable:true in colModel then the values from these columns will be send to the server at all edit operation and you will be able to build the corresponding SELECT statement in your mygrid_crud.php file.

If you want not display some columns which you need only to build the composite key you can use for this columns following options in the colModel

hidden: true, editable: true, editrules: { edithidden: false }, hidedlg: true

It will make columns invisible for user, but the data will be send to server at all edit operations.

One more small remarks an the end. Don't set default values in the . For example {name:"index", index:"index", label:"index"} has all three fields the same. So you can reduce all to {name:"index"}

Answer 2

So Oleg, i understand that the only solution to my problem is to set my "composite primary key" with hidden properties, as you suggest. And for sure, i'll use the unique index of my table for the index of my grid!

Thank you so much again ! Best regards, Qualliarys

Answer 3

So, here is the solution i get after a lot of efforts ;-(

It's maybe not the optimal solution, but add, edit and dell operations work well !!!

Here is an example of a table definition with a COMPOSITE PRIMARY KEY :

CREATE TABLE `chat` (
  `number` int(11) NOT NULL AUTO_INCREMENT,

  `user` varchar(30) NOT NULL,
  `pwd` varchar(100) NOT NULL,
  `subject` varchar(100) NOT NULL,
  `time` datetime NOT NULL,
  `recipient` varchar(100) NOT NULL,

  `message` varchar(1000) DEFAULT NULL,

  PRIMARY KEY (`user`, `pwd`, `subject`, `time`, `recipient`),
  UNIQUE KEY `number` (`number`)
) 

So to manage such table with a grid, i do as follows.

In my HTML file, i have this lines of code :

...
<table id='grid_chat'></table>
<div id='pager_chat'></div>
...

And in my PHP file, i have this lines of code :

 ...
$grid = new jqGridRender($conn);
…
if($oper=="edit"){…}
elseif($oper=="del"){…}
…
$grid->setPrimaryKeyId('number');
…
// here i get the primary key for the new message i want to add 
$chat = $_SESSION["chat"];
$user = $chat["user"];
$pwd = $chat["pwd"];
$subject = $chat["subject"];
…
$query = "SELECT * FROM chat WHERE user='$user' and pwd='$pwd' and subject='$subject'...”;
$grid->SelectCommand = $query;
…
// for the UNIQUE KEY (used for edit and dell operations)
$grid->setColProperty("number", array("hidden"=>true)); 

// for the COMPOSITE PRIMARY KEY (used for add operation)
$grid->setColProperty("user", array("hidden"=>true,"editoptions"=>array("value"=> $user),"editrules"=>array("required"=>true)));
$grid->setColProperty("pwd", array("hidden"=>true,"editoptions"=>array("value"=> $pwd),"editrules"=>array("required"=>true)));
$grid->setColProperty("subject", array("hidden"=>true,"editoptions"=>array("value"=> $subject),"editrules"=>array("required"=>true)));
$grid->setColProperty("time", array("hidden"=>true,"editoptions"=>array("value"=> $time),"editrules"=>array("required"=>true)));
$grid->setColProperty("recipient", array("hidden"=>true,"editoptions"=>array("value"=> $recipient),"editrules"=>array("required"=>true)));

// for the message
$grid->setColProperty("message", array("classes"=>"multiligne","label"=>"Message","width"=>400,"edittype"=>"textarea","editrules"=>array("required"=>true)));

// Set a new user datetime format using PHP convensions (for the php time variable)
$grid->setUserTime('Y-m-d H:i:s');
…

It's not the "All in One" approach, but it works well !!

If somebody see something to optimize, please tell me back !!!

Regards,

Qualliarys