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" <[email protected]>  
> 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 <[email protected]> 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


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