How to fill a Symfony choice widget dynamically with the result of a DB query

Question

In one of my forms I would like to have a dropdown (sfWidgetFormChoice) where the options are generated dynamically by executing a query on the database.

To be a little bit more precise, I'm going to list all versions which I have in the table. The query looks something like this:

select distinct version from mytable order by version desc

What I have so far but doesn't work:

class myForm extends sfForm

$query = "select distinct version from mytable order by version desc";

$versions = Doctrine_Manager::getInstance()->getCurrentConnection()->fetchAssoc($query);

public function configure()

$this->setWidgets(array('version' => new sfWidgetFormChoice(array('choices' => self::$versions))));

Edit:

Thansk guys for your answers! Much appreciated!

Anyhow your solutions base on having a model of the table. I'd rather have a PDO directly as it's faster.

In the Symfony Documentation I found what I was looking for under "Using Raw SQL Queries".

So I extended my Form to end up with this:

class myForm extends sfForm
{
  public function getVersions()
  {
    $connection = Doctrine_Manager::connection();
    $query      = "select distinct version from mytable order by version desc";
    $statement  = $connection->prepare($query);
    $statement->execute();
    $resultset = $statement->fetchAll(PDO::FETCH_COLUMN, 0);

    return $resultset;
  }

  public function configure()
  {
    $this->setWidgets(array('version' => new sfWidgetFormChoice(array('choices' => self::getVersions()))));
  }
}

As a result of this my dropdown gets properly filled with what is in my table, yay! BUT I get also warnings:

Warning: Invalid argument supplied for foreach() in lib/vendor/symfony/lib/plugins/sfDoctrinePlugin/lib/database/sfDoctrineConnectionProfiler.class.php on line 196

Warning: join() [function.join]: Invalid arguments passed in lib/vendor/symfony/lib/plugins/sfDoctrinePlugin/lib/database/sfDoctrineConnectionProfiler.class.php on line 141

Warning: Invalid argument supplied for foreach() in lib/vendor/symfony/lib/plugins/sfDoctrinePlugin/lib/database/sfDoctrineConnectionProfiler.class.php on line 196

Warning: Invalid argument supplied for foreach() in lib/vendor/symfony/lib/plugins/sfDoctrinePlugin/lib/debug/sfDoctrineConnectionProfiler.class.php on line 117

Any idea what I'm doing wrong here??? Oddly enough the dropdown looks fine.

Answer 1

I finally found the solution I was looking for by myself. This accesses the DB now directly. The reason why I don't use FETCH_COLUMN anymore is that this would create an array where the key is an Id starting from 0 and the value is whatever is returned by the query itself. Instead I wanted to have the key and value to be whatever the query delivers. This is why the query now delivers two columns being the same and then FETCH_KEY_PAIR does the rest.

In case anyone else is interested in how I solved this, here is my code which is filling the drop down properly and causing no errors.

class myForm extends sfForm
{
  public function getVersions()
  {
    $connection = Doctrine_Manager::getInstance()->getCurrentConnection()->getDBh();
    $query      = "select distinct version as key, version as value from mytable order by version desc";
    $statement  = $connection->prepare($query);
    $statement->execute();
    $resultset = $statement->fetchAll(PDO::FETCH_KEY_PAIR);

    return $resultset;
  }

  public function configure()
  {
    $this->setWidgets(array('version' => new sfWidgetFormChoice(array('choices' => self::getVersions()))));
  }
}