[RESOLVED] View options for field type enum/set

tuf-web_co_uk

Hi, (havent been on here in a while)

was just wondering if it's possible to select the options for the field type enum or set, so i can produce a drop down menu with the list in, and so i can add more options to it, using ALTER TABLE.

e.g

$sql = 'ALTER TABLE jk_data CHANGE cat cat ENUM(\'Animal\',\'Bar\',\'Blonde\',\'Medical\',\'Other\',\'Sex\',\'Ya Mamma\') NOT NULL';

how can i get the list of options, to be displayed in a web browser, from the enum type in the above sql code.

thank you,
Jon

toplay

Parse the information returned in the second column from a query like this:

SHOW COLUMNS FROM table_name LIKE enum_column_name

However, I don't recommend that you keep using ALTER and adding more enum values like that. The better approach is to design the table in such a way where there's one row per category value.

hth.

tuf-web_co_uk

i'd only be using alter table when i wanted to add a new category, so that wouldn't be very often. would that be any better?

toplay

If it's not "very often" then why would you be wanting to automate it? I think you're kidding yourself. You'll probably end up changing it more times than you care to believe. Think further out into the future.

IMO, the right design would be to have a table called categories and data filled like so:

CATG_ID  CATG_NAME
-------  -----------
1        Animal
2        Bar
3        Blonde
4        Medical

etc. etc. etc.

Then other tables can just store the ID of the category. Example: Let's say there's a customer table:

CUST_ID  CUST_FNAME  CUST_LNAME  CUST_ADDR  etc. etc. etc.
-------  ----------  ----------  --------------
1        John        Doe         123 somewhere
2        Jane        Doe         456 Heaven lane

etc. etc. etc.

Let's say customers can have more than one category, so you could have a customer_category table. Example:

CUST_CATG_ID  CUST_ID  CATG_ID
------------  -------  -------
1             1        2
2             1        3
3             2        1
4             2        4

The above shows that "John Doe" has two categories "Bar" and "Blonde", while "Jane Doe" has "Animal" and "Medical".

That's just an example and as you can see, it allows unlimited category entries and customer categories are unlimited. It's designed with one row per value as opposed to one column containing many possible values.

Make sure your database structure is done right to begin with and is normalized to at least the 3rd normal form. See this Introduction to Database Normalization page:

http://dev.mysql.com/tech-resources/articles/intro-to-normalization.html

If you take the time to design the tables and how data is stored, then future modifications or additions to tables and source code become a lot easier.

🙂