Database backed Zend_Form elements

A relatively common task when working with forms is filling select boxes or checkbox and radiobutton lists from a database.

It’s not very tricky to query a database, and fill a Zend_Form element’s possible values from the resultset, but it can be quite boring and extraneous code…

We could also create an element which we can give some SQL, and it’ll fill itself automatically! This way you can even define database-backed elements in XML or INI configuration files.

Creating a database-backed Zend_Form_Element_Select

Let’s use a select box element as an example. First, let’s quickly look how you would typically fill it with some values:

$el = new Zend_Form_Element_Select(array('name' => 'somename'));
$el->setMultiOptions(array(
 '1' => 'One',
 '2' => 'Two'
));

To do the above from a database, we would first need to query the db, fetch a resultset and maybe parse the rows into some other format.

Now, let’s look at how we could automate all that. We will extend the Zend_Form_Element in question, adding some new methods and variables to it so we can use it for this purprose:

class CU_Form_Element_DbSelect extends Zend_Form_Element_Select {
  private $_dbAdapter;
  private $_dbSelect;
 
  private $_identityColumn = 'id';
  private $_valueColumn = '';
 
  /**
   * Set the database adapter used
   * @param Zend_Db_Adapter_Abstract $adapter
   */
  public function setDbAdapter(Zend_Db_Adapter_Abstract $adapter) {
    $this->_dbAdapter = $adapter;
  }
 
  /**
   * Set the query used to fetch the data
   * @param string|Zend_Db_Select $select 
   */
  public function setDbSelect($select) {
    $this->_dbSelect = $select;
  }
 
  /**
   * Set the column where the identifiers for the options are fetched
   * @param string $name
   */
  public function setIdentityColumn($name) {
    $this->_identityColumn = $name;
  }
 
  /**
   * Set the column where the visible values in the options are fetched
   * @param string $name
   */
  public function setValueColumn($name) {
    $this->_valueColumn = $name;
  }
 
  public function render(Zend_View_Interface $view = null) {
    $this->_performSelect();
    return parent::render($view);
  }
}

You may notice the _performSelect method is missing. We’ll look at it in a bit, but let’s first go over what we did here.

We need to be able to set and store the database adapter and the select query used, so we add setters and variables for them. We also add two pairs more for identity and value columns. What are these?

The “identity column” is where the value property of the option will be taken from. Usually this is the ID of the row, so this one gets a default value set to that. The “value column” is the visible text that’s shown in the option. Since this one usually differs each time, it doesn’t have a default value.

We also override the render method, which calls another method to perform the select, and then calls the original render method to actually render the element. Since the actual HTML rendering part doesn’t change, we can use the old one.

Now, the missing method:

private function _performSelect() {
  if(!$this->_dbAdapter)
    $this->_dbAdapter = Zend_Db_Table::getDefaultAdapter();
 
  $stmt = $this->_dbAdapter->query($this->_dbSelect);
  $results = $stmt->fetchAll(Zend_Db::FETCH_ASSOC);
  $options = array();
 
  foreach($results as $r) {
    if(!isset($r[$this->_identityColumn])) {
      throw new Zend_Form_Element_Exception(
        'Identity column is not present in the result');
    }
 
    if(!isset($r[$this->_valueColumn])) {
      throw new Zend_Form_Element_Exception(
        'Value column is not present in the result');
    }
 
    $options[$r[$this->_identityColumn]] = $r[$this->_valueColumn];
  }
 
  $this->setMultiOptions($options);
}

This one performs the query, and then maps the result into an array which can be used with the setMultiOptions method shown earlier.

This method also checks if the db adapter was set. In case it isn’t set, it attempts to get one from Zend_Db_Table. I’ll tell you why this is useful in a bit!

Using this class

The easiest way to use this class with Zend_Form is to instanciate it manually like this:

//Let's assume $form is our Zend_Form object, and $adapter is a Zend_Db_Adapter object
$el = new CU_Form_Element_DbSelect(array(
  'name' => 'someList',
  'dbAdapter' => $adapter,
  'dbSelect' => 'SELECT id, name FROM news',
  'valueColumn' => 'name',
  'label' => 'Choose news item'
));
 
$form->addElement($el);

This will produce a select element with each item from the news table in it – quite simple, no? Because we defined those setter methods earlier, Zend_Form_Element’s magic handles it for us so that we can nicely pass these right in the array, rather than manually calling each setter.

You could also use a Zend_Db_Select object in place of the SQL query if you want.

Benefits and drawbacks

The main benefits of this approach show when you want to create forms quickly, for example when you’re still developing your application. With this, you can easily define where the data in the element comes from without having to write code to query the database manually.

This is also where the main drawback lies: because each element of this kind will perform an SQL query, you may suddenly find a lot of queries, depending on the setup.

As an added bonus, this also works with Zend_Config based forms! If you like building your forms in XML or INI files, you can use this element and define all this in the configuration. Just leave out the dbAdapter value, and remember to set a default adapter in Zend_Db_Table in your bootstrap or such – this is why we test it in the element’s code.

Implementing this for checkboxes or radios would follow a quite similar process – you’d just mainly change the class which is being extended.

Want to learn a bit more about Zend_Form elements? Check out complex custom elements in Zend_Form.

I’ve also written a post on Zend_Form decorators, which you may be interested in if you want to modify the HTML markup of a form.