This is a tough one: How does one read the contents of an enum for a drop down list

Hi,

I have a Table that has as one of its data fields an enum.
This enum contains all the allowable categories for this Table entry.

So my question is how do I read the contents of this enum to create via Php the drop down list of all allowable types for entry into this table?
To be more clear: how do I get from MySQL all allowable values for this field which allowable values are in the enum data definition of this field AND then how do I read these values into Php for generating the
<select name=“type” size=“1”>
<option value=“<?php echo $x; ?>”><?php echo $x; ?></option>
etc.

I told you it is a tough one :slight_smile:

Regards,

Where it is a fixed list of options that can never change then using an enum is a reasonable alternative provided that you also know that it is never going to need to switch to a different database system - since enum is a proprietary mySQL data type.

When you do need to change an enum finding and updating all the code references is the easy part - resolving what to do when you want to change the order of the values in the enum or delete one or more is much harder as it involves updates to all the existing data in the database. That’s why a separate lookup table is easier - even if you don’t provide an option in the application for updating it and rely on manual database updates when it changes (assuming it doesn’t change all that frequently).

There’s a whole chapter in the book “SQL Antipatterns” by Bill Kerwin (published by Pragmatic Bookshelf) that specifically covers the disadvantages of enum (and the equivalent in other sql databases that have an equivalent) and how best to resolve the issues.

I am just curious how do you read this value from MySQL.

I mean if use this MySQL this command:
SHOW COLUMNS FROM faq LIKE ‘faq_type’
how do you read it the value then via Php?

That is when I try to read the value in Php, like this:
$result_get_types = mysql_fetch_array($query_get_types);

$types = $result_get_types[‘faq_type’];

one gets an Error message that:
Notice: Undefined index: faq_type in /var/www/html/anoox.com/news/s_FAQ.php

I don’t think that’s really helping with the question at hand. However, good general advice. (:

The manual page for ENUM in MySQL handily tells you what to do: “If you want to determine all possible values for an ENUM column, use SHOW COLUMNS FROM tbl_name LIKE enum_col and parse the ENUM definition in the Type column of the output.” - Source.

Also, as has been hinted at already, does your schema change often enough to require looking up these ENUM values dynamically: what’s wrong with just writing the values into your source code and changing them on the odd occasion when they change in the database? (Even this can be automated.)

Googling for “php getting mysql enum values” gives some interesting results. The first one for example gives a code example.

Build an iterator. :slight_smile:

…and the winner, of Most Awful Code of the morning is… AnthonySterling!


<?php

function parseEnums($string){
  function enum(){
    return func_get_args();
  }
  return eval('return ' . $string . ';');
}

var_dump(
  parseEnums("enum('WON','LOST','OPEN')")
);

/*

  array(3) {
    [0]=>
    string(3) "WON"
    [1]=>
    string(4) "LOST"
    [2]=>
    string(4) "OPEN"
  }

*/

?>

If it does change frequently enough to not hard code it then it changes frequently enough to use a lookup table instead of an enum so as to make changing the list of allowed values easier.

Oh the shame, sorry OP. Next time I’ll be sure to have a coffee before signing in to SitePoint. :blush:

I wonder if querying information.schema would be a better option…

The types does change that often, but they may from time to time.
And hand coding the types into HTML is just too head ache prone from past experience. Since whenever the types change then one has to find all places where the lists are and manually change all. So all and all i think it is best to place them in a separate table.

OTN, I think enum is a good date type when the choices are between simple things like ‘ON’, ‘Off’ or ‘YES’, ‘No’ etc. simple choices. Dont u think?

Well Thanks to everyone for all your suggestions.

At the end, after looking at all the suggestions, I decided it is best to create a new table and put all the type values in there rather than try to have them as enum values.


<?php

function parse($string){
  return array_filter(
      explode(
      "','",
      substr(
        $string,
        6,
        -2
      )
    )
  );
}

var_dump(
  parse("enum('WON','LOST','OPEN')")
);

/*

  array(3) {
    [0]=>
    string(3) "WON"
    [1]=>
    string(4) "LOST"
    [2]=>
    string(4) "OPEN"
  }

*/

?>

Maybe something like the following:


SELECT
  column_type AS 'enum'
FROM
  information_schema.columns
WHERE
    table_schema = 'schema'
  AND
    table_name = 'table'
  AND
    column_name = 'column'
  AND
    data_type = 'enum'

schema, table and column would need to be adjusted. The above query returns a single column, named ‘enum’ with a value similar to “enum(‘open’,‘won’,‘lost’)”. You need to manually parse this, whether you use SQL or PHP to do this is up to you. :slight_smile:

I do find it strange though that you cannot access the ENUM values directly, I’ll dig some more.

I’m not sure its necessary. For example for a normal varchar field there is a maximum length and other validation rules for the data it’s supposed to store. You don’t normally query the DB to figure that out, it’s hand coded into your PHP. The select could be the same, where the PHP reflects the data structure of the DB.

Just a quick update, the best I’ve come up with (without SPs or ‘temp tables’) is the following:-


SELECT
  REPLACE(
    SUBSTR(
      column_type,
      7,
      LENGTH(column_type) - 8
    ),
    '\\'',
    ''
  ) AS 'column_enums'
FROM
  information_schema.columns
WHERE
    table_schema  = 'schema'
  AND
    table_name    = 'table'
  AND
    column_name   = 'column'
  AND
    data_type     = 'enum';

This returns a column named ‘column_enums’, it will be either empty or contain a comma separated list of values which you can pass to [fphp]explode/fphp.

won,lost,open

I would of course, add an application-side cache of these values with a high TTL.

It looks like I’ve overlooked the possibility of on an ENUM containing an empty string or a NULL, so with that, here’s where I’m at now.


SELECT
  column_type
FROM
  information_schema.columns
WHERE
    table_schema  = 'schema'
  AND
    table_name    = 'table'
  AND
    column_name   = 'column'
  AND
    data_type     = 'enum';


<?php
error_reporting(-1);
ini_set('display_errors', true);

$tests = array(
  "enum('one','two','three')",
  "enum('one')",
  "enum('', NULL)",
  'enum(NULL)',
  'enum()',
  'varchar(10)',
  '           ',
  false,
  true
);

function array_from_enum_def($def){
  $enums = array();
  if(6 < strlen($def) && 'enum' === substr($def, 0, 4)){
    $enums = array_map(
      create_function('$enum', 'return (string)trim($enum, "\\'");'),
      (array)explode(',', substr($def, 5, -1))
    );
  }
  return $enums;
}

print_r(array_map(
  'array_from_enum_def',
  $tests
));
?>

So, instead of parsing the enum definition in MySQL - pass the complete definition to the array_from_enum_def function defined above.


Array
(
    [0] => Array
        (
            [0] => one
            [1] => two
            [2] => three
        )

    [1] => Array
        (
            [0] => one
        )

    [2] => Array
        (
            [0] => 
            [1] =>  NULL
        )

    [3] => Array
        (
            [0] => NULL
        )

    [4] => Array
        (
        )

    [5] => Array
        (
        )

    [6] => Array
        (
        )

    [7] => Array
        (
        )

    [8] => Array
        (
        )

)

I’m going to leave this here. (: