{"id":1301,"date":"2015-12-04T17:04:33","date_gmt":"2015-12-04T22:04:33","guid":{"rendered":"http:\/\/xlinesoft.com\/blog\/?p=1301"},"modified":"2022-11-02T12:26:28","modified_gmt":"2022-11-02T17:26:28","slug":"storing-field-labels-and-visibility-rules-in-the-database","status":"publish","type":"post","link":"https:\/\/xlinesoft.com\/blog\/2015\/12\/04\/storing-field-labels-and-visibility-rules-in-the-database\/","title":{"rendered":"Storing field labels and visibility rules in the database"},"content":{"rendered":"<p>You need to change some field labels in your project but don&#8217;t want to rebuild and upload the whole project. In this tutorial, we&#8217;ll show how to do that by storing field labels in the database and displaying them dynamically. As a bonus, we&#8217;ll also show how to hide fields based on visibility rules stored in the database, and make fields read-only or required. <\/p>\n<p>First, we need to create a table in the database to store all those settings. We would need fields like table name, field name, field label and visibility. Here is the sample table script for MySQL. <\/p>\n<div class=\"my-syntax-highlighter\">\n<pre><textarea id=\"mshighlighter\" class=\"mshighlighter\" language=\"sql\" name=\"mshighlighter\" >\r\nCREATE TABLE `settings`(\r\n`id` int NOT NULL AUTO_INCREMENT, \r\n`tablename` varchar(50) NULL, \r\n`field` varchar(50) NULL, `label` varchar(100) NULL, \r\n`visible` tinyint NULL DEFAULT 0, \r\n`readonly` tinyint NULL DEFAULT 0,\r\n`required` tinyint NULL DEFAULT 0,\r\nPRIMARY KEY (`id`))\r\nCHARACTER SET utf8;<\/textarea><\/pre>\n<\/div>\n<p><!--<a target=\"_blank\" class=\"myButton\" href=\"http:\/\/xlinesoft.com\/livedemo\/labels\/settings_list.php\" rel=\"noopener noreferrer\">Live demo<\/a>--><br \/>\n<!--more--><\/p>\n<p>Here is how it works in generated application. Go to &#8220;settings&#8221; table first. Add a new record specifying table name (carscars), field name (Horsepower), field label (anything you want). Make field visible if you want to see how field label being applied. Turn visibility off to see field disappear from all pages. Once record added and saved go to &#8220;carscars&#8221; table and see those rules being applied.<\/p>\n<h2>Code<\/h2>\n<p><b>PHPRunner<\/b><\/p>\n<p>Code needs to be applied to each table where you need this functionality. No changes in the code are required. <\/p>\n<h4>AfterTableInit event<\/h4>\n<div class=\"my-syntax-highlighter\">\n<pre><textarea id=\"mshighlighter\" class=\"mshighlighter\" language=\"php\" name=\"mshighlighter\" >\r\nglobal $field_labels, $tables_data;\r\n$sql=\"select * from settings where tablename='\".$table.\"'\";\r\n$rs = CustomQuery($sql);\r\nwhile ($data = db_fetch_array($rs)) {\r\n\t\t$field_labels[$table][mlang_getcurrentlang()][GoodFieldName($data[\"field\"])]=$data[\"label\"];\r\n\t\tif ($data[\"readonly\"]==1)\r\n\t\t\t$tables_data[$table][$data[\"field\"]][\"EditFormats\"][\"edit\"][\"EditFormat\"]=\"Readonly\";\r\n\t\tif ($data[\"required\"]==1)\r\n\t\t\t$tables_data[$table][$data[\"field\"]][\"EditFormats\"][\"edit\"][\"validateAs\"][\"basicValidate\"][0]=\"IsRequired\";\r\n}<\/textarea><\/pre>\n<\/div>\n<h4>Edit\/Add\/View pages, BeforeDisplay event<\/h4>\n<div class=\"my-syntax-highlighter\">\n<pre><textarea id=\"mshighlighter\" class=\"mshighlighter\" language=\"php\" name=\"mshighlighter\" >\r\nglobal $strTableName;\r\n$sql=\"select * from settings where tablename='\".$strTableName.\"'\";\r\n$rs = CustomQuery($sql);\r\nwhile ($data = db_fetch_array($rs)) {\r\n\tif ($data[\"visible\"]!=1) {\r\n\t\t$pageObject->hideField($data[\"field\"]);\r\n\t}\r\n}<\/textarea><\/pre>\n<\/div>\n<h4>List page, BeforeDisplay event<\/h4>\n<div class=\"my-syntax-highlighter\">\n<pre><textarea id=\"mshighlighter\" class=\"mshighlighter\" language=\"php\" name=\"mshighlighter\" >\r\nglobal $strTableName;\r\n$sql=\"select * from settings where tablename='\".$strTableName.\"'\";\r\n$rs = CustomQuery($sql);\r\nwhile ($data = db_fetch_array($rs)) {\r\n\tif ($data[\"visible\"]!=1) {\r\n\t\t$xt->assign($data[\"field\"].\"_fieldheadercolumn\", false);\r\n\t\t$xt->assign($data[\"field\"].\"_fieldcolumn\", false);\r\n\t}\r\n}<\/textarea><\/pre>\n<\/div>\n<h4>Print page, BeforeDisplay event<\/h4>\n<div class=\"my-syntax-highlighter\">\n<pre><textarea id=\"mshighlighter\" class=\"mshighlighter\" language=\"php\" name=\"mshighlighter\" >\r\nglobal $strTableName;\r\n$sql=\"select * from settings where tablename='\".$strTableName.\"'\";\r\n\r\n$rs = CustomQuery($sql);\r\nwhile ($data = db_fetch_array($rs)) {\r\n\tif ($data[\"visible\"]!=1) {\r\n\t\tforeach( $xt->xt_vars[\"body\"][\"data\"] as $page=>$pagedata ) {\r\n\t\t\t$xt->xt_vars[\"body\"][\"data\"][$page][$data[\"field\"].\"_fieldheadercolumn\"] = false;\r\n\t\t\t$xt->xt_vars[\"body\"][\"data\"][$page][$data[\"field\"].\"_fieldcolumn\"] = false;\r\n\t\t}\r\n\t}\r\n}<\/textarea><\/pre>\n<\/div>\n<p><b>ASPRunnerPro<\/b><\/p>\n<h4>AfterTableInit event<\/h4>\n<div class=\"my-syntax-highlighter\">\n<pre><textarea id=\"mshighlighter\" class=\"mshighlighter\" language=\"php\" name=\"mshighlighter\" >\r\ndim sql, rs, data\r\nsql=\"select * from settings where tablename='\" & table & \"'\"\r\nset rs = CustomQuery(sql)\r\ndo while bValue(DoAssignment(data,db_fetch_array(rs))) \r\n         \r\n\tsetArrElementN field_labels,CreateArray3(GoodFieldName(table),mlang_getcurrentlang(),GoodFieldName(data(\"field\"))),data(\"label\")\r\n \r\n  if data(\"readonly\")=1 then\r\n\t\tsetArrElementN tables_data,CreateArray5(GoodFieldName(table),data(\"field\"),\"EditFormats\",\"edit\",\"EditFormat\"),\"Readonly\"\r\n\tend if\r\n  if data(\"required\")=1 then\r\n\r\n\t\tsetArrElementN tables_data,CreateArray6(GoodFieldName(table),data(\"field\"),\"EditFormats\",\"edit\",\"validateAs\",\"basicValidate\"),CreateArray2(0,\"IsRequired\")\r\n\tend if\r\n\r\nloop<\/textarea><\/pre>\n<\/div>\n<h4>Edit\/Add\/View pages, BeforeDisplay event<\/h4>\n<div class=\"my-syntax-highlighter\">\n<pre><textarea id=\"mshighlighter\" class=\"mshighlighter\" language=\"php\" name=\"mshighlighter\" >\r\ndim sql, rs, data\r\nsql=\"select * from settings where tablename='\" & strTableName & \"'\"\r\nset rs = CustomQuery(sql)\r\ndo while bValue(DoAssignment(data,db_fetch_array(rs))) \r\n    if data(\"visible\")<>1 then  \r\n        pageObject.hideField(data(\"field\"))\r\n    end if\r\nloop<\/textarea><\/pre>\n<\/div>\n<h4>List page, BeforeDisplay event<\/h4>\n<div class=\"my-syntax-highlighter\">\n<pre><textarea id=\"mshighlighter\" class=\"mshighlighter\" language=\"php\" name=\"mshighlighter\" >\r\ndim sql, rs, data\r\nsql=\"select * from settings where tablename='\" & strTableName & \"'\"\r\nset rs = CustomQuery(sql)\r\ndo while bValue(DoAssignment(data,db_fetch_array(rs))) \r\n    if data(\"visible\")<>1 then  \r\n\t\t\t\txt.assign data(\"field\") & \"_fieldheadercolumn\", false\r\n\t\t\t\txt.assign data(\"field\") & \"_fieldheader\", false\r\n    end if\r\nloop<\/textarea><\/pre>\n<\/div>\n<h4>Print page, BeforeDisplay event<\/h4>\n<div class=\"my-syntax-highlighter\">\n<pre><textarea id=\"mshighlighter\" class=\"mshighlighter\" language=\"php\" name=\"mshighlighter\" >\r\ndim sql, rs, data\r\nsql=\"select * from settings where tablename='\" & strTableName & \"'\"\r\nset rs = CustomQuery(sql)\r\ndo while bValue(DoAssignment(data,db_fetch_array(rs))) \r\n    if data(\"visible\")<>1 then  \r\n\t\t\t\txt.assign data(\"field\") & \"_fieldheadercolumn\", false\r\n\t\t\t\txt.assign data(\"field\") & \"_fieldheader\", false\r\n    end if\r\n\r\n\t\tset body = xt.getvar_p1(\"body\")\r\n\t\ta = body(\"data\").Items\r\n\r\n\t\tfor i=0 to body(\"data\").Count-1 \r\n\t\t\t\ta(i)(data(\"field\")&\"_fieldheadercolumn\")=false\r\n\t\t\t\ta(i)(data(\"field\")&\"_fieldheader\")=false\r\n\t  next    \r\nloop<\/textarea><\/pre>\n<\/div>\n<p><b>ASPRunner.NET (C#)<\/b><\/p>\n<h4>AfterTableInit event<\/h4>\n<div class=\"my-syntax-highlighter\">\n<pre><textarea id=\"mshighlighter\" class=\"mshighlighter\" language=\"php\" name=\"mshighlighter\" >\r\nstring t = table;\r\nstring sql=String.Format(\"select * from settings where tablename='{0}'\", t);\r\nXVar rs = tDAL.CustomQuery(sql);\r\nXVar data; \r\n\r\nwhile (data = CommonFunctions.db_fetch_array(rs)) {\r\n\t\tGlobalVars.field_labels[t][CommonFunctions.mlang_getcurrentlang()][MVCFunctions.GoodFieldName(data[\"field\"])]=data[\"label\"];\r\n\t\tif (data[\"readonly\"]==1)\r\n\t\t\tGlobalVars.tables_data[t][data[\"field\"]][\"EditFormats\"][\"edit\"][\"EditFormat\"]=\"Readonly\";\r\n\t\tif (data[\"required\"]==1)\r\n\t\t\tGlobalVars.tables_data[t][data[\"field\"]][\"EditFormats\"][\"edit\"][\"validateAs\"][\"basicValidate\"][0]=\"IsRequired\";\r\n}<\/textarea><\/pre>\n<\/div>\n<h4>Edit\/Add\/View pages, BeforeDisplay event<\/h4>\n<div class=\"my-syntax-highlighter\">\n<pre><textarea id=\"mshighlighter\" class=\"mshighlighter\" language=\"php\" name=\"mshighlighter\" >\r\nstring t = GlobalVars.strTableName.ToString();\r\nstring sql=String.Format(\"select * from settings where tablename='{0}'\", t);\r\nXVar rs = tDAL.CustomQuery(sql);\r\nXVar data; \r\n\r\nwhile (data = CommonFunctions.db_fetch_array(rs)) {\r\n\tif (data[\"visible\"]!=1) {\r\n\t\tpageObject.hideField(data[\"field\"]);\r\n\t}\r\n}<\/textarea><\/pre>\n<\/div>\n<p>Some ideas on how to extend this functionality:<\/p>\n<ul>\n<li>hide fields or change labels based on logged in user or day of week<\/li>\n<li>hide fields on some pages and show on others<\/li>\n<li>make this code work with multiple languages. You will have to add &#8220;language&#8221; field to &#8220;settings&#8221; table and modify code to take into account current language ($_SESSION[&#8220;language&#8221;] in PHP). For instance, if Russian language is selected you can ask users how many balalaika playing bears have they met today on their way to work. Apparently this question makes no sense if any other language is selected. <\/li>\n<li>apply similar approach to table labels. You will probably need a different table to store table labels, i.e. ID, TableName, Label. Then you can use MenuItemModify event to run a SQL query, retrieve current table label (if exists) and replace it using setTitle() function.<\/li>\n<\/ul>\n<p>Lets us know what else you&#8217;d like to store in the database and apply dynamically.<\/p>\n<p>Happy coding!<\/p>\n","protected":false},"excerpt":{"rendered":"<p>You need to change some field labels in your project but don&#8217;t want to rebuild and upload the whole project. In this tutorial, we&#8217;ll show how to do that by storing field labels in the database and displaying them dynamically. As a bonus, we&#8217;ll also show how to hide fields based on visibility rules stored in the database, and make fields read-only or required. First, we need to create a table in the database to store all those settings. We would need fields like table&#8230;<span class=\"clearfix clearfix-post\"><\/span><a href=\"https:\/\/xlinesoft.com\/blog\/2015\/12\/04\/storing-field-labels-and-visibility-rules-in-the-database\/\" class=\"more-link\">Continue Reading <span class=\"screen-reader-text\">&#8220;Storing field labels and visibility rules in the database&#8221;<\/span> <span class=\"meta-nav\">&rarr;<\/span><\/a><\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[1,4,8],"tags":[],"class_list":["post-1301","post","type-post","status-publish","format-standard","hentry","category-php-category","category-php-code-generator","category-tutorials"],"_links":{"self":[{"href":"https:\/\/xlinesoft.com\/blog\/wp-json\/wp\/v2\/posts\/1301","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/xlinesoft.com\/blog\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/xlinesoft.com\/blog\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/xlinesoft.com\/blog\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/xlinesoft.com\/blog\/wp-json\/wp\/v2\/comments?post=1301"}],"version-history":[{"count":26,"href":"https:\/\/xlinesoft.com\/blog\/wp-json\/wp\/v2\/posts\/1301\/revisions"}],"predecessor-version":[{"id":2866,"href":"https:\/\/xlinesoft.com\/blog\/wp-json\/wp\/v2\/posts\/1301\/revisions\/2866"}],"wp:attachment":[{"href":"https:\/\/xlinesoft.com\/blog\/wp-json\/wp\/v2\/media?parent=1301"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/xlinesoft.com\/blog\/wp-json\/wp\/v2\/categories?post=1301"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/xlinesoft.com\/blog\/wp-json\/wp\/v2\/tags?post=1301"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}