CakePHP show data from another linked table in dropdown

I have built a CakePHP app that creates appointments which have both a client and a doctor assigned to them from other tables in the database.

On the appointment add screen I want to be able to choose the Doctor name from a drop down (note the name consists of two fields, firstname and lastname and their is also a field called title for the prefix e.g. Mr). The information it SHOULD save though is the doctor_id as that is how the two tables are linked!

I have added the following to my view:

<?php 开发者_如何学Cecho $this->Form->input('doctor_id',array('label'=>'<strong>Choose Doctor</strong>'),$doctors); ?>

and then in my controller:

$this->set('doctors', $this->Appointment->Doctor->find('list'));

However this creates a list of random elements from the database. How can I get this to work? So the dropdown produced is something like:

<select>
<option value="1">Mr. John Doe</option>
<option value="2">Mrs. Jane Doe</option>
</select>

You can specify the fields you want to show in the drop down list by adding a 'fields' parameter to the find(), and you can use a virtualField in your Doctor model to get a name (or fullname).

doctor.php:

public function __construct($id = false, $table = null, $ds = null) {
    parent::__construct($id, $table, $ds);

    // This is for MySQL, change for whatever DB flavour you're using.
    $this->virtualFields = array(
      'name' => "CONCAT(`{$this->alias}`.`prefix`, ' ', `{$this->alias}`.`firstname`, ' ', `{$this->alias}`.`lastname`)"
  );
}

Change your find('list') in your controller to:

$this->set('doctors', $this->Appointment->Doctor->find(
  'list',
  array(
    'fields' => array('Doctor.name')
  )
);

You can of course add a 'order' parameter...

$this->set('doctors', $this->Appointment->Doctor->find(
  'list',
  array(
    'fields' => array('Doctor.name'),
    'order' => array('Doctor.firstname', 'Doctor.lastname')
  )
);

Hope that helps.

they are not random.

but you would want to use "order"=>array('id'=>'ASC') in your find() method in order to sort them by id