Skip to main content
R
Rick4209
Known Participant
January 9, 2010
Question

PHP - MySQL - SET column

  • January 9, 2010
  • 1 reply
  • 717 views

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?

This topic has been closed for replies.

1 reply

B
bregent
Participating Frequently
January 9, 2010

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
David_Powers
Inspiring
January 9, 2010

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)
B
bregent
Participating Frequently
January 9, 2010

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)
Terms & ConditionsAccessibility statement
Using the community Terms of use Privacy Cookie preferences Do not sell or share my personal information ad choicesAdChoices