CakePHP:: How to create Dropdown using Custom SQL query

Question

In UsersController:

$query = $this->User->query("SELECT name FROM Type");
$this->set('type',$query);

In view.ctp:

echo $this->Form->input('Type_id',array('options'=>$type));

This adds Admin,Type,1 and User, Type to dropdown.

I want to display only Admin and User in Dropdown.

Type Table contains only Two Columns id and type.

Thanks in advance.

using json_encode, [{"Type":{"name":"Admin"}},{"Type":{"name":"User"}}] — Komal
– Komal, Commented Nov 11, 2013 at 6:39
Is Type a table here? and is it associated with User Model ? — Anil kumar
– Anil kumar, Commented Nov 11, 2013 at 6:40

Anil kumar · Accepted Answer · 2013-11-11 14:50:30Z

1

Try with this

$query = $this->User->query("SELECT id,name FROM Type");    
$query = Hash::combine($query, '{n}.Type.id', '{n}.Type.name');
$this->set('type',$query);

OR

You can also do the above thing if there is a association between the User and Type Models

i.e.

$types = $this->User->Type->find('list', array('fields' => array('id', 'name')));

and in view.ctp

echo $this->Form->input('type_id',array('options'=>$types)); // here better to use type _id instead of Type_id

edited Nov 11, 2013 at 14:50

answered Nov 11, 2013 at 6:32

Anil kumar

4,1774 gold badges25 silver badges37 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Anil kumar Over a year ago

Glad it helped, Better if you set $query using some associations between users and types.

Guillermo Mansilla · Accepted Answer · 2013-11-11 14:20:07Z

0

Use the conventions

Try to avoid plain SQL.

You should use Model::find('list') and adapt it to your needs

Here is the documentation you need.

answered Nov 11, 2013 at 14:20

Guillermo Mansilla

3,8842 gold badges32 silver badges34 bronze badges

Collectives™ on Stack Overflow

CakePHP:: How to create Dropdown using Custom SQL query

2 Answers 2

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related