On Jan 11, 2012 7:13 AM, "Karl DeSaulniers" <karl@xxxxxxxxxxxxxxx> wrote: > > > On Jan 10, 2012, at 10:49 PM, Karl DeSaulniers wrote: > >> >> On Jan 10, 2012, at 9:30 AM, B. Aerts wrote: >> >>> 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 <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 >>>>> >>>> >>>> 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 >>> >> >> Hi Bert, >> Thanks for the response. I did consider that, but there may be more than 64 categories. >> So I am thinking that may not be best for my situation. I am actually at the same point again, but >> this time with the colors. I have multiple colors for each tshirt. >> I dont want to put all the separate colors as their own fields and there is an image associated with those colors too. >> I'd also like to not put those all in separate fields if I can. >> >> What's the best way to store multiple values that may change from time to time? >> What kind of field? >> >> IE: >> ('red.png', 'green.png', 'blue.png') >> >> SET() >> enum() >> blob() >> varchar() >> >> ??? >> TIA >> >> Karl DeSaulniers >> Design Drumm >> http://designdrumm.com >> >> >> -- >> PHP Database Mailing List (http://www.php.net/) >> To unsubscribe, visit: http://www.php.net/unsub.php >> > > > I am thinking of limiting the colors to 10 for now (after all there are only so many ways to die a shirt. =) > and using a comma delimited list of abbreviated color names as a varchar string. > Then read out that string, explode on the commas and put in an array. > > `pd_color` varchar(39) CHARACTER SET utf8 NOT NULL DEFAULT 'Blk,Wht,Gry,Tan,Nvy,Red,Grn,Yel,Org,Trq' > > With this I can dynamically call each different color file dependent on what's in the $colors[] array. > ... > $colors['Blk,Wht,Gry'] > foreach($colors as $color) { > ... get color for this iteration > $product = "Mens_Sample_".$color."_T.png"; //for eg. > ... > $image = "http://somewhere.com/images/products/".$product; > > and if I want to add colors, I can just change it to how ever many more characters + commas. > > ALTER TABLE... `pd_color` varchar(55) CHARACTER SET utf8 NOT NULL DEFAULT 'Blk,Wht,Gry,Tan,Nvy,Red,Grn,Yel,Org,Trq,Brk,Ptr,Sil,Tie' > > My thoughts are because it will be a string, it will be read by php out of MySQL fairly quickly and then php does the rest. > Am I headed for a headache or is this unconventional but OK or is this the way to go? Headache. Either use the MySQL set datatype or use a join table. Your way is a violation of the first normal form, which is generally a bad idea to do. Nit to mention slower. > Best, > > > Karl DeSaulniers > Design Drumm > http://designdrumm.com > > > > -- > PHP Database Mailing List (http://www.php.net/) > To unsubscribe, visit: http://www.php.net/unsub.php >
