« SQL functions or keywords in CakePHP conditions
Dealing with different configuration for development and production box »

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.  

This entry was posted on Wednesday, December 10th, 2008 at 12:13 am and is filed under CakePHP. You can follow any responses to this entry through the RSS 2.0 feed. You can leave a response, or trackback from your own site.

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

  1. SteveH Says:
    January 16th, 2009 at 5:54 am

    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. Petr 'PePa' Pavel Says:
    January 16th, 2009 at 2:03 pm

    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:
    September 25th, 2009 at 9:34 am

    How about this…

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

  4. Petr 'PePa' Pavel Says:
    September 25th, 2009 at 11:07 am

    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:
    December 8th, 2009 at 1:56 am

    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.

Leave a Reply