Populating a SELECT box with distinct values

Let’s say you log events into a database table Log. Attribute event holds event type and you want to filter the log listing just by that.

Here’s what you probably try at first:

  1.  
  2. $this->set(‘events’,
  3.   $this->Log->find(‘list’,
  4.     array(‘fields’ => ‘DISTINCT Log.event’, ‘Log.event’)));
  5.  

It’s not gonna work and you will have to use find(‘all’) and Set::combine() to get what you want:

  1.  
  2. $events = $this->Log->find(‘all’,
  3.   array(‘fields’ => ‘DISTINCT Log.event’));
  4. $this->set(‘events’,
  5.   Set::combine($events, ‘{n}.Log.event’, ‘{n}.Log.event’));
  6.  

6 Responses to “Populating a SELECT box with distinct values”

  1. SteveH says:

    Ok,

    I’m still perplexed with simply populating a SELECT box with distinct values from a table column.

    Something like:

    $this->set(‘citylist’, $this->Zldinput->find(‘list’, array(‘fields’=>’Zldinput.City’, ‘group’=>’Zldinput.City’)));

    gets me close, but as you mention my array has elements of the id and City thus my SELECT gets

    BOSTON

    where I want the ……value=”BOSTON”>BOSTON.

    your solution is close but combine throws an error on $events

    Cheers, Steve

  2. Hi Steve, that’s weird that you’re getting an error.
    I’m using CakePHP 1.2.0.7692 RC3 and my real code differs from the example I have given in my post only in that I specify additional options like “order” and “recursive” for the find() call.

    My best guess is that your find call doesn’t return a valid search result array (e.g. because of a typo in a field name), or you changed the variable name that holds it but forgot to change it later in the combine call as well. I create this kind of errors all the time.

    Petr

  3. Ming says:

    How about this…

    $list = $this->find(‘list’, array(‘fields’ => array(‘Model.field’)));
    return array_unique($list);

  4. Ming, your code wouldn’t work for several reasons:

    1. If you just return something then you can’t use it in your view to populate SELECT box. Maybe you omitted $this->set() just to keep your code simple though.
    2. With $this->set() your solution would pass through
      array(0 => value1, 1 => value2 …)
      and thus if I choose value1 in the SELECT box it’s in fact “0” that gets submitted.
      We need
      array(value1 => value1, value2 => value2 …)
      because we want value1 submitted.
    3. I also considered that you were in fact, just suggesting to use find(‘list). But then again you get
      array(0 => value1, 1 => value2 …)

    But thanks for commenting. Expressing opinions, making suggestions. That’s what these comments are for.

  5. CyberCortex says:

    After a lot of tests, I finally solved this problem with this ridiculous line:

    $years = $this->Payments->find(‘list’, array(‘fields’ => array(‘year’, ‘year’)));

    Sorry for my bad english, I am Brazilian.

  6. philthathril says:

    If you don’t care about the ids, just the value, then you can do something like this…

    $list = $this->Item->find(‘list’, array(‘fields’=>array(‘Model.field’)));
    foreach ($list as $item) {
    $items[$item] = true;
    }
    $this->set(‘something’, array_keys ($items));

    This may not be ideal, but it’ll get the job done. Hope this helps!

    ~Philip

Leave a Reply