INSERT into ENUM column

Question

Hi, I have a ENUM column in db like so, ENUM('us','uk','fr','intl')

I'm using checkboxes to confirm the country in HTML and the PHP is

SET country ='".$country_us." ".$country_uk." ".$country_fr." ".$country_intl."'

The query passes but it does not add to the column.

If I do just

country ='$country_us'

-- it works fine.

Here's a previous question of mine for more info. Thanks for your help!

Answer 1

An ENUM can only be one value at a time. If you want to allow multiple values at a time, use SET.

Answer 2

It sounds like you're trying to insert multiple values into a single ENUM column. This is not possible.

If you could have only one country value, I'd recommend using radio buttons, and then your second example would work correctly. However, since you might want to have country_us and country_uk, an ENUM column is not capable of storing that information.

A better model for this sounds like something best addressed in your original question.

Answer 3

The ENUM column means that you can store only one value in it, and that value has to be one of the defined ENUM members.

You should turn your checkboxes to radio buttons, so that a user can only select one country. In your code, you extract that single value and insert it into the column.

Answer 4

You cannot store multiple values in a (MySQL) ENUM column. ENUM means, that you can insert exactly one of the values defined for the column. Use a SET column instead and update its values like this:

UPDATE ... SET country = 'uk,us';

Ie, assign a comma-separated list of allowed values. See the mysql documentation for more information.

Even better, use separate tables for this:

CREATE TABLE country (
    cty_id     INTEGER NOT NULL PRIMARY KEY,
    cty_code   CHAR(2) NOT NULL UNIQUE,
    cty_title  VARCHAR(30) NOT NULL,
);

CREATE TABLE map_story_country (
    story_id INTEGER,
    cty_id   INTEGER,
    PRIMARY KEY (story_id, cty_id)
);

Then, insert an ID-mapping for every country you want to assign to your story/article.