Zend Mysql get ENUM values

Question

I use Zend Framework in my application. And I want to know how to get values from ENUM field in MySQL table.
For example: i have permissions field (ENUM('delete_admin', 'edit_admin')). How to get array('delete_admin', 'edit_admin') in he best way?
Thank you in advance.

Answer 1

Add this in your Zend_Table class:

$adapter = $this->getDefaultAdapter();
$sql     = 'SHOW COLUMNS FROM `table` LIKE `field`';
$result  = $adapter->fetchRow($sql);

preg_match('=\((.*)\)=is' $options);
str_replace("'", '' $options[1]);
$options = explode(',', $options[1]);

Answer 2

A method for your application model mapper. Assuming your_field_name is the name of the database column that you need to list the ENUM values of.

The method returns an array with the ENUM values of your_field_name column.

public function getAvailableEnumTypes() {
    $this->your_model_name = new Application_Model_DbTable_YourModelName(); // DB table
    $info = $this->your_model_name->info();
    $dat = explode("','", preg_replace("/(enum\('|'\))/", "", $info['metadata']['your_field_name']['DATA_TYPE']));
    return $dat;
}

Answer 3

Add a method to your Zend_Db_Table_Abstract extended class.

public function getEnumValues($field) {
  $metadata = $this->info(self::METADATA);
  preg_match_all('/\'(?<item>.+?)\'/', $metadata[$field]['DATA_TYPE'], $matches);
  return $matches['item'];
}

Answer 4

This is how you can get an explode-ready string from MySQL:

SELECT REPLACE(TRIM(TRAILING ')' FROM (TRIM(LEADING 'enum(' FROM c.COLUMN_TYPE))), '\'', '')
FROM information_schema.`COLUMNS` c
WHERE c.COLUMN_NAME = 'enum_col'

You just need to do a explode(',' $result) on it to get an array with your enum-values.

Remember that you need read-access to information_schema-database to do this.

Answer 5

This is how i did it:

in your model put this

function getInfoTabella()
    {
        $data = $this->info(self::METADATA);
        return $data;
    }

then use this:

$model = new $model_name();
        $description = $model->getInfoTabella();
        $enum = $description[$FIELD_NAME]['DATA_TYPE'];

        $inizia_enum = strpos($enum, "'");
        $finisce_enum = strrpos($enum, "'");
        if ($inizia_enum === false || $finisce_enum  === false)
            throw new Exception('errore enum database');

        $finisce_enum -= $inizia_enum ;


        $enum = substr($enum, $inizia_enum, $finisce_enum+1);
        str_replace("'", '', $enum);
        $enum = explode("," , $enum);
return $enum;

Answer 6

I did it in next way:

$metadata = $this->info(self::METADATA);
$typesString = $metadata['enum_column_name']['DATA_TYPE'];
preg_match("=\((.*)\)=is", $typesString, $parts);
$enumColumnValues = explode("','", trim($parts[1], "'"));