CakePHP 2.0 - Use MySQL ENUM field with form helpe

Question

I've been researching a bit and I found that CakePHP's form helper doesn't interpret ENUM fields correctly, so it simply outputs a text input. I found a post that suggested to use a helper for that specific purpose. Does anybody know a better way to achieve this? Or if CakePHP devs intend to correct this some day?

Thanks for reading!

Answer 1

Cake attempts to be database agnostic and therefore this issue won't be "corrected" since it's not a bug. For example, SQL server doesn't have an exact equivalent of MySQL's ENUM field type.

I would recommend getting your possible list of enum values like so:

YourController.php

// get column type
$type = $this->Model->getColumnType('field');

// extract values in single quotes separated by comma
preg_match_all("/'(.*?)'/", $type, $enums);

// enums
var_dump($enums[1]);

Then use a select field in your view and pass the enums as options. Your current value you'll already have. How does that sound?

Answer 2

I created a function that goes into AppController to handle this. I combined some of the information provided above.

Usage:

$enumList = getEnumValues($ModelField)  where ModelField is in this format:  'Model.Field'

Function that I put in AppController:

function getEnumValues($ModelField){

        // split input into Model and Fieldname
        $m = explode('.', $ModelField);
        if ($m[0] == $ModelField) {
                return false;
        } else {                

                (! ClassRegistry::isKeySet($m[0])) ? $this->loadModel($m[0]): false;
                $type = $this->$m[0]->getColumnType($m[1]);
                preg_match_all("/'(.*?)'/", $type, $enums);
                foreach ($enums[1] as $value){$enumList[$value] = $value;}
                return $enumList;
        }                
}

Answer 3

i think the Behaviour way it's good...but the array keys are integer

so i have modified the function like this

function enumOptions($model, $field) {

 //Cache::clear(); 
 $cacheKey = $model->alias . '_' . $field . '_enum_options';
 $options = Cache::read($cacheKey);
 $enumOptions = array();
 if (!$options) {
 $sql = "SHOW COLUMNS FROM `{$model->useTable}` LIKE '{$field}'";
 $enumData = $model->query($sql);


  $options = false;
  if (!empty($enumData)) {
    $enumData = preg_replace("/(enum|set)\('(.+?)'\)/", '\\2', $enumData[0]['COLUMNS']['Type']);
    $options = explode("','", $enumData);

    foreach ($options as $option) {
        $enumOptions["$option"] = $option;
    }
  }
  Cache::write($cacheKey, $enumOptions);
}
return $enumOptions;

}

in order to be able to save the right value in the db field when the form is submitted

Answer 4

I am new to cakephp I found some old code and pieced together an enum select box for you enjoy

/**
* Behavior with useful functionality around models containing an enum type field
*
* Copyright (c) Debuggable, http://debuggable.com
*
*
*
* @package default
* @access public
*
* reworked by Nathanael Mallow for cakephp 2.0
*
*/
/*
 *Use case:Add this (EnumerableBehavior.php)  to app/Model/Behavior/
 *  -->in the Model add public $actsAs = array('Enumerable');     
 *  -->in the *_controller add $enumOptions = $this->Categorie->enumOptions('Section');
 *  -->in the view add print $this->Form->input('{db_field_name}', array('options' =>       $enumOptions, 'label' => 'here'));
 *
 *
 */
class EnumerableBehavior extends ModelBehavior {
/**
 * Fetches the enum type options for a specific field
 *
 * @param string $field 
 * @return void
 * @access public
 */
  function enumOptions($model, $field) {

 //Cache::clear(); 
 $cacheKey = $model->alias . '_' . $field . '_enum_options';
 $options = Cache::read($cacheKey);

 if (!$options) {
  $sql = "SHOW COLUMNS FROM `{$model->useTable}` LIKE '{$field}'";
  $enumData = $model->query($sql);


      $options = false;
      if (!empty($enumData)) {
    $enumData = preg_replace("/(enum|set)\('(.+?)'\)/", '\\2', $enumData[0]['COLUMNS']['Type']);
    $options = explode("','", $enumData);   
      }
      Cache::write($cacheKey, $options);
    }
    return $options;
  }
}
?>

Answer 5

/* comments about previus answers ***

• Use case:Add this (EnumerableBehavior.php) to app/Model/Behavior/
• -->in the Model add public $actsAs = array('Enumerable');
• -->in the action of the *_controller add $enumOptions = $this->YourModelName->enumOptions('db_field_name'); $this->set('enumOptions',$enumOptions);
• -->in the view add print $this->Form->input('{db_field_name}', array('options' => $enumOptions, 'label' => 'here')); * */

Answer 6

If you want to use MySqlEnumFormHelper instead of normal and call it by $this->Form-> instead by $this->MySqlEnumFormHelper . You should add this line in your controller to alias MySqlEnumFormHelper as Form.

public $helpers = array('Form' => array(
    'className' => 'MySqlEnumForm'
));