{"id":2406,"date":"2021-05-26T13:30:23","date_gmt":"2021-05-26T18:30:23","guid":{"rendered":"https:\/\/xlinesoft.com\/blog\/?p=2406"},"modified":"2026-08-16T15:47:02","modified_gmt":"2026-08-16T20:47:02","slug":"database-based-dropdowns-with-dialog-api","status":"publish","type":"post","link":"https:\/\/xlinesoft.com\/blog\/2021\/05\/26\/database-based-dropdowns-with-dialog-api\/","title":{"rendered":"Populate Dialog API Dropdowns from a Database in PHPRunner"},"content":{"rendered":"<p>Dialog API runs in JavaScript, but the values you want to display in a dropdown often come from a database. The solution is to retrieve those values on the server, pass them to JavaScript with <strong>setProxyValue()<\/strong>, and then use them as the lookup options in your dialog.<\/p>\n<p>The process has three steps:<\/p>\n<ol>\n<li>Query the database and build an array of lookup values.<\/li>\n<li>Pass that array to JavaScript using <strong>setProxyValue()<\/strong>.<\/li>\n<li>Use the resulting <strong>proxy<\/strong> value as the Dialog API lookup options.<\/li>\n<\/ol>\n<p>This approach works in both PHPRunner and ASPRunner.NET. The server-side code is different, but the Dialog API JavaScript is the same.<\/p>\n<p><!--more--><\/p>\n<h2>Starting with a hardcoded dropdown<\/h2>\n<p>A Dialog API lookup field can use a two-dimensional array containing the stored value and display value for each entry.<\/p>\n<p>For example:<\/p>\n<div class=\"my-syntax-highlighter\">\n<pre><textarea id=\"mshighlighter\" class=\"mshighlighter\" language=\"js\" name=\"mshighlighter\" >\r\nreturn Runner.Dialog({\r\n\ttitle: 'Preferences',\r\n\tfields: [{\r\n\t\tname: 'color',\r\n\t\tlabel: 'What is your favorite color?',\r\n\t\ttype: 'lookup',\r\n\t\tvalue: 2,\r\n\t\toptions: [\r\n\t\t\t[1, 'red'],\r\n\t\t\t[2, 'green'],\r\n\t\t\t[3, 'yellow']\r\n\t\t]\r\n\t}],\r\n\tok: 'Save',\r\n\tcancel: 'Cancel',\r\n\tbeforeOK: function(popup, controls) {\r\n\t\tswal('Success', 'Selected color: ' + controls[0].val(), 'success');\r\n\t}\r\n});<\/textarea><\/pre>\n<\/div>\n<p>This works well when the available choices are fixed. If the choices are stored in a database, however, hardcoding them into the JavaScript is not practical.<\/p>\n<p>Instead, we can retrieve them before the page is displayed.<\/p>\n<h2>Step 1: Retrieve the lookup values from the database<\/h2>\n<p>Add the following code to the <strong>BeforeDisplay<\/strong> event of the page where you will use the Dialog API.<\/p>\n<h3>PHPRunner<\/h3>\n<div class=\"my-syntax-highlighter\">\n<pre><textarea id=\"mshighlighter\" class=\"mshighlighter\" language=\"php\" name=\"mshighlighter\" >\r\n$lookup = array();\r\n\r\n$rs = DB::Query(\"select id, color from carsbcolor\");\r\n\r\nwhile ($data = $rs->fetchAssoc())\r\n{\r\n\t$row = array();\r\n\t$row[] = $data[\"id\"];\r\n\t$row[] = $data[\"color\"];\r\n\t$lookup[] = $row;\r\n}\r\n\r\n$pageObject->setProxyValue(\"lookup\", $lookup);<\/textarea><\/pre>\n<\/div>\n<p>This code retrieves the values from the database and builds an array in the format expected by a Dialog API lookup:<\/p>\n<div class=\"my-syntax-highlighter\">\n<pre><textarea id=\"mshighlighter\" class=\"mshighlighter\" language=\"\" name=\"mshighlighter\" >\r\n[\r\n\t[1, 'red'],\r\n\t[2, 'green'],\r\n\t[3, 'yellow']\r\n]<\/textarea><\/pre>\n<\/div>\n<p>The actual values, of course, come from your database.<\/p>\n<h3>ASPRunner.NET<\/h3>\n<p>The equivalent C# code is:<\/p>\n<div class=\"my-syntax-highlighter\">\n<pre><textarea id=\"mshighlighter\" class=\"mshighlighter\" language=\"\" name=\"mshighlighter\" >\r\ndynamic lookup, rs, row, data;\r\n\r\nlookup = XVar.Clone(XVar.Array());\r\n\r\nrs = XVar.Clone(\r\n\tDB.Query(new XVar(\"select id, color from carsbcolor\"))\r\n);\r\n\r\nwhile (XVar.Pack(data = XVar.Clone(rs.fetchAssoc())))\r\n{\r\n\trow = XVar.Clone(XVar.Array());\r\n\trow.InitAndSetArrayItem(data[\"id\"], null);\r\n\trow.InitAndSetArrayItem(data[\"color\"], null);\r\n\tlookup.InitAndSetArrayItem(row, null);\r\n}\r\n\r\npageObject.setProxyValue(\r\n\tnew XVar(\"lookup\"),\r\n\t(XVar)(lookup)\r\n);\r\n\r\nreturn null;<\/textarea><\/pre>\n<\/div>\n<p>In both cases, the important line is <strong>setProxyValue()<\/strong>. It makes the server-side <strong>lookup<\/strong> array available to JavaScript.<\/p>\n<p>For more information, see the <a href=\"https:\/\/xlinesoft.com\/phprunner\/docs\/setproxyvalue.htm\">PHPRunner setProxyValue() documentation<\/a> or the <a href=\"https:\/\/xlinesoft.com\/asprunnernet\/docs\/setproxyvalue.htm\">ASPRunner.NET setProxyValue() documentation<\/a>.<\/p>\n<h2>Step 2: Use the database values in Dialog API<\/h2>\n<p>Now we can replace the hardcoded options with the values passed from the server:<\/p>\n<div class=\"my-syntax-highlighter\">\n<pre><textarea id=\"mshighlighter\" class=\"mshighlighter\" language=\"js\" name=\"mshighlighter\" >\r\nreturn Runner.Dialog({\r\n\ttitle: 'Preferences',\r\n\tfields: [{\r\n\t\tname: 'color',\r\n\t\tlabel: 'What is your favorite color?',\r\n\t\ttype: 'lookup',\r\n\t\tvalue: 2,\r\n\t\toptions: proxy['lookup']\r\n\t}],\r\n\tok: 'Save',\r\n\tcancel: 'Cancel',\r\n\tbeforeOK: function(popup, controls) {\r\n\t\tswal('Success', 'Selected color: ' + controls[0].val(), 'success');\r\n\t}\r\n});<\/textarea><\/pre>\n<\/div>\n<p>The key line is:<\/p>\n<div class=\"my-syntax-highlighter\">\n<pre><textarea id=\"mshighlighter\" class=\"mshighlighter\" language=\"js\" name=\"mshighlighter\" >\r\noptions: proxy['lookup']<\/textarea><\/pre>\n<\/div>\n<p>The <strong>proxy<\/strong> object contains values that were passed from the server using <strong>setProxyValue()<\/strong>.<\/p>\n<p>There is no need to hardcode the dropdown values in JavaScript. Change the records in the database and the dialog will use the current values the next time the page is loaded.<\/p>\n<h2>Complete example<\/h2>\n<p>Putting everything together, the PHPRunner version consists of two pieces.<\/p>\n<p><strong>BeforeDisplay event:<\/strong><\/p>\n<div class=\"my-syntax-highlighter\">\n<pre><textarea id=\"mshighlighter\" class=\"mshighlighter\" language=\"php\" name=\"mshighlighter\" >\r\n$lookup = array();\r\n\r\n$rs = DB::Query(\"select id, color from carsbcolor\");\r\n\r\nwhile ($data = $rs->fetchAssoc())\r\n{\r\n\t$lookup[] = array(\r\n\t\t$data[\"id\"],\r\n\t\t$data[\"color\"]\r\n\t);\r\n}\r\n\r\n$pageObject->setProxyValue(\"lookup\", $lookup);<\/textarea><\/pre>\n<\/div>\n<p><strong>JavaScript:<\/strong><\/p>\n<div class=\"my-syntax-highlighter\">\n<pre><textarea id=\"mshighlighter\" class=\"mshighlighter\" language=\"js\" name=\"mshighlighter\" >\r\nreturn Runner.Dialog({\r\n\ttitle: 'Preferences',\r\n\tfields: [{\r\n\t\tname: 'color',\r\n\t\tlabel: 'What is your favorite color?',\r\n\t\ttype: 'lookup',\r\n\t\toptions: proxy['lookup']\r\n\t}],\r\n\tok: 'Save',\r\n\tcancel: 'Cancel',\r\n\tbeforeOK: function(popup, controls) {\r\n\t\tswal(\r\n\t\t\t'Success',\r\n\t\t\t'Selected color: ' + controls[0].val(),\r\n\t\t\t'success'\r\n\t\t);\r\n\t}\r\n});<\/textarea><\/pre>\n<\/div>\n<p>The database query can return any two fields you need. The first value in each row becomes the stored value, while the second is displayed to the user.<\/p>\n<p>For example, the same technique could populate a dialog with customers, products, employees, categories or any other lookup data stored in your database.<\/p>\n<p>For more information, see the <a href=\"https:\/\/xlinesoft.com\/phprunner\/docs\/about_dialog_api.htm\">PHPRunner Dialog API documentation<\/a> and the <a href=\"https:\/\/xlinesoft.com\/asprunnernet\/docs\/about_dialog_api.htm\">ASPRunner.NET Dialog API documentation<\/a>.<\/p>\n","protected":false},"excerpt":{"rendered":"<p>Dialog API runs in JavaScript, but the values you want to display in a dropdown often come from a database. The solution is to retrieve those values on the server, pass them to JavaScript with setProxyValue(), and then use them as the lookup options in your dialog. The process has three steps: Query the database and build an array of lookup values. Pass that array to JavaScript using setProxyValue(). Use the resulting proxy value as the Dialog API lookup options. This approach works in both&#8230;<span class=\"clearfix clearfix-post\"><\/span><a href=\"https:\/\/xlinesoft.com\/blog\/2021\/05\/26\/database-based-dropdowns-with-dialog-api\/\" class=\"more-link\">Continue Reading <span class=\"screen-reader-text\">&#8220;Populate Dialog API Dropdowns from a Database in PHPRunner&#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":[16,1,8],"tags":[],"class_list":["post-2406","post","type-post","status-publish","format-standard","hentry","category-asp-net","category-php-category","category-tutorials"],"_links":{"self":[{"href":"https:\/\/xlinesoft.com\/blog\/wp-json\/wp\/v2\/posts\/2406","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=2406"}],"version-history":[{"count":6,"href":"https:\/\/xlinesoft.com\/blog\/wp-json\/wp\/v2\/posts\/2406\/revisions"}],"predecessor-version":[{"id":3444,"href":"https:\/\/xlinesoft.com\/blog\/wp-json\/wp\/v2\/posts\/2406\/revisions\/3444"}],"wp:attachment":[{"href":"https:\/\/xlinesoft.com\/blog\/wp-json\/wp\/v2\/media?parent=2406"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/xlinesoft.com\/blog\/wp-json\/wp\/v2\/categories?post=2406"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/xlinesoft.com\/blog\/wp-json\/wp\/v2\/tags?post=2406"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}