.I have a table that has several fields that contain integers which are the indexes of separate tables used to lookup the text value. Here is the sql to re-create it.
|
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 |
-- 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 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; -- -- 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 */; /*!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 <strong>index.php</strong> <!DOCTYPE html> <html> <head> <title>jqGrid PHP Demo</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" /> <style type="text"> html, body { margin: 0; /* Remove body margin/padding */ padding: 0; overflow: hidden; /* Remove scroll bars on browser window */ font-size: 75%; } </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/jquery-ui.min.js" type="text/javascript"></script> <script src="/jqSuite/js/trirand/jquery.jqGrid.min.js" type="text/javascript"></script> <script type="text/javascript"> $.jgrid.no_legacy_api = true; $.jgrid.useJSON = true; $.jgrid.defaults.width = "700"; </script> </head> <body> <?php include ("main.php");?> </body> </html> //main.php // Version 5.5.5 require_once '/var/www/html/jqSuite/jq-config.php'; require_once ABSPATH."php/PHPSuito/jqGrid.php"; require_once ABSPATH."php/PHPSuito/DBdrivers/jqGridPdo.php"; $conn = new PDO(DB_DSN."ledger",DB_USER,DB_PASSWORD); $conn->query("SET NAMES utf8"); $grid = new jqGridRender($conn); $grid->SelectCommand = " select m.ID, c.CustomerFullName, s.Status from tblMain m, tblCustomers c, tblStatus s where m.CustomerFullName = c.CustomerFullNameID and m.Status = s.StatusID"; $grid->dataType = 'json'; $grid->table='tblMain'; $grid->setPrimaryKeyID('ID'); $grid->setColModel(); $grid->setGridOptions(array( "rowNum"=>10, "rowList"=>array(10,40,50), "sortname"=>"CustomerFullName", // "sortorder"=>"asc", "hoverrows"=>true, "altRows"=>true, "height"=>"600", "autowidth"=>true, "toppager"=>true, "caption"=>"Customers" )); $grid->setUrl('main.php'); $grid->setFilterOptions(array("searchOperators"=>true)); $grid->setSelect("CustomerFullName","SELECT CustomerFullNameID, CustomerFullName from tblCustomers order by 2", false, true, true, array(""=>"")); $grid->setSelect("Status","SELECT StatusID, Status from tblStatus order by 2", false, true, true, array(""=>"")); $grid->navigator = true; $grid->setNavOptions('navigator', array("excel"=>false,"add"=>true,"edit"=>false,"del"=>false,"view"=>true, "search"=>true, "cloneToTop"=>true)); $grid->setNavOptions('add',array("height"=>"auto","dataheight"=>"auto","width"=>"auto","closeAfterAdd"=>true)); $grid->setNavOptions('view',array("top"=>30,"left"=>30,"height"=>"auto","dataheight"=>"auto","width"=>800,"labelswidth"=>"20%")); $grid->renderGrid('#grid4','#grid4-toppager',true, null, null, true, false); |
The grid renders as expected, and I am able to add records to the main table via the form. What I can’t figure out is how to get the searching functions to work. When I use the select dropdown to search, I never get any matches because the search is looking for the index value of the text, but the grid only contains the text. When I add a record the setselect statement is using the index value in the data array sent to the server, so that works fine.
How do I get the the searching part to work?
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
Copyright 2014 TriRand LtdAll Rights ReservedRSS
Back to Top
