another MySQL syntax thingy

[Ddr]

Hi everyone. I am leaving something out of one of the field parameters apparently, but have not been able to figure out this silly thing. This is a table I am trying to create:

CREATE TABLE `user_info2` (

`id` INT NOT NULL AUTO_INCREMENT,
`uid` INT NOT NULL ,
`gender` VARCHAR( 10 ) NOT NULL ,
`pref` VARCHAR( 10 ) NOT NULL ,
`coming` VARCHAR( 12 ) NOT NULL ,
`smoker` VARCHAR( 12 ) NOT NULL ,
`food_req` ENUM,
`food_probs` TEXT,
`exper` VARCHAR( 15 ) NOT NULL ,
`see_demo` ENUM,
`see_more` TEXT,
`give_demo` ENUM,
`givedemo_more` TEXT,
PRIMARY KEY ( `id` )
)

Upon trying to create, PHPMyAdmin sends this message:

MySQL said:

You have an error in your SQL syntax. Check the manual that corresponds to your MySQL server version for the right syntax to use near ' `food_probs` TEXT, `exper` VARCHAR( 15 ) NOT NULL , `see

It's been a long week. Can anyone spot the problem here? Thanks, I really appreciate the help.

Dale

[HalfaBee]

Enum needs to be defined like this

ENUM("one", "two", "three")

[Ddr]

Thanks, Halfabee, that puts me on the right track ( and I just listened to that Monty Python sketch the other day *S*)

Can I ask for a little more information. I have 3 columns that I want to use ENUM for. In each case, I want to store a string of values selected from a checklist on a form. The first enum, mentioned above, has 5 options. The second and third have 11 options, each. Can you please show the correct way to quantify enum? Or am I making a mistake in choosing enum to store the string? Any advice would be greatly appreciated.

TIA

Dale

[HalfaBee]

I don't use the ENUM type very often.
It probably depends on what you want to do with the information being stored.


This is from the manual

The ENUM Type

An ENUM is a string object whose value normally is chosen from a list of allowed values that are enumerated explicitly in the column specification at table creation time.

The value may also be the empty string ("") or NULL under certain circumstances:


· If you insert an invalid value into an ENUM (that is, a string not present in the list of allowed values), the empty string is inserted instead as a special error value. This string can be distinguished from a 'normal' empty string by the fact that this string has the numerical value 0. More about this later.

· If an ENUM is declared NULL, NULL is also a legal value for the column, and the default value is NULL. If an ENUM is declared NOT NULL, the default value is the first element of the list of allowed values.

Each enumeration value has an index:

· Values from the list of allowable elements in the column specification are numbered beginning with 1.

· The index value of the empty string error value is 0. This means that you can use the following SELECT statement to find rows into which invalid ENUM values were assigned:


mysql> SELECT * FROM tbl_name WHERE enum_col=0;

· The index of the NULL value is NULL.

For example, a column specified as ENUM("one", "two", "three") can have any of the values shown below. The index of each value is also shown:

Value Index
NULL NULL
"" 0
"one" 1
"two" 2
"three" 3
An enumeration can have a maximum of 65535 elements.

Lettercase is irrelevant when you assign values to an ENUM column. However, values retrieved from the column later have lettercase matching the values that were used to specify the allowable values at table creation time.

If you retrieve an ENUM in a numeric context, the column value's index is returned. For example, you can retrieve numeric values from an ENUM column like this:


mysql> SELECT enum_col+0 FROM tbl_name;

If you store a number into an ENUM, the number is treated as an index, and the value stored is the enumeration member with that index. (However, this will not work with LOAD DATA, which treats all input as strings.)

ENUM values are sorted according to the order in which the enumeration members were listed in the column specification. (In other words, ENUM values are sorted according to their index numbers.) For example, "a" sorts before "b" for ENUM("a", "b"), but "b" sorts before "a" for ENUM("b", "a"). The empty string sorts before non-empty strings, and NULL values sort before all other enumeration values.

If you want to get all possible values for an ENUM column, you should use: SHOW COLUMNS FROM table_name LIKE enum_column_name and parse the ENUM definition in the second column.

[Ddr]

Thanks again for you research here, HalfaBee

I had read this already. I read it a few more times here, too. Perhaps enum is the wrong type of column for me.

Since the values I want to enter will only be a string of values that do not need to be cut, sliced, or interpolated.....all I really need is a column type that will take the checked values from the form, store them literally as a string, and spit them back out as a string when a queiry for them is entered. Could you recommend a column type for that?

Am I asking for too much of MySQL? To acheive this, do I have to have a separate table that assigns key values to all the different possible choices, then do as said in the ENUM instructions and store the form selections as a string of key numbers? That sounds like overkill for this application.

Again, any suggestions would be appreciated. Thanks so much for your patience with me here.

Dale

[HalfaBee]

I would use varchar(x) to store the strings
ENUM's are probably more useful when a script creates a 1-n value.

I think It also allows for some maths to be done of the key as well.

[Ddr]

thanks again for your help. I will give that a try

Dale