PHP - MySQL - SET column

[Rick4209]

In my sql database I have a set column that works fine.  The user can select multiple options.  However, I am having trouble displaying those options using the record set in dreamweaver.  I want the recordset to find any one of the possible options and display the record, but it's not displaying anything.  Am I missing something here?

[bregent]

I'll just put in my $0.02 that +99% of the time using a SET to store more than one piece of data in a database field is a bad idea. It violates rules of normalization and makes data analysis very difficult. There are some exceptions and without knowing more details I can't say if it is appropriate in this case.

I'll let David answer the php aspect of your question

[David_Powers]

bregent wrote:

I'll just put in my $0.02 that +99% of the time using a SET to store more than one piece of data in a database field is a bad idea. It violates rules of normalization and makes data analysis very difficult.

Not really. A SET column in MySQL doesn't violate the rules of normalization. It's actually a shorthand way of creating a lookup table - or a linking table, since it creates a many-to-many relationship. Internally, MySQL simply stores a numerical reference to each value. In other words, behind the scenes it's storing a bunch of foreign keys.

The way you search for a particular option stored in a SET column is to use the MySQL function, FIND_IN_SET().

SELECT * FROM mytable
WHERE FIND_IN_SET('whatever', column_name)

[bregent]

OK, my bad. So a SET is sort of a user defined datatype, where the developer sets the allowable values of the column?  The thing that troubled me is that the example the reference guide gives shows it being used to store multiple values as in the last line here:

-----------------------------------

For example, a column specified as SET('one', 'two')         NOT NULL can have any of these values:

''
'one'
'two'
'one,two'

------------------------------------

So that's not really a problem inherent in a SET column, just a way that it can be misused like any other string column.

David_Powers wrote:

bregent wrote:

I'll just put in my $0.02 that +99% of the time using a SET to store more than one piece of data in a database field is a bad idea. It violates rules of normalization and makes data analysis very difficult.

Not really. A SET column in MySQL doesn't violate the rules of normalization. It's actually a shorthand way of creating a lookup table - or a linking table, since it creates a many-to-many relationship. Internally, MySQL simply stores a numerical reference to each value. In other words, behind the scenes it's storing a bunch of foreign keys.

The way you search for a particular option stored in a SET column is to use the MySQL function, FIND_IN_SET().

SELECT * FROM mytable
WHERE FIND_IN_SET('whatever', column_name)

[David_Powers]

bregent wrote:

OK, my bad. So a SET is sort of a user defined datatype, where the developer sets the allowable values of the column?  The thing that troubled me is that the example the reference guide gives shows it being used to store multiple values

I suppose it depends on your interpretation, but a SET data type allows you to define up to 64 values that can be stored. The idea is to enable you to store items drawn from a predefined list that are associated with a particular record. It's useful for things like optional extras, multiple choices in a poll, and so on.

So, let's say I define my SET column as accepting the following values:

air conditioning

sun roof

alloy wheels

satnav

If you order a car with air conditioning and a satnav, you insert the values as a comma-delimited string ("air conditioning,satnav"), but MySQL stores it internally as 1,4. When it returns the result, it displays "air conditioning,satnav". As mentioned before, you can use FIND_IN_SET() to search for particular values.

It probably goes against a strict interpretation of normalization, but it has the same effect as a linking table. It's just a heckuva lot easier to do than to create different tables and maintain a many to many relationship.

It works only when you have a predefined list. It won't work if you want to add arbitrary values into the field.

[bregent]

David, thanks for the explanation.

>It probably goes against a strict interpretation of normalization,

>but it has the same effect as a linking table. It's just a heckuva

>lot easier to do than to create different tables and maintain a

>many to many relationship.

The only problem I see again comes with storing more than one piece of information in a single column. Typically, this makes it difficult to extract data, particularly aggregate data.

In your example

>If you order a car with air conditioning

>and a satnav, you insert the values as a

>comma-delimited string ("air conditioning,satnav"

I assume there is an easy way to list all of the options a person or a group of people have ordered.  Is it easy to count the number of options ordered?

[David_Powers]

bregent wrote:

The only problem I see again comes with storing more than one piece of information in a single column. Typically, this makes it difficult to extract data, particularly aggregate data.

Getting aggregate data is probably difficult. Like everything in database design, you need to know the capabilities and drawbacks of the various data types.

I assume there is an easy way to list all of the options a person or a group of people have ordered.  Is it easy to count the number of options ordered?

Although the values are stored internally as numbers, the result returned by the database converts the input back to a comma-delimited string. However, it's important to note that the value returned is in the same order as the internal storage. Say, for example, I store "satnav,air conditioning", it will be returned as "air conditioning,satnav" because that's the order specified in the column definition. Things like counting need to be done in the application logic, and not in MySQL.

[bregent]

Thanks David.