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
