Database backed Zend_Form elements

March 9, 2009 – 9:20 am Tags: , ,

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.

Share this:
  1. 16 Responses to “Database backed Zend_Form elements”

  2. i usually add a method in my model that extends DbTable for making the query for the select, and i save the result as a property so i only do the same query just once.

    By gabriel solomon on Mar 9, 2009

  3. Thank you, this was a though provoking post. From what I’ve seen few or none of the ZF tutorials or books cover this, which is surprising since it’s a very common operation.

    I hadn’t thought of sub-classing the form element before. Certainly useful, especially for doing quick prototyping.

    Somewhere along the way, (at least for display purposes), you’ll also need to turn the identity value back into value. I’m interested in where do you prefer to do this?

    In my current application, I’m using a domain gateway as described in Matthew Weier-O’Phinney’s blog. The gateway has a getFieldList() method that provides the array for the form. There is also a getFieldById() that turns an id back into the value. The array with id -> value mapping is a static member of the class for caching.

    I’ve also been considering having a 2nd type of value object that provides a fully flattened set of values, hiding all of the database lookups and relations.

    Thanks,
    –Rob T.

    By Rob T. on Mar 9, 2009

  4. I believe “init” method usage is more appropriate instead of “render” in this case.

    Something like:
    class Form_Element_Select_News extends Zend_Form_Element_Select {
    public function init() {
    // @todo: add multi options
    }
    }

    By sarunas.d on Mar 10, 2009

  5. It kind of depends. The problem with init() in this case is that it runs right after the constructor.

    If we put the SQL select code in init(), then we can’t first instanciate the element and then use the setters. This is because the init() function has already been executed at this point.

    By Jani Hartikainen on Mar 10, 2009

  6. IMHO, in this case setters can be (and must be) used at construction time by passing values in option array. Method “render” is only executed when rendering form element.

    Look at a common usage scenario:
    $form = new Form();
    if ($isRequestMethodPost && $form->isValid($_POST)) { // method “render” not executed yet, no values in select element – problems with validation
    // Insert record into DB
    }

    echo $form; // method “render” executed

    So the right usage would be:
    $element = new Element(array(‘adapter’ => $adapter, ‘sql’ => ‘SELECT * FROM table’, …));

    Not the:
    $element = new Element();
    $element->setAdapter($adapter);
    $element->setSql(…);

    By sarunas.d on Mar 10, 2009

  7. Setters, in this case, is only required for Dependency Injection at construction time.

    By sarunas.d on Mar 10, 2009

  8. You could use the Zend_Db_Adapter method fetchPairs to fetch the associative array, so with the query “SELECT id, name FROM news” the ID would be the key and name would be the value. That would cut down the amount of code and options required.

    Nice post :)

    By Tom Graham on Mar 11, 2009

  9. I would be thankful if any one tell me how PEAR can be used with Zend Framework ?

    Regards,
    Rej

    By zendrej on Mar 12, 2009

  10. You can use PEAR classes with ZF based apps the same way you’d use them with non-ZF apps.

    By Jani Hartikainen on Mar 14, 2009

  11. Thanks for the article. Works a treat.

    I’m a bit a noob, so to help others out on the bits I got stuck on:

    1. I was getting a “’1′ was not found in the haystack” error.

    After some googling I resolved by adding:

    $el->setRegisterInArrayValidator(false)
    ->setRequired(true)
    ->addValidator(‘NotEmpty’)
    ->addErrorMEssage(‘*’);

    But I guess you could write those in as methods too. I couldnt really understand what “setRegisterInArrayValidator(false)” actually does, but there we go, I’m a noob, and it’s working for now :)

    Also, I keep my DB config in Zend_Registry, so I just added this to my form class before I called the extended class: $adapter = Zend_Registry::get(‘db’);

    Might help someone out :)

    thanks for the great posts.

    By firecall on Mar 22, 2009

  12. Hi!

    Great post, but I wouldn`t be myself if I use it “as is” so here goes my approach: http://pastebin.com/f1e3a2350

    The main difference is that used mentioned “fetchPairs” and create own SQL so that you can use such element with config file (INI or XML) but you don`t have to write comples queries there (in such case just create “view” in your DB and pass it as table name).

    I`ve also moved initiation to getMultiOptions() so that both validation and field populatnio are covered and “lazy loading” of data is also maintained :-)

    Regards

    By MichaƂ Bachowski on Mar 30, 2009

  13. Facing problem in creating a form…………please help me in doing this

    By Karan on Apr 6, 2009

  14. it’s a great post. but for newbies like me it’s a little hard to put it in action. can someone possibly give me an example on how to use it in a form that is posted and retrieve that posted variable on the second step.

    Thanks,

    By Arash on Sep 25, 2009

  15. Thank you very much! It helped me a lot. Everything just works!

    By ion7 on Nov 24, 2009

  16. Thanks! This was a really handy snippet. Too bad Zend doesn’t support this by default. After all, it isn’t rare to fill an select element with database data, right? Special thanks to Michael Bachowski for his modification to use the DbTable adapter.

    By Martijn Dwars on Aug 24, 2010

  1. 1 Trackback(s)

  2. Jun 23, 2010: Zend Framework – A Win « Zenwerx

Post a Comment

You can use some HTML (a, em, strong, etc.). If you want to post code, use <pre lang="PHP">code here</pre> (you can replace PHP with the language you are posting)