Working with a column of data type 'SET'

Databases
1

I have a table with one column of data type ‘SET’. It’s easy enough to extract the settings in this column from any row. But I want to be able to compare those settings to the full list contained in the table structure.

I need first to extract this full list of ‘SET’ options from the table structure.

I have tried ‘mysql_field_type’ but it doesn’t yield enough detail (I just get told it’s a binary string of a certain length).

I recognise that the ‘SET’ data type has many limitations, and perhaps this is one of them, but if anyone knows how to do this, I’d be grateful.

I could type the full list into my PHP code as an array, but then I’d have to edit it if I changed the DB table, so I’m trying to avoid that.

2

this is another great example of the shortcomings of the SET datatype

i realize that adding another table to your design will require more code, but that’s your best bet at this point

3

Thanks for your reply. Yes, I’m coming round to that view too !

4

Oooo, I did this. Gimme a mo.

5

Here, this thread may help.

6

Thank you, Anthony. I took a look, and I think it just confirmed Rudy’s view that I ought to be going for another table. But I’ll bear it in mind when the real work starts.

7

Ha, yeah, I’d be inclined to agree. :stuck_out_tongue:

8

In case anyone else stumbles on this thread, as the person who started it, I’d like to add a final comment.

The SET data type is convenient but (as has been said before) it does have limitations. My advice to anyone who has to attempt any sort of editing, changing, manipulating of data stored as a SET is: don’t try. Create a separate table.

That’s what I did, and all the limitations vanished. My thanks to those who pointed me in that direction.