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).
"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.
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.
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.