.Hello,
I have made a grid with subgrid and everything works except for I could not get the edit function to work with my subgrid. I am able to get the edit dialog open, but after I submit my edits, nothing changed. One thing I can think of is the primary key I set (“$grid->setPrimaryKeyId(‘cosmic’);”) in both main grid and the subgrid is actually not unique in main grid, but it is unique in subgrid table. I figure since I am only editting the subgrid table where the primary key is unique, it should be fine. Maybe I’m wrong? If this is indeed the problem, how can I handle this situation? The “cosmic” column is the column that links the main grid and the subgrid. Thanks!
The code is pasted below:
#grid.php (this is the main grid)
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139 140 141 142 143 144 145 146 147 148 149 150 151 152 153 154 155 156 157 158 159 160 161 162 163 164 165 166 167 168 169 170 171 172 173 174 175 176 177 178 179 180 181 182 183 184 185 186 187 188 189 190 191 192 193 194 195 196 |
<?php require_once 'jq-config.php'; // include the jqGrid Class require_once "../../Guriddo_Suito_PHP_4_8_2_6m/php/PHPSuito/jqGrid.php"; // include the PDO driver class require_once "../../Guriddo_Suito_PHP_4_8_2_6m/php/PHPSuito/DBdrivers/jqGridPdo.php"; // Connection to the server $conn = new PDO(DB_DSN,DB_USER,DB_PASSWORD); //get $sample_name from main.php if(!session_id()) session_start(); $sample = $_SESSION['sample']; //get filter criteria if(isset($_SESSION["frequencyFrom"]) and $_SESSION["frequencyFrom"] != "") $frequencyFrom = $_SESSION["frequencyFrom"]; else $frequencyFrom = 0; if(isset($_SESSION["frequencyTo"]) and $_SESSION["frequencyTo"] != "") $frequencyTo = $_SESSION["frequencyTo"]; else $frequencyTo = 100; if(isset($_SESSION["depthFrom"]) and $_SESSION["depthFrom"] != "") $depthFrom = $_SESSION["depthFrom"]; else $depthFrom = 0; if(isset($_SESSION["depthTo"]) and $_SESSION["depthTo"] != "") $depthTo = $_SESSION["depthTo"]; else $depthTo = 2147483648; if(isset($_SESSION["genotype"])) $genotype = $_SESSION["genotype"]; else $genotype = array("Heterozygous", "Homozygous", "No Call"); if(isset($_SESSION["coding"])) $coding = $_SESSION["coding"]; else $coding = array("synonymous", "non-synonymous", "non-coding", "other", "na"); //echo "frequency low is " ; //echo $frequencyFrom; //echo "frequency high is " ; //echo $frequencyTo; // include the mysqli driver class //require_once "../../Guriddo_Suito_PHP_4_8_2/php/PHPSuito/DBdrivers/jqGridMysqli.php"; // Connection to the server //$conn = @mysqli_connect(servername, username, password, db_name); // Create the jqGrid instance $grid = new jqGridRender($conn); if(in_array('na',$coding)) $grid->SelectCommand = " select t1.*, t2.cDNA, t2.protein, IFNULL(t2.coding, 'na') as coding, t2.gene, t3.id as dbsnp, t3.property as property_dbSNP, t4.lastName, t4.firstName, t5.property as property_curated, t5.note, t6.occurance_count from gene50 as t1 left join cosmic as t2 on t1.chr = t2.chr and t1.pos = t2.pos and t1.ref = t2.ref and t1.alt = t2.alt left join dbsnp as t3 on t1.chr = t3.chr and t1.pos = t3.pos and t1.ref = t3.ref and t1.alt = t3.alt left join gene50_sample as t4 on t1.report_no = t4.report_no and t1.barcode = t4.barcode left join gene50_curation as t5 on t1.cosmic = t5.cosmic left join (select cosmic, count(*) as occurance_count from gene50 where (genotype = 'Homozygous' or genotype = 'Heterozygous') and cosmic != '---' group by cosmic) as t6 on t1.cosmic = t6.cosmic where (order_no = '$sample' or pathology_no = '$sample' or lastName = '$sample') and frequency between '$frequencyFrom' and '$frequencyTo' and depth between '$depthFrom' and '$depthTo' and genotype in ('".implode("','", array_values($genotype))."') and (coding in ('".implode("','", array_values($coding))."') or coding is null) group by uid, cDNA, protein"; else $grid->SelectCommand = " select distinct t1.*, t2.cDNA, t2.protein, IFNULL(t2.coding, 'na') as coding, t2.gene, t3.id as dbsnp, t3.property as property_dbSNP, t4.lastName, t4.firstName, t5.property as property_curated, t5.note, t6.occurance_count from gene50 as t1 left join cosmic as t2 on t1.chr = t2.chr and t1.pos = t2.pos and t1.ref = t2.ref and t1.alt = t2.alt left join dbsnp as t3 on t1.chr = t3.chr and t1.pos = t3.pos and t1.ref = t3.ref and t1.alt = t3.alt left join gene50_sample as t4 on t1.report_no = t4.report_no and t1.barcode = t4.barcode left join gene50_curation as t5 on t1.cosmic = t5.cosmic left join (select cosmic, count(*) as occurance_count from gene50 where (genotype = 'Homozygous' or genotype = 'Heterozygous') and cosmic != '---' group by cosmic) as t6 on t1.cosmic = t6.cosmic where (order_no = '$sample' or pathology_no = '$sample' or lastName = '$sample') and frequency between '$frequencyFrom' and '$frequencyTo' and depth between '$depthFrom' and '$depthTo' and genotype in ('".implode("','", array_values($genotype))."') and coding in ('".implode("','", array_values($coding))."') group by uid, cDNA, protein"; //echo $grid->SelectCommand; // set the ouput format to json $grid->dataType = 'json'; //define primary key $grid->setPrimaryKeyId('cosmic'); // Let the grid create the model $grid->setColModel(); // Set the url from where we obtain the data $grid->setUrl('grid.php'); // Set grid caption using the option caption $grid->setGridOptions(array( "caption"=>"Somatic mutation results", "rowNum"=>20, "sortname"=>"cosmic", "height"=>"500", "rowList"=>array(10,20,50), "multiselect"=>true )); // Change some property of the field(s) $grid->setColProperty("uid", array("width"=>40, "editable"=>false)); $grid->setColProperty("chr", array("width"=>60, "editable"=>false)); $grid->setColProperty("pos", array("width"=>60, "editable"=>false) ); $grid->setColProperty("cosmic", array("width"=>80, "editable"=>false)); $grid->setColProperty("ref", array("width"=>60, "editable"=>false)); $grid->setColProperty("alt", array("width"=>60, "editable"=>false)); $grid->setColProperty("genotype", array("width"=>60, "editable"=>true)); $grid->setColProperty("depth", array("width"=>60, "editable"=>false)); $grid->setColProperty("altCount", array("width"=>60, "editable"=>false)); $grid->setColProperty("frequency", array("width"=>60, "editable"=>false)); $grid->setColProperty("report_no", array("width"=>60, "editable"=>false)); $grid->setColProperty("barcode", array("width"=>60, "editable"=>false)); $grid->setColProperty("cDNA", array("width"=>60, "editable"=>false)); $grid->setColProperty("protein", array("width"=>100, "editable"=>false)); $grid->setColProperty("coding", array("width"=>100, "editable"=>false)); $grid->setColProperty("gene", array("width"=>60, "editable"=>false)); $grid->setColProperty("dbsnp", array("width"=>60, "editable"=>false)); $grid->setColProperty("property_dbSNP", array("width"=>60, "editable"=>false)); $grid->setColProperty("lastName", array("width"=>60, "editable"=>false)); $grid->setColProperty("firstName", array("width"=>60, "editable"=>false)); $grid->setColProperty("property_curated", array("width"=>60)); $grid->setColProperty("note", array("width"=>60)); $grid->setColProperty("occurance_count", array("width"=>60, "editable"=>false)); //set subgrid $grid->setSubGridGrid("curation_grid.php"); //Show server error $grid->showError = true; //add output table $grid->table = 'gene50'; // Enable navigator $grid->navigator = true; $grid->toolbarfilter = true; $grid->csvsep=","; $grid->setNavOptions('navigator', array("pdf"=>true,"add"=>false,"edit"=>false,"del"=>false,"view"=>false)); $grid->setNavOptions('search', array( "multipleGroup"=>true, "showQuery"=>true )); $grid->setNavOptions('edit',array("closeAfterEdit"=>true,"editCaption"=>"Update cosmic id","bSubmit"=>"Update")); //get selected rows $custom = <<<CUSTOM jQuery("#getselected").click(function(){ var selr = jQuery('#grid').jqGrid('getGridParam','selarrrow'); if(selr) alert(selr); }); CUSTOM; $grid->setJSCode($custom); // Run the script $grid->renderGrid('#grid','#pager', true, null, null, true,true,true); ?> |
#curation_grid.php (this is the subgrid)
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 |
<?php require_once 'jq-config.php'; // include the jqGrid Class require_once "../../Guriddo_Suito_PHP_4_8_2_6m/php/PHPSuito/jqGrid.php"; // include the PDO driver class require_once "../../Guriddo_Suito_PHP_4_8_2_6m/php/PHPSuito/DBdrivers/jqGridPdo.php"; // Connection to the server $conn = new PDO(DB_DSN,DB_USER,DB_PASSWORD); // Tell the db that we use utf-8 $conn->query("SET NAMES utf8"); // Get the needed parameters passed from the main grid // By default we add to postData subgrid and rowid parameters in the main grid $oper = jqGridUtils::GetParam("oper"); $subtable = jqGridUtils::GetParam("subgrid"); $rowid = jqGridUtils::GetParam("rowid"); if(!$rowid) die("Missed parameters"); // Create the jqGrid instance $grid = new jqGridRender($conn); $grid->SelectCommand = " select * from gene50_curation where cosmic = ?"; //echo $grid->SelectCommand; // set the ouput format to json $grid->dataType = 'json'; // set primary key $grid->setPrimaryKeyId('cosmic'); // Let the grid create the model $grid->setColModel(null,array($rowid)); // Set the url from where we obtain the data $grid->setUrl('curation_grid.php'); /*$onselrow = <<< ONSELROW function(rowid, selected) { if(rowid && rowid !== lastSelection) { $(this).jqGrid('restoreRow', lastSelection); $(this).jqGrid('editRow', rowid, true); lastSelection = rowid; } } ONSELROW;*/ //$grid->setGridEvent('onSelectRow', $onselrow); $grid->table = 'gene50_curation'; // Set grid caption using the option caption $grid->setGridOptions(array( "rowNum"=>1, "height"=>"50", "width"=>"500", "postData"=>array("subgrid"=>$subtable,"rowid"=>$rowid) )); // Change some property of the field(s) $grid->setColProperty("cosmic", array("width"=>40, "editable"=>false)); $grid->setColProperty("property", array("width"=>60, "editable"=>true)); $grid->setColProperty("note", array("width"=>60, "editable"=>true)); //Show server error $grid->showError = true; $grid->navigator = true; $grid->setNavOptions('navigator', array("excel"=>false,"add"=>false,"edit"=>true,"del"=>false,"view"=>false)); // Enjoy $subtable = $subtable."_t"; $pager = $subtable."_p"; $grid->renderGrid($subtable,$pager, true, null, array(&$rowid), true,true); ?> |
Hello,
If you look carefully into the editing example of subgrid (which you use as base) you will note that the primary key in subgrid differ from the parameter key in selectCommand.
You say that the cosmic key is unique in subgrid. If this is the case this mean that you can query only one record at a time and your model is many to one.
I recommend you to add additional auto increment key in subgrid table like by example curration_id. In this case the code can look like this:
|
1 2 3 4 5 6 7 |
$grid->SelectCommand = "select * from gene50_curation where cosmic = ?"; //echo $grid->SelectCommand; // set the ouput format to json $grid->dataType = 'json'; // set primary key $grid->setPrimaryKeyId('curration_id'); |
Kind Regards,
Will
Guriddo Support Team
Thanks for the reply. I tried your solution by adding an AI column in the database and use that as the primary key in subgrid. The grid then becomes nonreponsive.
There is one thing that I am not sure. In your subgrid demo, you never set the primarykey for the main grid. But for me, the subgrid will only display the record if I set the primary key in the main grid to be “cosmic” by doing
“$grid->setPrimaryKeyId(‘cosmic’);”
Otherwise the subgrid is empty. How does the subgrid know which record to show? The select command for the subgrid is
select * from gene50_curation where cosmic = ?
I do not understand what the “?” refers to. I though this has something to do with the main grid row id, but how is the main grid row id defined?
BTW, how did you put the code in the code window? That is much easier to read.
Copyright 2014 TriRand LtdAll Rights ReservedRSS
Back to Top
