Re: Storing multiple items in one MySQL field?

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?

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