CODEWITHSUNDEEP
jqgrid
Qualliarys
Qualliarys

Reputation: 143

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

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 :

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.

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

Upvotes: 2

Views: 4462

Answers (2)

Andy
Andy

Reputation: 424

Just ran into this problem as well and have a different solution that someone may find useful.

I really didn't want to have to change my table structure for jqGrid, so I created a composite key instead.

My data's primary key was a composite of an integer and a date (it's a table of monthly sales targets, so the dates had wide separation). What I did was to invent an ID for the json response that was a combination of the two pieces:

$response->rows[$line]['id'] = $id . "*" . $date;

Then when edited data posts back from jqGrid to the page, the id passed back is indeed something like "79*2014-03-01", which is easy to split back into the parts I needed for accessing the data for update.

Upvotes: 1

Oleg
Oleg

Reputation: 222007

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"}

Upvotes: 7

Related Questions