Oh, I see – The ContactDate field in the pivot table is just text, so that’s why the ‘between’ oper didn’t work. I changed the oper to ‘bw’ (begins with) and pass the year from the drop-down and it works perfectly.
All done! Thanks again for your help.
Ken
Based on your demo and comments I modified my scripts and incorporated the function in your demo. That is almost working 100% – I just have one part that doesn’t seem to be working.
I added a ‘between’ search for the ContactDate comlum and it doesn’t seem to be working.
Here is my updated code. Any suggestions are appreciated.
index.php
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 |
<?php include "sqlPDO.php"; $db = opendb('MySQL','tinicum'); $sql = " select LpcDescription from tblLpcType order by LpcID"; $LPChtml = array(); foreach ($db->query($sql) as $row) { $LPChtml[strval($row['LpcDescription'])] = $row['LpcDescription']; } $sql = " select distinct year(ContactDate) year from tblContactNotes where year(ContactDate) > 0 order by 1"; $Yearhtml = array(); foreach ($db->query($sql) as $row) { $Yearhtml[strval($row['year'])] = $row['year']; } $sql = " select distinct month(ContactDate) monthnum, date_format(ContactDate,'%M') monthname from tblContactNotes where month(ContactDate) > 0 order by 1"; $Monthhtml = array(); foreach ($db->query($sql) as $row) { $Monthhtml[strval($row['monthnum'])] = $row['monthname']; } ?> <!DOCTYPE html> <html> <head> <title>Contact Notes</title> <link rel="stylesheet" type="text/css" media="screen" href="/jqSuite/css/jquery-ui.css" /> <link rel="stylesheet" type="text/css" media="screen" href="/jqSuite/css/trirand/ui.jqgrid.css" /> <link rel="stylesheet" type="text/css" media="screen" href="/jqSuite/css/ui.multiselect.css" /> <link rel="stylesheet" type="text/css" media="screen" href="/css/mtabs.css" /> <style type="text"> html, body { margin: 0; /* Remove body margin/padding */ padding: 0; overflow: hidden; /* Remove scroll bars on browser window */ font-size: 100%; } .ui-jqgrid { font-size: 14px; } .ui-jqgrid .ui-jqgrid-btable tbody tr.jqgrow td { white-space: normal !important; height: auto; } .mylongdata { height: 190px; overflow-y: auto; } </style> <script src="/jqSuite/js/jquery.min.js" type="text/javascript"></script> <script src="/jqSuite/js/trirand/i18n/grid.locale-en.js" type="text/javascript"></script> <script src="/jqSuite/js/trirand/jquery.jqGrid.min.js" type="text/javascript"></script> <!-- Files for the export ----------------------> <script type="text/javascript" language="javascript" src="/js/pivotExport/pdfmake.min.js"></script> <script type="text/javascript" language="javascript" src="/js/pivotExport/vfs_fonts.js"></script> <script type="text/javascript" language="javascript" src="/js/pivotExport/jszip.min.js"></script> <script type="text/javascript"> $.jgrid.no_legacy_api = true; $.jgrid.useJSON = true; $.jgrid.defaults.width = "700"; </script> <script src="/jqSuite/js/jquery-ui.min.js" type="text/javascript"></script> <script> var LPChtml = <?php print(json_encode($LPChtml)); ?>; var Yearhtml = <?php print(json_encode($Yearhtml)); ?>; var Monthhtml = <?php print(json_encode($Monthhtml)); ?>; var postData = ''; $(document).ready(function(){ $.each(LPChtml, function(val, text) { // console.log("val: "+val+" text: "+text); $('#LPC').append($("<option></option>").attr("value",val).text(text)); }); $.each(Yearhtml, function(val, text) { // console.log("val: "+val+" text: "+text); $('#year').append($("<option></option>").attr("value",val).text(text)); }); $.each(Monthhtml, function(val, text) { // console.log("val: "+val+" text: "+text); $('#month').append($("<option></option>").attr("value",val).text(text)); }); /***************************************************************************************************************************************/ function buildCustomSearch( rule_arr, group ){ if(group === undefined) { group = "AND"; } var ruleGroup = ""; if(Array.isArray(rule_arr) && rule_arr.length) { ruleGroup = "{\"groupOp\":\"" + group + "\",\"rules\":["; var gi=0; $.each(rule_arr,function(i,n){ if (gi > 0) {ruleGroup += ",";} ruleGroup += "{\"field\":\"" + n.name + "\","; ruleGroup += "\"op\":\"" + n.oper + "\","; ruleGroup += "\"data\":\"" + n.val.replace(/\\/g,'\\\\').replace(/\"/g,'\\"') + "\"}"; gi++; }); ruleGroup += "]}"; console.log(ruleGroup); } return ruleGroup; } var grid = $("#grid12"); $("#do_search").on('click',function(){ // console.log("Search was clicked - val is "+$("#year").val()); if ($('#Year') != '%') { ys = $('#year').val()+"-01-01"; ye = $('#year').val()+"-12-31"; } // console.log(ys+"..."+ye); var my_fld=[]; /* *opts : ['eq','ne', 'lt', 'le', 'gt', 'ge', 'nu', 'nn', 'in', 'ni','bw', 'bn', 'ew', 'en', 'cn', 'nc'], */ my_fld.push({ name: "LpcDescription", val : $("#LPC").val(), oper:"eq" }); my_fld.push({ name: "ContactDate", val: ys+"..."+ye, oper: "bt" }); // console.log(my_fld); var rule = buildCustomSearch( my_fld, "AND"); grid.setGridParam({postData:{filters:rule}, search:true}).trigger("reloadGrid"); }); /***************************************************************************************************************************************/ }); </script> </head> <body> <div> <form action="#"> <label for="LPC">LPC:</label> <select id="LPC" name="LPC"> <option value='%'>All</option> </select> <label for="year">Year:</label> <select id="year" name="year"> <option value='%'>All</option> </select> <label for="year">Month:</label> <select id="month" name="month"> <option value='%'>All</option> </select> <input id="do_search" type="button" value="Search"></input> </form> <br /> </div> <?php include("leaderboard.php"); ?> </body> </html> |
leaderboard.php
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 |
<?php @session_start(); require_once '/var/www/llpc.tinicumconservancy.org/public_html/jqSuite/jq-config.php'; require_once '/var/www/llpc.tinicumconservancy.org/public_html/jqSuite/php/PHPSuito/jqPivotGrid.php'; require_once ABSPATH."php/PHPSuito/DBdrivers/jqGridPdo.php"; // Connection to the server $db = new PDO(DB_DSN."tinicum",DB_USER,DB_PASSWORD); // Tell the db that we use utf-8 $db->query("SET NAMES utf8"); $pivot = new jqPivotGrid($db); $pivot->SelectCommand = " SELECT concat(lm.LastName,', ',lm.FirstName) as ContactedBy, lt.LpcDescription, lo.LandOwner, cn.ContactDate, case cn.ContactMode when 1 then 'Phone' when 2 then 'Other' when 3 then 'eMail' when 4 then 'Face to Face' when 5 then 'Mail' when 6 then 'Background' when 7 then 'no contact info' when 8 then 'N/A' end as ContactMode, count(*) as total FROM tblContactNotes cn, tblLpcMembers lm, tblLandOwners lo, tblLpcType lt where cn.ContactedBy = lm.LpcMemberID and cn.LandOwnerID = lo.LandOwnerID and lm.LPC = lt.LpcID GROUP BY cn.ContactedBy, cn.LandOwnerID, cn.ContactDate, cn.ContactMode"; $pivot->setData('leaderboard.php'); $pivot->setGridOptions(array( "rowNum" => 1000, "width" => "auto", "height" => "auto", "sortname" => "ContactDate", "sortorder" => "desc", "rowList" => array(20,40,60,80,1000), "altRows" => true, "caption" => "Leader Board", "groupingView" => array("groupCollapse" => true) )); $pivot->setxDimension(array( array( "dataName" => "LpcDescription", "width" => 240), array( "dataName" => "ContactedBy", "width" => 80), array( "dataName" => "LandOwner", "width" => 115), array( "dataName" => "ContactDate", "width" => 115) )); $pivot->setyDimension(array( array( "dataName" => "ContactMode") )); $pivot->setaggregates(array( array( "member" => "total", "aggregator" => "sum", "width" => 80, "label" => "Total", "formatter" => "integer", "align" => "right", "summaryType" => "sum") )); $pivot->setPivotOptions(array( "rowTotals" => true, "colTotals" => true )); $pivot->navigator = true; $pivot->setNavOptions('navigator', array("excel"=>false,"add"=>false,"edit"=>false,"view"=>false,"search"=>true,"reload"=>false)); $buttonoptions = array( "#pager12",array( "caption"=>"Csv", "title"=>"Local Export to CSV", "onClickButton"=>"js: function(){ jQuery('#grid12').jqGrid('exportToCsv'); }" ) ); $pivot->callGridMethod("#grid12", "navButtonAdd", $buttonoptions,1000); $buttonoptions = array( "#pager12",array( "caption"=>"Excel", "title"=>"Local Export to Escel", "onClickButton"=>"js: function(){ jQuery('#grid12').jqGrid('exportToExcel'); }" ) ); $pivot->callGridMethod("#grid12", "navButtonAdd", $buttonoptions,500); $buttonoptions = array( "#pager12", array( "caption"=>"Pdf", "title"=>"Local Export to PDF", "onClickButton"=>"js: function(){ jQuery('#grid12').jqGrid('exportToPdf'); }" ) ); $pivot->callGridMethod("#grid12", "navButtonAdd", $buttonoptions,500); $pivot->renderPivot("#grid12","#pager12", true, null, true, true); ?> |
I did see that in your original reply.
I did some testing and it turns out that all of the things I want to filter on aren’t actually in the pivot data – when I initially wrote this I didn’t realize that the initial data was a one-shot thing.
Thanks for the help – I need to go back to the drawing board and come up with a different method to accomplish what I have in mind.
Ken
Hi Will:
I must be missing something here. The example you provided is not using PHP, nor is it a pivot table. It looks to me like a straight up javascript only clone of the example on your website for custom search with a PHP grid.
I understand about adding a click event in the index.php file, but I don’t see how that ever would change anything in the leaderboard.php file since it’s not referenced.
Ken
Hi Will:
I guess I should have been more specific – and perhaps I posted in the wrong forum too.
I have a PHP pivotGrid that I want to add filtering to, if that’s possible.
Here is my code:
index.php
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 |
<?php include "sqlPDO.php"; $db = opendb('MySQL','tinicum'); $sql = " select LpcID, concat(LPC,' - ',LpcDescription) as LPC from tblLpcType order by LpcID"; $LPChtml = array(); foreach ($db->query($sql) as $row) { $LPChtml[strval($row['LpcID'])] = $row['LPC']; } $sql = " select distinct year(ContactDate) year from tblContactNotes where year(ContactDate) > 0 order by 1"; $Yearhtml = array(); foreach ($db->query($sql) as $row) { $Yearhtml[strval($row['year'])] = $row['year']; } $sql = " select distinct month(ContactDate) monthnum, date_format(ContactDate,'%M') monthname from tblContactNotes where month(ContactDate) > 0 order by 1"; $Monthhtml = array(); foreach ($db->query($sql) as $row) { $Monthhtml[strval($row['monthnum'])] = $row['monthname']; } ?> <!DOCTYPE html> <html> <head> <title>Contact Notes</title> <link rel="stylesheet" type="text/css" media="screen" href="/jqSuite/css/jquery-ui.css" /> <link rel="stylesheet" type="text/css" media="screen" href="/jqSuite/css/trirand/ui.jqgrid.css" /> <link rel="stylesheet" type="text/css" media="screen" href="/jqSuite/css/ui.multiselect.css" /> <link rel="stylesheet" type="text/css" media="screen" href="/css/mtabs.css" /> <style type="text"> html, body { margin: 0; /* Remove body margin/padding */ padding: 0; overflow: hidden; /* Remove scroll bars on browser window */ font-size: 100%; } .ui-jqgrid { font-size: 14px; } .ui-jqgrid .ui-jqgrid-btable tbody tr.jqgrow td { white-space: normal !important; height: auto; } .mylongdata { height: 190px; overflow-y: auto; } </style> <script src="/jqSuite/js/jquery.min.js" type="text/javascript"></script> <script src="/jqSuite/js/trirand/i18n/grid.locale-en.js" type="text/javascript"></script> <script src="/jqSuite/js/trirand/jquery.jqGrid.min.js" type="text/javascript"></script> <!-- Files for the export ----------------------> <script type="text/javascript" language="javascript" src="/js/pivotExport/pdfmake.min.js"></script> <script type="text/javascript" language="javascript" src="/js/pivotExport/vfs_fonts.js"></script> <script type="text/javascript" language="javascript" src="/js/pivotExport/jszip.min.js"></script> <script type="text/javascript"> $.jgrid.no_legacy_api = true; $.jgrid.useJSON = true; $.jgrid.defaults.width = "700"; </script> <script src="/jqSuite/js/jquery-ui.min.js" type="text/javascript"></script> <script> var LPChtml = <?php print(json_encode($LPChtml)); ?>; var Yearhtml = <?php print(json_encode($Yearhtml)); ?>; var Monthhtml = <?php print(json_encode($Monthhtml)); ?>; $(document).ready(function(){ $.each(LPChtml, function(val, text) { // console.log("val: "+val+" text: "+text); $('#LPC').append($("<option></option>").attr("value",val).text(text)); }); $.each(Yearhtml, function(val, text) { // console.log("val: "+val+" text: "+text); $('#year').append($("<option></option>").attr("value",val).text(text)); }); $.each(Monthhtml, function(val, text) { // console.log("val: "+val+" text: "+text); $('#month').append($("<option></option>").attr("value",val).text(text)); }); }); </script> </head> <body> |
<?php include(“leaderboard.php”); ?> </body> </html>
leaderboard.php
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 |
<?php @session_start(); require_once '/var/www/llpc.tinicumconservancy.org/public_html/jqSuite/jq-config.php'; require_once '/var/www/llpc.tinicumconservancy.org/public_html/jqSuite/php/PHPSuito/jqPivotGrid.php'; require_once ABSPATH."php/PHPSuito/DBdrivers/jqGridPdo.php"; // Connection to the server $db = new PDO(DB_DSN."tinicum",DB_USER,DB_PASSWORD); // Tell the db that we use utf-8 $db->query("SET NAMES utf8"); $pivot = new jqPivotGrid($db); $pivot->SelectCommand = " SELECT concat(lm.LastName,', ',lm.FirstName) as ContactedBy, lo.LandOwner, cn.ContactDate, case cn.ContactMode when 1 then 'Phone' when 2 then 'Other' when 3 then 'eMail' when 4 then 'Face to Face' when 5 then 'Mail' when 6 then 'Background' when 7 then 'no contact info' when 8 then 'N/A' end as ContactMode, count(*) as total FROM tblContactNotes cn, tblLpcMembers lm, tblLandOwners lo where cn.ContactedBy = lm.LpcMemberID and cn.LandOwnerID = lo.LandOwnerID GROUP BY cn.ContactedBy, cn.LandOwnerID, cn.ContactDate, cn.ContactMode"; $pivot->setData('leaderboard.php'); $pivot->setGridOptions(array( "rowNum" => 1000, "width" => "auto", "height" => "auto", "sortname" => "ContactDate", "sortorder" => "desc", "rowList" => array(20,40,60,80,1000), "altRows" => true, "caption" => "Leader Board", "groupingView" => array("groupCollapse" => true) )); $pivot->setxDimension(array( array( "dataName" => "ContactedBy", "width" => 240), array( "dataName" => "LandOwner", "width" => 115), array( "dataName" => "ContactDate", "width" => 115) )); $pivot->setyDimension(array( array( "dataName" => "ContactMode") )); $pivot->setaggregates(array( array( "member" => "total", "aggregator" => "sum", "width" => 80, "label" => "Total", "formatter" => "integer", "align" => "right", "summaryType" => "sum") )); $pivot->setPivotOptions(array( "rowTotals" => true, "colTotals" => true )); $pivot->navigator = true; $pivot->setNavOptions('navigator', array("excel"=>false,"add"=>false,"edit"=>false,"view"=>false,"search"=>false,"reload"=>false)); $buttonoptions = array( "#pager12",array( "caption"=>"Csv", "title"=>"Local Export to CSV", "onClickButton"=>"js: function(){ jQuery('#grid12').jqGrid('exportToCsv'); }" ) ); $pivot->callGridMethod("#grid12", "navButtonAdd", $buttonoptions,1000); $buttonoptions = array( "#pager12",array( "caption"=>"Excel", "title"=>"Local Export to Escel", "onClickButton"=>"js: function(){ jQuery('#grid12').jqGrid('exportToExcel'); }" ) ); $pivot->callGridMethod("#grid12", "navButtonAdd", $buttonoptions,500); $buttonoptions = array( "#pager12", array( "caption"=>"Pdf", "title"=>"Local Export to PDF", "onClickButton"=>"js: function(){ jQuery('#grid12').jqGrid('exportToPdf'); }" ) ); $pivot->callGridMethod("#grid12", "navButtonAdd", $buttonoptions,500); $pivot->renderPivot("#grid12","#pager12", true, null, true, true); ?> |
I am looking forward to seeing the example!
Ken
(For some reason the Code icon is missing from this compose window – sorry in advance for the ugly code block).
I made the changes you suggested:
<p style=”padding-left: 40px;”>// Set output format to json
$grid2->dataType = ‘json’;
$grid2->setTable = ‘tblLandOwners’;
$grid2->table = ‘tblLandOwners’;
$grid2->setPrimaryKeyID(‘LandOwnerID’);
$grid2->serialKey = true;
if ($grid2->oper == ‘add’) {
$data = filter_input_array(INPUT_POST);
$grid2->trans = false;
if ($grid2->insert($data)) {
// $sql = “select LAST_INSERT_ID()”;
// $stmt = $conn->query($sql);
// $lastInsertID = $stmt->fetch();
$lastInsertID = $conn->lastInsertId();
// Create a ‘blank’ record for this landowner in tblParcels
$sql = “insert into tblParcels (LandOwnerID) values(“.$lastInsertID[0].”)”;
$conn->query($sql);
}
}
$grid2->add = false;</p>
<p style=”padding-left: 40px;”>// Let the grid create the model
$grid2->setColModel();</p>
The insert for tblLandOwners worked, but no record was created in tblParcels.
Ken
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 |
show create table tblLandOwners\G *************************** 1. row *************************** Table: tblLandOwners Create Table: CREATE TABLE <code>tblLandOwners</code> ( LandOwnerID</code> int(4) NOT NULL AUTO_INCREMENT, CurrentlyAssignedTo</code> int(4) NOT NULL DEFAULT 38, LandOwner</code> varchar(220) DEFAULT NULL, LandOwnerNotes</code> varchar(4044) DEFAULT NULL, Status</code> tinyint(2) NOT NULL, LandOwnerAddress1</code> varchar(220) DEFAULT NULL, LandOwnerAddress2</code> varchar(220) DEFAULT NULL, LandOwnerCity</code> varchar(220) DEFAULT NULL, LandOwnerState</code> varchar(220) DEFAULT NULL, LandOwnerZip</code> varchar(220) DEFAULT NULL, HowToContact</code> varchar(255) DEFAULT NULL, MailingSalutation</code> varchar(220) DEFAULT NULL, AddressedTo</code> varchar(220) DEFAULT NULL, PRIMARY KEY (<code>LandOwnerID</code>), KEY <code>CurrentlyAssignedTo</code> (<code>CurrentlyAssignedTo</code>), KEY <code>Status</code> (<code>Status</code>), CONSTRAINT <code>tblLandOwners_ibfk_1</code> FOREIGN KEY (<code>Status</code>) REFERENCES <code>tblLandOwnerStatus</code> (<code>StatusID</code>) ON UPDATE CASCADE ) ENGINE=InnoDB AUTO_INCREMENT=169 DEFAULT CHARSET=utf8mb3 COLLATE=utf8mb3_general_ci ROW_FORMAT=COMPACT 1 row in set (0.018 sec) |
And just for completeness – here’s tblParcels
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 |
show create table tblParcels\G *************************** 1. row *************************** Table: tblParcels Create Table: CREATE TABLE <code>tblParcels</code> ( ParcelID</code> int(4) NOT NULL AUTO_INCREMENT, LandOwnerID</code> int(4) DEFAULT NULL, WatershedID</code> int(4) NOT NULL DEFAULT 9, DeededTo</code> varchar(220) DEFAULT NULL, ParcelNum</code> varchar(220) NOT NULL DEFAULT '00-000-000', Acres</code> decimal(8,4) DEFAULT 0.0000, ContiguousParcels</code> int(1) NOT NULL DEFAULT 0, GasLease</code> int(1) NOT NULL DEFAULT 0, DisqualifyingUses</code> int(1) NOT NULL DEFAULT 0, LandUse</code> int(2) NOT NULL DEFAULT 10, ParcelRoadNum</code> varchar(220) DEFAULT NULL, ParcelRoad</code> varchar(220) DEFAULT NULL, ParcelCity</code> varchar(220) DEFAULT NULL, ParcelState</code> varchar(220) DEFAULT NULL, ParcelZip</code> varchar(220) DEFAULT NULL, LPC</code> int(1) NOT NULL DEFAULT 6, PRIMARY KEY (<code>ParcelID</code>), KEY <code>LandOwnerID</code> (<code>LandOwnerID</code>), KEY <code>WatershedID</code> (<code>WatershedID</code>), KEY <code>LandUse</code> (<code>LandUse</code>), CONSTRAINT <code>tblParcels_ibfk_2</code> FOREIGN KEY (<code>WatershedID</code>) REFERENCES <code>tblWatersheds</code> (<code>WatershedID</code>) ON DELETE CASCADE ON UPDATE CASCADE, CONSTRAINT <code>tblParcels_ibfk_3</code> FOREIGN KEY (<code>LandUse</code>) REFERENCES <code>tblLandUses</code> (<code>LandUseID</code>) ON UPDATE CASCADE, CONSTRAINT <code>tblParcels_ibfk_4</code> FOREIGN KEY (<code>LandOwnerID</code>) REFERENCES <code>tblLandOwners</code> (<code>LandOwnerID</code>) ON DELETE SET NULL ON UPDATE CASCADE ) ENGINE=InnoDB AUTO_INCREMENT=238 DEFAULT CHARSET=utf8mb3 COLLATE=utf8mb3_general_ci ROW_FORMAT=COMPACT 1 row in set (0.000 sec) |
I did manage to find a workaround that actually worked. Rather than using the $grid->getLastInsertId() method, I used the MySQL LAST_INSERT_ID() function:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 |
// Set output format to json $grid2->dataType = 'json'; $grid2->setTable = 'tblLandOwners'; $grid2->table = 'tblLandOwners'; $grid2->setPrimaryKeyID('LandOwnerID'); $grid2->serialKey = true; if ($grid2->oper == 'add') { $data = filter_input_array(INPUT_POST); if ($grid2->insert($data)) { $sql = "select LAST_INSERT_ID()"; $stmt = $conn->query($sql); $lastInsertID = $stmt->fetch(); //$lastInsertID = $grid2->getLastInsertId(); // Create a 'blank' record for this landowner in tblParcels $sql = "insert into tblParcels (LandOwnerID) values(".$lastInsertID[0].")"; $conn->query($sql); } } $grid2->add = false; // Let the grid create the model $grid2->setColModel(); |
That worked like a champ.
Ken
I added some test code based on what you said, but I never get any value with the $grid->getLastInsertId method.
Here’s what I added:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 |
// Set output format to json $grid2->dataType = 'json'; $grid2->setTable = 'tblLandOwners'; $grid2->table = 'tblLandOwners'; $grid2->setPrimaryKeyID('LandOwnerID'); $grid2->serialKey = true; $lastInsertID = "none set"; if ($grid2->oper == 'add') { $fh = fopen('/tmp/addloform.txt','w'); $data = filter_input_array(INPUT_POST); fwrite($fh,"Data: ".print_r($data,true)."\n"); if ($grid2->insert($data)) { $lastInsertID = $grid2->getLastInsertId(); fwrite($fh,"Last insert LandOwnerID: ".$lastInsertID."\n"); fclose($fh); } } $grid2->add = false; // Let the grid create the model $grid2->setColModel(); |
After I perform the insert, the text file I created (addloform.txt) contains:
Data: Array
(
[LandOwnerID] =>
[LandOwner] => Rogers, K&T
[LandOwnerNotes] => jj
[Status] => 1
[CurrentlyAssignedTo] => 26
[LandOwnerAddress1] => 86 Gruiver Rd
[LandOwnerAddress2] =>
[LandOwnerCity] => Branchburg
[LandOwnerState] => NJ
[LandOwnerZip] => 08876
[HowToContact] => kjh
[MailingSalutation] => lkjh
[AddressedTo] => lkjh
[oper] => add
)Last insert LandOwnerID:
It looks like the $grid->getLastInsertId method is returning something (since the value got reset from “none set”), but it’s not the primary key from the insert.
The Help file for this method reads:
getLastInsertId( )The method return the last inserted id when a add is performed and the primary key is serial. In order to have effect this method should be called after renderGrid, editGrid or insert methods – see below
API Tags:
Access: public
Is it possible I have it in the wrong place?
Ken
Hi Will
If you could send that to me I’d appreciate it.
Do you have a target date for the next release?
Ken
Thank you Will – I will give that a try.
Ken
I did a lot of further digging and testing, and I think the issue is that the insert into the first table isn’t sending the LpcMemberID in the post message since that field is an auto-increment field, and my understanding of the function gets its data from either the $_GET or $_POST data. Looking at the debug log I never see any value set for $cid other than _empty, so the setAfterCrudAction sql fails with an error (I had to enable error logs in my DB engine).
So the question is – is it even possible to get the last primary key value (LpcMemberID) in the script?
Will:
Good grief, all that because I forgot to capitalize an ‘m’!
All I had to do was correct the select statement for the proper case.
Thank you for your quick reply.
Ken
What is the secret to formatting code like you did in your post above? I did it once years ago and now can’t for the life of me figure out how to do it.
Can you post a sticky post somewhere with instructions on how to post things like that in this forum?
Copyright 2014 TriRand LtdAll Rights ReservedRSS
Back to Top