CODEWITHSUNDEEP
mysqldatabasecakephpenums
Bird
Bird

Reputation: 81

Cakephp query for enum field in database

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'
]

Upvotes: 1

Views: 2280

Answers (3)

dragmosh
dragmosh

Reputation: 393

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);

Upvotes: 2

AD7six
AD7six

Reputation: 66237

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.

Upvotes: 2

Shadow
Shadow

Reputation: 34232

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

Upvotes: 0

Related Questions