rob
Administrator
Moving on from the previous faq on conditional drop downs which you can find here:
http://fabrikar.com/index.php?option=com_smf&Itemid=9&topic=1858.0
What happens if instead of defining the drop downs in the javascript we want to get the drop down data from a database table?
in this example we have the following tables:
countries
--------------
id
label
towns
--------------
id
country_id (this foreign key states which country the town belongs to)
label
make sure you have some sensible data in both tables .e.g.
countries
id label
1 UK
2 France
towns
id country_id town
1 1 London
2 1 Birmingham
3 2 Paris
4 2 Tolouse
Our first drop down is a simple database join element, with the following options
table:countries
Table's Foreign Key Column: id
Table's Foreign Value Column: label
Our second drop down element, called 'town' is the one that will be updated by our selections in the first drop down, and is a dropdown element type with one sub-option:
value:''
label:'Please select a country'
We now should have the two elements set up on our form. Next we need to create a javascript action that occurs when you select an option in the country drop down:
- edit the country drop down
- select the javascript tab and add a new event.
- set the action to be "onchange"
- enter the following in the code text aread:
Save your element.
Now so far this will not work, as we have not set up our userPluginAjax class on the server - this is the php code that responds to the ajax request and returns the matching data.
Fabrik provides a sample file for this which you can find in /components/com_fabrik/user_ajax_example.php, In this file Hugh has a great introduction to how this file is to be used, and its worth reading!
For the purposes of this example create new file called /components/com_fabrik/user_ajax.php
We now need to add in the method "getFields" to this script, that is to say the function name that is included in the "method=" part of the ajax url
Here's how my user_ajax.php script now looks
Save this file
Now preview your form, and selecting the country should update the town drop down list
http://fabrikar.com/index.php?option=com_smf&Itemid=9&topic=1858.0
What happens if instead of defining the drop downs in the javascript we want to get the drop down data from a database table?
in this example we have the following tables:
countries
--------------
id
label
towns
--------------
id
country_id (this foreign key states which country the town belongs to)
label
make sure you have some sensible data in both tables .e.g.
countries
id label
1 UK
2 France
towns
id country_id town
1 1 London
2 1 Birmingham
3 2 Paris
4 2 Tolouse
Our first drop down is a simple database join element, with the following options
table:countries
Table's Foreign Key Column: id
Table's Foreign Value Column: label
Our second drop down element, called 'town' is the one that will be updated by our selections in the first drop down, and is a dropdown element type with one sub-option:
value:''
label:'Please select a country'
We now should have the two elements set up on our form. Next we need to create a javascript action that occurs when you select an option in the country drop down:
- edit the country drop down
- select the javascript tab and add a new event.
- set the action to be "onchange"
- enter the following in the code text aread:
Code:
//get the country id that has been selected by the user
var cid = this.getValue();
// this is the database table name that contains the information you want to display in the town drop down
var table = 'towns';
//these are the fields in the 'towns' table
var key = 'id';
var label = 'label';
var foreignkey = 'country_id';
// this is the html id of the drop down element we want to update
var dropdown = 'jos_fabrik_formdata_1___town';
//below here you shouldnt need to edit anything
//we want to create an ajax object to send a request to fabrik's userPluginAjax class - this call contains the variables
//outlined above and update the second drop down with the data returned from the server
var url = 'index2.php?option=com_fabrik&no_html=1&task=userPluginAjax&method=getFields';
url += '&table=' + table;
url += '&val='+cid;
url += '&key='+key;
url += '&label='+label;
url += '&foreignkey='+foreignkey;
new Ajax(url, {
method: 'get',
update: $(dropdown)
}
).request();Save your element.
Now so far this will not work, as we have not set up our userPluginAjax class on the server - this is the php code that responds to the ajax request and returns the matching data.
Fabrik provides a sample file for this which you can find in /components/com_fabrik/user_ajax_example.php, In this file Hugh has a great introduction to how this file is to be used, and its worth reading!
For the purposes of this example create new file called /components/com_fabrik/user_ajax.php
We now need to add in the method "getFields" to this script, that is to say the function name that is included in the "method=" part of the ajax url
Here's how my user_ajax.php script now looks
Code:
/* MOS Intruder Alerts */
defined( '_VALID_MOS' ) or die( 'Direct Access to this location is not allowed.' );
class userAjax {
function getFields(){
global $database;
//get all the variables passed in by the ajax objects url
$table = mosGetParam($_REQUEST, 'table');
$key = mosGetParam($_REQUEST, 'key', 'id');
$label = mosGetParam($_REQUEST, 'label', 'label');
$foreignKey = mosGetParam($_REQUEST, 'foreignkey', 'country_id');
$val = mosGetParam($_REQUEST, 'val', '1');
//run a query on the database to get the matching fields
$sql = "SELECT $key AS value, $label AS label FROM $table WHERE $foreignKey = '$val'";
$database->setQuery($sql);
$rows = $database->loadObjectList();
//write the results of the query back to the browser - the javascript code will then assign
//the text to the second drop down
foreach($rows as $row){
echo "<option value=\"$row->value\">$row->label</option>";
}
}
}Save this file
Now preview your form, and selecting the country should update the town drop down list
