There are situations when built-in dependent dropdown boxes won't cut. For instance you need to use a different SQL query based on master dropdown selection or make dropdown box dependent on two fields instead of just one. Here is how this can be done in PHPRunner.
Let consider a simple Cars database.
[size="4"]1. Simple dependent dropdown box[/size]
In Cars table setup both Make and Model as Lookup wizards pointing them respectively to Makes and Models tables.
Proceed to Events screen in PHPRunner and add the following code to Cars table Add page Javascript OnLoad event
var ctrlMake = Runner.getControl(pageid, 'Make');
var ctrlModel = Runner.getControl(pageid, 'Model');
var func = function(e){
$.post("mylookup.php", {
'category' : this.getValue() },
function(data) {
var sel = $("#value_Model_1");
sel.empty();
for (var i=0; i<data.length; i++) {
sel.append('<option value="' + data[i].id + '">' + data[i].desc + '</option>');
}
}, "json");
};
ctrlMake.on('keyup', func);
ctrlMake.on('change', func);
A quick explanation of what this code does. When the value of master control (Make) changes we send a POST request via AJAX to custom PHP file named mylookup.php. This PHP file executes SQL query retrieving data for the second lookup and returns it in JSON format. Once data is received we delete all existing items from the second dropdown and populate it with new items.
Here is the code for mylookup.php file that needs to be created in the output directory.
<?php
include("include/dbcommon.php");
$result = array();
$make = db_addslashes(trim(postvalue('category')));
$sql = "SELECT * FROM Models WHERE Make = '$make'";
$rs = CustomQuery($sql);
while ($data = db_fetch_array($rs)) {
$result[] = array(
'id' => $data['Model'],
'desc' => $data['Model'],
);
}
echo json_encode($result);
?>
Note that in this code we use Model as both Link Field and Display Field. If you want to display a list of models but save their IDs in the database here is how this code needs to be modified (only ID field name changes).
<?php
include("include/dbcommon.php");
$result = array();
$make = db_addslashes(trim(postvalue('category')));
$sql = "SELECT * FROM Models WHERE Make = '$make'";
$rs = CustomQuery($sql);
while ($data = db_fetch_array($rs)) {
$result[] = array(
'id' => $data['id'],
'desc' => $data['Model'],
);
}
echo json_encode($result);
?>
Note: exactly the same code will work if Make is setup as a simple text field. As long as you type correct Make value Model dropdown box will be populated with the list of matching models.
[size="4"]2. Using a different SQL query[/size]
Lets says that data in your Models table is not very clean and Make field contains values like 'Tesla' and 'Tesla Motors'. When someone types Tesla we want to display all models that have 'Tesla' as a part of their Make.
Javascript code won't change, just the SQL part of PHP code will change.
<?php
include("include/dbcommon.php");
$result = array();
$make = db_addslashes(trim(postvalue('category')));
$sql = "SELECT * FROM Models WHERE Make LIKE '%$make%'";
$rs = CustomQuery($sql);
while ($data = db_fetch_array($rs)) {
$result[] = array(
'id' => $data['Model'],
'desc' => $data['Model'],
);
}
echo json_encode($result);
?>
Another slightly artificial example where we select the year of make first and then list all models available for that year or for later years.
<?php
include("include/dbcommon.php");
$result = array();
$year= db_addslashes(trim(postvalue('category')));
$sql = "SELECT * FROM Models WHERE YearOfMake >= $year";
$rs = CustomQuery($sql);
while ($data = db_fetch_array($rs)) {
$result[] = array(
'id' => $data['Model'],
'desc' => $data['Model'],
);
}
echo json_encode($result);
?>
[size="4"]3. Making dropdown box dependent on two other fields[/size]
This is the most interesting and challenging one. Lets assume we have two dropdown boxes (Make and Model) and need to filter YearOfMake dropdown box depending on Make and Model selection.
Javascript OnLoad event
var ctrlMake = Runner.getControl(pageid, 'Make');
var ctrlModel = Runner.getControl(pageid, 'Model');
var func = function(e){
$.post("mylookup.php", {
'make' : ctrlMake.getValue(),
'model' : ctrlModel.getValue() },
function(data) {
var sel = $("#value_YearOfMake_1");
sel.empty();
for (var i=0; i<data.length; i++) {
sel.append('<option value="' + data[i].id + '">' + data[i].desc + '</option>');
}
}, "json");
};
ctrlModel.on('keyup', func);
ctrlModel.on('change', func);
Note that now we need to pass both Make and Model to our mylookup.php file. Here is the updated mylookup.php file.
<?php
include("include/dbcommon.php");
$result = array();
$make= db_addslashes(trim(postvalue('make')));
$model = db_addslashes(trim(postvalue('model')));
$sql = "SELECT * FROM Models WHERE make='$make' and model='$model'";
$rs = CustomQuery($sql);
while ($data = db_fetch_array($rs)) {
$result[] = array(
'id' => $data['YearOfMake'],
'desc' => $data['YearOfMake'],
);
}
echo json_encode($result);
?>
This is it.