Cakephp query for enum field in database

Question

I have a field in database. It's type is enum and it looks like

enum('NO ANSWER', 'ANSWERED', 'BUSY').

I need to put this values into dropdown. How can I write query in cakephp? I tried:

$result = TableRegistry::get('Calls')->find('list', ['valueField' => 'disposition'])->distinct('disposition')->toArray();

But it returns

[
(int) 1 => null,
(int) 77 => '',
(int) 64 => 'NO ANSWER',
(int) 65 => 'ANSWERED',
(int) 72 => 'BUSY'
]

but I need something like this:

[
(int) 1 => 'NO ANSWER',
(int) 2 => 'ANSWERED',
(int) 3 => 'BUSY'
]

score 2 · Answer 1 · answered Jul 05 '16 at 10:00

I need to put this values into dropdown

Unless the enum values are going to change frequently (and if the are, why would you use an enum..) just put the array of data you need somewhere:

$options = [
    'NO ANSWER' => 'NO ANSWER',
    'ANSWERED' => 'ANSWERED',
    'BUSY' => 'BUSY'
];

And then use it:

echo $this->Form->select('field', $options);

Note that the key in $options is what will be submitted, the value is what will be displayed. More info about the select method is in the documentation.

score 2 · Answer 2 · answered Jul 06 '16 at 17:31

I found this answer somewhere on SO, but I couldn't find it again. You can do this:

$cols = $this->Model->query("show columns from table_name like 'enum_column_name'")
$enum = explode(',', substr(str_replace(array("'", "(", ")"),'',$cols[0]['COLUMNS']['Type']), 4));
$options = array_combine($enum, $enum);

Then in your form, you can use the end of AD7six's answer and add:

echo $this->Form->select('field', $options);

score 0 · Answer 3 · edited May 23 '17 at 12:25

The problem is that the values of enum are defined in the create table, they are not a piece of data available when you query your table's data. How can I get enum possible values in a MySQL database? SO topic describes how to get the values of the enum through a php code. Just make sure that you reassign the keys for the enum values so that the keys start from 1, and not from 0 (0 stands for empty value).

Cakephp query for enum field in database

3 Answers3