Will:
I see now where I went wrong on my last iteration – I changed the SelectCommand per your examples and my grid is now behaving correctly.
Thank you so much for your diligence and support.
Ken
Will:
So the original SQL I submitted is the same as in your last message – the only change you suggested is to flip the formatter option to true in the setSelect statement – is that correct?
When I made those changes the CustomerFullName and Status columns in the grid are blank (looking at the rendered HTML they contain a non-breaking space ). When I do a search I do get the drop-down list as I expect, but the search never finds any records.
I checked the Developer’s console for Chrome – the response to the AJAX query for main.php has all of the data in it:
{“records”:5,”page”:1,”total”:1,”rows”:[{“ID”:2,”CustomerFullName”:”Ginger Grant”,”Status”:”Active”},{“ID”:1,”CustomerFullName”:”Jonas Grumby”,”Status”:”Archived”},{“ID”:3,”CustomerFullName”:”Lovey Howell”,”Status”:”Inactive”},{“ID”:5,”CustomerFullName”:”Roy Hinkley”,”Status”:”Active”},{“ID”:4,”CustomerFullName”:”Thurston Howell III”,”Status”:”Active”}]}
I can’t figure out why I am seeing in those two columns.
Ken
Hi Will:
When I originally tested the SQL I did it from the command line and it worked fine. I didn’t try phpMyAdmin until just now and I see the error you are getting. The problem was that the mysqldump is creating table tblMain before tblStatus. I re-arragend the SQl and it’s working now for PhpMyAdmin:
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 |
-- MariaDB dump 10.19 Distrib 10.6.12-MariaDB, for debian-linux-gnu (x86_64) -- -- Host: localhost Database: ledger -- ------------------------------------------------------ -- Server version 10.6.12-MariaDB-0ubuntu0.22.04.1 /*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */; /*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */; /*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */; /*!40101 SET NAMES utf8mb4 */; /*!40103 SET @OLD_TIME_ZONE=@@TIME_ZONE */; /*!40103 SET TIME_ZONE='+00:00' */; /*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */; /*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */; /*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */; /*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 */; -- -- Table structure for table tblCustomers -- DROP TABLE IF EXISTS tblCustomers; /*!40101 SET @saved_cs_client = @@character_set_client */; /*!40101 SET character_set_client = utf8 */; CREATE TABLE tblCustomers ( CustomerFullNameID int(2) NOT NULL AUTO_INCREMENT, CustomerFullName varchar(50) NOT NULL, PRIMARY KEY (CustomerFullNameID) ) ENGINE=InnoDB AUTO_INCREMENT=8 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci; /*!40101 SET character_set_client = @saved_cs_client */; -- -- Dumping data for table tblCustomers -- LOCK TABLES tblCustomers WRITE; /*!40000 ALTER TABLE tblCustomers DISABLE KEYS */; INSERT INTO tblCustomers VALUES (1,'Jonas Grumby'),(2,'Willie Gilligan'),(3,'Mary Ann Summers'),(4,'Ginger Grant'),(5,'Roy Hinkley'),(6,'Thurston Howell III'),(7,'Lovey Howell'); /*!40000 ALTER TABLE tblCustomers ENABLE KEYS */; UNLOCK TABLES; -- -- Table structure for table tblStatus -- DROP TABLE IF EXISTS tblStatus; /*!40101 SET @saved_cs_client = @@character_set_client */; /*!40101 SET character_set_client = utf8 */; CREATE TABLE tblStatus ( StatusID int(11) NOT NULL AUTO_INCREMENT, Status varchar(20) NOT NULL, PRIMARY KEY (StatusID) ) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci; /*!40101 SET character_set_client = @saved_cs_client */; -- -- Dumping data for table tblStatus -- LOCK TABLES tblStatus WRITE; /*!40000 ALTER TABLE tblStatus DISABLE KEYS */; INSERT INTO tblStatus VALUES (1,'Active'),(2,'Inactive'),(3,'Archived'); /*!40000 ALTER TABLE tblStatus ENABLE KEYS */; UNLOCK TABLES; /*!40103 SET TIME_ZONE=@OLD_TIME_ZONE */; -- -- Table structure for table tblMain -- DROP TABLE IF EXISTS tblMain; /*!40101 SET @saved_cs_client = @@character_set_client */; /*!40101 SET character_set_client = utf8 */; CREATE TABLE tblMain ( ID int(11) NOT NULL AUTO_INCREMENT, CustomerFullName int(2) NOT NULL, Status int(1) NOT NULL, PRIMARY KEY (ID), KEY CustomerFullName (CustomerFullName), KEY Status (Status), CONSTRAINT tblMain_ibfk_1 FOREIGN KEY (CustomerFullName) REFERENCES tblCustomers (CustomerFullNameID) ON DELETE CASCADE ON UPDATE NO ACTION, CONSTRAINT tblMain_ibfk_2 FOREIGN KEY (Status) REFERENCES tblStatus (StatusID) ON DELETE CASCADE ON UPDATE NO ACTION ) ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci; /*!40101 SET character_set_client = @saved_cs_client */; -- -- Dumping data for table tblMain -- LOCK TABLES tblMain WRITE; /*!40000 ALTER TABLE tblMain DISABLE KEYS */; INSERT INTO tblMain VALUES (1,1,3),(2,4,1),(3,7,2),(4,6,1),(5,5,1); /*!40000 ALTER TABLE tblMain ENABLE KEYS */; UNLOCK TABLES; /*!40101 SET SQL_MODE=@OLD_SQL_MODE */; /*!40014 SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS */; /*!40014 SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS */; /*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */; /*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */; /*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */; /*!40111 SET SQL_NOTES=@OLD_SQL_NOTES */; -- Dump completed on 2023-06-27 12:04:23 |
When I do that I no longer see the text values in the gird from the foreign table, I only get the IDs, and when I do a search I get no dropdown list.
Ken
The <> icon in the picture is not showing in the editor anymore, so how do we add code?
Ken
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
Copyright 2014 TriRand LtdAll Rights ReservedRSS
Back to Top