Populate Dialog API Dropdowns from a Database in PHPRunner

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:

1. Query the database and build an array of lookup values.
2. Pass that array to JavaScript using setProxyValue().
3. Use the resulting proxy value as the Dialog API lookup options.

This approach works in both PHPRunner and ASPRunner.NET. The server-side code is different, but the Dialog API JavaScript is the same.

Starting with a hardcoded dropdown

A Dialog API lookup field can use a two-dimensional array containing the stored value and display value for each entry.

For example:

return Runner.Dialog({
	title: 'Preferences',
	fields: [{
		name: 'color',
		label: 'What is your favorite color?',
		type: 'lookup',
		value: 2,
		options: [
			[1, 'red'],
			[2, 'green'],
			[3, 'yellow']
		]
	}],
	ok: 'Save',
	cancel: 'Cancel',
	beforeOK: function(popup, controls) {
		swal('Success', 'Selected color: ' + controls[0].val(), 'success');
	}
});

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.

Instead, we can retrieve them before the page is displayed.

Step 1: Retrieve the lookup values from the database

Add the following code to the BeforeDisplay event of the page where you will use the Dialog API.

PHPRunner

$lookup = array();

$rs = DB::Query("select id, color from carsbcolor");

while ($data = $rs->fetchAssoc())
{
	$row = array();
	$row[] = $data["id"];
	$row[] = $data["color"];
	$lookup[] = $row;
}

$pageObject->setProxyValue("lookup", $lookup);

This code retrieves the values from the database and builds an array in the format expected by a Dialog API lookup:

[
	[1, 'red'],
	[2, 'green'],
	[3, 'yellow']
]

The actual values, of course, come from your database.

ASPRunner.NET

The equivalent C# code is:

dynamic lookup, rs, row, data;

lookup = XVar.Clone(XVar.Array());

rs = XVar.Clone(
	DB.Query(new XVar("select id, color from carsbcolor"))
);

while (XVar.Pack(data = XVar.Clone(rs.fetchAssoc())))
{
	row = XVar.Clone(XVar.Array());
	row.InitAndSetArrayItem(data["id"], null);
	row.InitAndSetArrayItem(data["color"], null);
	lookup.InitAndSetArrayItem(row, null);
}

pageObject.setProxyValue(
	new XVar("lookup"),
	(XVar)(lookup)
);

return null;

In both cases, the important line is setProxyValue(). It makes the server-side lookup array available to JavaScript.

For more information, see the PHPRunner setProxyValue() documentation or the ASPRunner.NET setProxyValue() documentation.

Step 2: Use the database values in Dialog API

Now we can replace the hardcoded options with the values passed from the server:

return Runner.Dialog({
	title: 'Preferences',
	fields: [{
		name: 'color',
		label: 'What is your favorite color?',
		type: 'lookup',
		value: 2,
		options: proxy['lookup']
	}],
	ok: 'Save',
	cancel: 'Cancel',
	beforeOK: function(popup, controls) {
		swal('Success', 'Selected color: ' + controls[0].val(), 'success');
	}
});

The key line is:

options: proxy['lookup']

The proxy object contains values that were passed from the server using setProxyValue().

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.

Complete example

Putting everything together, the PHPRunner version consists of two pieces.

BeforeDisplay event:

$lookup = array();

$rs = DB::Query("select id, color from carsbcolor");

while ($data = $rs->fetchAssoc())
{
	$lookup[] = array(
		$data["id"],
		$data["color"]
	);
}

$pageObject->setProxyValue("lookup", $lookup);

JavaScript:

return Runner.Dialog({
	title: 'Preferences',
	fields: [{
		name: 'color',
		label: 'What is your favorite color?',
		type: 'lookup',
		options: proxy['lookup']
	}],
	ok: 'Save',
	cancel: 'Cancel',
	beforeOK: function(popup, controls) {
		swal(
			'Success',
			'Selected color: ' + controls[0].val(),
			'success'
		);
	}
});

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.

For example, the same technique could populate a dialog with customers, products, employees, categories or any other lookup data stored in your database.

For more information, see the PHPRunner Dialog API documentation and the ASPRunner.NET Dialog API documentation.