On 2012-01-08, at 7:27 AM, Niel Archer <n...@chance.now> wrote:

> --
> Niel Archer
> niel.archer (at) blueyonder.co.uk
>> Hello phpers and sqlheads,
>> If you have a moment, I have a question.
>> 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)
>> 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.
>> 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

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

Reply via email to