CODEWITHSUNDEEP
phpmysql
MrSplashyPants
MrSplashyPants

Reputation: 293

INSERT into ENUM column

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!

Upvotes: 1

Views: 6075

Answers (4)

Ferdinand Beyer
Ferdinand Beyer

Reputation: 67147

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.

Upvotes: 0

Henrik Opel
Henrik Opel

Reputation: 19441

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.

Upvotes: 0

VoteyDisciple
VoteyDisciple

Reputation: 37803

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.

Upvotes: 0

Gumbo
Gumbo

Reputation: 655239

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

Upvotes: 3

Related Questions