Re: Storing multiple items in one MySQL field?

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


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