Re: Storing multiple items in one MySQL field?

On Jan 11, 2012, at 12:45 AM, Peter Lind wrote:

On Jan 11, 2012 7:13 AM, "Karl DeSaulniers" <karl@designdrumm.com> wrote:

On Jan 10, 2012, at 10:49 PM, Karl DeSaulniers wrote:

On Jan 10, 2012, at 9:30 AM, B. Aerts wrote:

On 08/01/12 23:35, Karl DeSaulniers wrote:

On Jan 8, 2012, at 10:36 AM, Bastien wrote:

On 2012-01-08, at 7:27 AM, Niel Archer <not@chance.now> wrote:

-- Niel Archer niel.archer (at) blueyonder.co.uk

Hello phpers and sqlheads, If you have a moment, I have a question. INTRO: I am trying to set up categories for a web site. Each item can belong to more than one category. IE: Mens, T-Shirts, Long Sleeve Shirts, etc.. etc.. (Sorry no fancy box drawing) QUESTION: My question is what would the best way be to store this in one MySQL field and how would I read and write with PHP to that field? I have thought of enum() but not on the forefront of what that actually does and what it is best used for. I just know its a type of field that can have multiple items in it. Not sure if its what I need. REASON: I just want to be able to query the database with multiple category ID's and it check this field and report back if that category is present or if there are multiple present. Maybe return as a list or an array? I would like to stay away from creating multiple fields in my table for this.

Have you considered separate tables? Store the categories in one

table

and use a third to store the item and category combination, one row

per

item,category combo. This is a common pattern to manage such

situations.

NOTE: The categories are retrieved as a number FYI. Any help/code would be greatly appreciated. But a link does just fine for me. Best Regards, Karl DeSaulniers Design Drumm http://designdrumm.com Hope your all enjoying your 2012!

-- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php

Neil's solution is the best. Storing a comma separated list will involve using a LIKE search to find your categories. This will result in a full table scan and will be slow when your tables get bigger. Storing them in a join table as Neil suggested removes the need for a like search an will be faster Bastien -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php

Thanks guys for the responses. So.. what your saying if I understand correctly. Have the categories in one table all in separate fields. Than have a the products table. Than have a third table that stores say a product id and all the individual categories for that product in that table as separate fields associated with that product id? Am I close? Sounds like a good situation, but I didn't want to really create a new table. One product will probably have no more than 3 combinations of categories. So not sure it this is necessary. EG: Tshirts = 1 Jackets = 2 etc.. Mens = 12 Womens = 13 So lets say I want to find all the Mens Tshirts.. I was wanting one field to hold the 1, 12 hope that clarifies Karl DeSaulniers Design Drumm http://designdrumm.com

Hi Karl, if you don't want to do with the third-table-solution, how about an

assembler-style bit-wise OR of all categories ?

constant TSHIRTS = 1 ; // 2 to the 0th power constant JACKETS = 2 ; // 2 to the 1st power

constant MENS    = 8 ;  // 2 to the 3rd power

constant WOMENS = 16 ; // 2 to the girl power :-) INSERT INTO TABLE t_myTable ( ID, categoryField) VALUES ( myNewId, TSHIRTS | MENS ) ; SELECT ID FROM t_myTable WHERE ( categoryField && ( TSHIRTS | MENS )) > 0 ; This assumes that your number of categories is not that big of course,

as you're limited to 64 bits/categories on a modern machine.

Bert -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php

Hi Bert, Thanks for the response. I did consider that, but there may be more than

64 categories.

So I am thinking that may not be best for my situation. I am actually at

the same point again, but

this time with the colors. I have multiple colors for each tshirt. I dont want to put all the separate colors as their own fields and there

is an image associated with those colors too.

I'd also like to not put those all in separate fields if I can. What's the best way to store multiple values that may change from time

to time?

What kind of field? IE: ('red.png', 'green.png', 'blue.png') SET() enum() blob() varchar() ??? TIA Karl DeSaulniers Design Drumm http://designdrumm.com -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php

I am thinking of limiting the colors to 10 for now (after all there are

only so many ways to die a shirt. =)

and using a comma delimited list of abbreviated color names as a varchar

string.

Then read out that string, explode on the commas and put in an array. pd_color varchar(39) CHARACTER SET utf8 NOT NULL DEFAULT

'Blk,Wht,Gry,Tan,Nvy,Red,Grn,Yel,Org,Trq'

With this I can dynamically call each different color file dependent on

what's in the $colors[] array.

... $colors['Blk,Wht,Gry'] foreach($colors as $color) { ... get color for this iteration $product = "Mens_Sample_".$color."_T.png"; //for eg. ... $image = "http://somewhere.com/images/products/".$product; and if I want to add colors, I can just change it to how ever many more

characters + commas.

ALTER TABLE... pd_color varchar(55) CHARACTER SET utf8 NOT NULL

DEFAULT 'Blk,Wht,Gry,Tan,Nvy,Red,Grn,Yel,Org,Trq,Brk,Ptr,Sil,Tie'

My thoughts are because it will be a string, it will be read by php out

of MySQL fairly quickly and then php does the rest.

Am I headed for a headache or is this unconventional but OK or is this

the way to go? Headache. Either use the MySQL set datatype or use a join table. Your way is a violation of the first normal form, which is generally a bad idea to do. Nit to mention slower.

Best, Karl DeSaulniers Design Drumm http://designdrumm.com -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php

Ah, thank you Peter! I had a feeling set() was better, but needed the verification. Will set() still be good if say, all of a sudden, I have 72 colors? Is there a max for set? Going to go do a little research. Thanks for the help. Best Regards, Karl DeSaulniers Design Drumm http://designdrumm.com