Re: Storing multiple items in one MySQL field?

On Jan 11, 2012, at 3:29 AM, Karl DeSaulniers wrote:

>
> 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
>


How does set() know the difference between say
the first row (1) + the fifth row (5) and the second row (2) + fouth  
row (4)
in the bit set? The sum of both are the same.
I am sure I am congfusing something.


Karl DeSaulniers
Design Drumm
http://designdrumm.com


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