Does Listdata Create A Distinct List

Yii 1.1 General Discussion
1

I have the following code in a form i need it to display all Client names except its removing any duplicates.




<div class="row">

<?php echo $form->labelEx($model,'Client_Name'); 

$list = CHtml::listData(ClientDetails::model()->findAllbySql('SELECT Client_Name, concat(Client_Name,"  (",Broker_Name,")",Broker_Firstname ," ",Broker_Surname," ","(",Account_Number,")",".") as "Broker_Surname" FROM client_details ORDER BY Client_Name'), 'Client_Name', 'Broker_Surname');

echo $form->dropDownList($model, 'Client_Name', $list,array('empty'=>'--please select--'));

echo $form->error($model,'Client_Name'); ?>

</div><!-- row -->

It’s returning the Query fine except that it is displaying in the dropdownlist a Distinct list based on the client name.

My Questions are:

  1. Does listData create a distinct list if so how do i get around it.

  2. Have i coded my dropdownlist incorrectly causing it to only display distinct values if so where can i fix it.

2 



SELECT Client_Name, 

  concat(Client_Name,"  (",Broker_Name,")", Broker_Firstname ," ",Broker_Surname," ","(",Account_Number,")",".") 

    AS "Broker_Surname" 

FROM client_details ORDER BY Client_Name

I don’t mean to be insulting but this is a very ugly bit of SQL. Before I did anything else I’d revise it:




SELECT Client_Name, Broker_Name, Broker_Firstname, Broker_Surname, Account_Number 

FROM client_details ORDER BY Client_Name

I’d leave the concatenation to PHP because not only is it more efficient (not sending a bunch of extra text between PHP and MySQL servers) but it is much cleaner looking. I also wonder why you’ve named your columns the way you did. For example, why are you storing information about the broker in your clients table? You should probably have a separate ‘Brokers’ table for that information and then the only thing you’d store in the clients table is an ID from the brokers table. I’m not going to be able to help you with the rest of this until you get a little better design going. Not because I don’t want to but because there is no right answer to the best way to use a VARCHAR called ‘Client_name’ as a key field (the best way to do that is to not do it).

3

No Offense taken.

Its like that as it needs to be displayed as

"Client Name (Brokerage Name) Broker firstname Broker Surname (Account Number)." In the dropdownlist.

Looks Like hell i know. I think i have it sorted now where i create a second concat with the client name and account number but ill have to explode out the account number in my model.

I’ll let you have a look at the code when I’m done but i still think listdata is creating a list based on distinct values.

4

First, go to your model for ClientDetails and add this:




public function getBrokerName() {

  return 'my incredibly long concat: '. $this->Client_Name . '('.$this->Broker_Name.') ... you can finish';

}

Then make some small changes to your view:




<div class="row">

<?php echo $form->labelEx($model,'Client_Name');

$clients = ClientDetails::model()->findAll();

$list = CHtml::listData($clients, 'Client_Name', 'brokerName'); // <-- this last parameter is the same as the new function name above (without the 'get' part)

echo $form->dropDownList($model, 'Client_Name', $list, array('empty'=>'--please select--'));

echo $form->error($model,'Client_Name'); ?>

</div><!-- row -->

I still want you to move all of those broker fields to another table too :). This should get you going though.

5

The key will be distinct because CHtml::listData() builds and returns an associative array. The value of each entry will be that of the last duplicate record that the query returns.

The key of the listData array represents the value attribute of the generated option tag, so you won’t have two options with the same value.

6

LOL, Keith just reminded me that I’ve been answering my own problems with this code and not the posters’.