Magento – how to read from database table in system.xml for config

When defining your field in your system.xml file you can specify the source_model of this field. For example the source model for the standard yes no drop down is as follows.

<source_model>adminhtml/system_config_source_yesno</source_model>

This will tell the field to use the model Mage_Adminhtml_Model_System_Config_Source_Yesno for building the options. Inside this model you will see the function toOptionArray which

class Mage_Adminhtml_Model_System_Config_Source_Yesno
{

    /**
     * Options getter
     *
     * @return array
     */
    public function toOptionArray()
    {
        return array(
            array('value' => 1, 'label'=>Mage::helper('adminhtml')->__('Yes')),
            array('value' => 0, 'label'=>Mage::helper('adminhtml')->__('No')),
        );
    }
}

So using this as a basis for your requirements what you can do is set-up your own model for example Your_Module_Model_System_Config_Region, then you would simply need to use your table's collection to build the correct structure in the function toOptionArray.

As far as loading one source model based on the value of another you have two options.

Using Frontend Models

Perfect example of this is the tax options for country and region under app/code/core/Mage/Tax/etc/system.xml.

Basically what you need to do is create a frontend_model that extends Mage_Adminhtml_Block_System_Config_Form_Field. This block will only need to have the function _getElementHtml. What you can do then is call the parent function so the default select is created and show and then add some javascript that will update this select based on when another element has changed.

In the tax example it uses the standard RegionUpdater but it is possible to work with the more generic SelectUpdater which allows for one select element to change another.

Now I will show you in depth how this can be achieved. In this example I am using the magento standard country and region collections but you can easily plug in your own collections when needed.

Firstly you will need the system.xml, this should add the correct frontend_model and source_model references:

<config>
    <tabs>
        <example>
            <label>Example</label>
        </example>
    </tabs>
    <sections>
        <example translate="label" module="example_frontendmodel">
            <class>separator-top</class>
            <label>Example</label>
            <tab>example</tab>
            <sort_order>100</sort_order>
            <show_in_default>1</show_in_default>
            <show_in_website>1</show_in_website>
            <show_in_store>1</show_in_store>
            <groups>
                <frontend_model translate="label">
                    <label>Frontend Model</label>
                    <sort_order>30</sort_order>
                    <show_in_default>1</show_in_default>
                    <show_in_website>1</show_in_website>
                    <show_in_store>1</show_in_store>
                    <fields>
                        <country translate="label">
                            <label>Default Country</label>
                            <frontend_type>select</frontend_type>
                            <source_model>example_frontendmodel/system_config_source_country</source_model>
                            <sort_order>10</sort_order>
                            <show_in_default>1</show_in_default>
                            <show_in_website>1</show_in_website>
                            <show_in_store>1</show_in_store>
                        </country>
                        <region translate="label">
                            <label>Default State</label>
                            <frontend_type>select</frontend_type>
                            <frontend_model>example_frontendmodel/adminhtml_frontend_region_updater</frontend_model>
                            <source_model>example_frontendmodel/system_config_source_region</source_model>
                            <sort_order>20</sort_order>
                            <show_in_default>1</show_in_default>
                            <show_in_website>1</show_in_website>
                            <show_in_store>1</show_in_store>
                        </region>
                    </fields>
                </frontend_model>
            </groups>
        </example>
    </sections>
</config>

For this example the two source_models are the same as the standard Mage_Tax source models so there is nothing special there. But you could here load all the options you need for from your tables.

The next thing of importance is the frontend_model here what you do is extend the class Mage_Adminhtml_Block_System_Config_Form_Field and create your own _getElementHtml function. This function will call the parent _getElementHtml and set a variable with the returned html. Then you can build your javascript using the SelectUpdater you can see from the init, in file js/mage/adminhtml/form.js, that you need to specify the following.

1. The first select element's id,
2. The second select element's id,
3. A default message for use on the second select when the first one is not selected,
4. A message to use if the selection from the first select element does not have a match in the second,
5. The values for the second element json encoded,
6. The current selected item,

My example looks as follows.

<?php

class Example_FrontendModel_Block_Adminhtml_Frontend_Region_Updater
    extends Mage_Adminhtml_Block_System_Config_Form_Field
{
    protected function _getElementHtml(Varien_Data_Form_Element_Abstract $element)
    {
        $html = parent::_getElementHtml($element);

        $js = '<script type="text/javascript">
            var updater = new SelectUpdater(
                "example_frontend_model_country",
                "example_frontend_model_region",
                "none",
                "none",
                %s,
                false
            );
            updater.update();
        </script>';

        $html .= sprintf($js, $this->helper('example_frontendmodel')->getRegionJson());
        return $html;
    }
}

The values are built from my helper and replaced into the html string just before being returned. The easiest way to build the values is to create an array of the following format.

array(
    'first_select_value1' => array(
        'second_select_value1' => 'second_select_text1',
        'second_select_value2' => 'second_select_text2',
    ),
    'first_select_value2' => array(
        'second_select_value3' => 'second_select_text3',
        'second_select_value4' => 'second_select_text4',
    )
)

Here is my example's helper for building the values, but you could easily extend it to use what ever collections you need for your need.

<?php
class Example_FrontendModel_Helper_Data extends Mage_Core_Helper_Abstract {
    public function getRegionJson()
    {
        /**
         * array(
         *  'first_value' => array(
         *      'second_value' => 'second_txt'
         *  )
         * )
         */
        $countryIds = array();
        $countryCollection = Mage::getModel('directory/country')->getResourceCollection()
            ->loadByStore();
        foreach ($countryCollection as $country) {
            $countryIds[] = $country->getCountryId();
        }
        $collection = Mage::getModel('directory/region')->getResourceCollection()
            ->addCountryFilter($countryIds)
            ->load();
        $regions = array();
        foreach ($collection as $region) {
            if (!$region->getRegionId()) {
                continue;
            }
            $regions[$region->getCountryId()][$region->getRegionId()] = $this->__($region->getName());
        }
        return Mage::helper('core')->jsonEncode($regions);
    }
}