Re: Storing multiple items in one MySQL field?

[Bastien <phpster@xxxxxxxxx>]

On 2012-01-08, at 7:27 AM, Niel Archer <not@xxxxxxxxxx> 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