Re: Storing multiple items in one MySQL field?

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