Re: [PHP-DB] Storing multiple items in one MySQL field?
On Jan 11, 2012, at 12:45 AM, Peter Lind wrote: On Jan 11, 2012 7:13 AM, Karl DeSaulniers k...@designdrumm.com 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 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. 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
Re: [PHP-DB] Storing multiple items in one MySQL field?
On Jan 11, 2012, at 3:29 AM, Karl DeSaulniers wrote: On Jan 11, 2012, at 12:45 AM, Peter Lind wrote: On Jan 11, 2012 7:13 AM, Karl DeSaulniers k...@designdrumm.com 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 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. 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.
Re: [PHP-DB] Storing multiple items in one MySQL field?
*snip* How does set() know the difference between say the first row (1) + the fifth row (5) and the second row (2) + fouth row (4) in the bit set? The sum of both are the same. I am sure I am congfusing something. Ummm ... if you're asking how set figures out how bitmasks differ, then yes, you're very confused. It's a bitmask, summing plays no role. Rest assured that set will know the difference between different kinds of content in the column. Regards Peter -- hype WWW: plphp.dk / plind.dk LinkedIn: plind BeWelcome/Couchsurfing: Fake51 Twitter: kafe15 /hype -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] Storing multiple items in one MySQL field?
Ok, thanks. On Jan 11, 2012, at 11:53 AM, Peter Lind wrote: *snip* How does set() know the difference between say the first row (1) + the fifth row (5) and the second row (2) + fouth row (4) in the bit set? The sum of both are the same. I am sure I am congfusing something. Ummm ... if you're asking how set figures out how bitmasks differ, then yes, you're very confused. It's a bitmask, summing plays no role. Rest assured that set will know the difference between different kinds of content in the column. Regards Peter -- hype WWW: plphp.dk / plind.dk LinkedIn: plind BeWelcome/Couchsurfing: Fake51 Twitter: kafe15 /hype -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php Karl DeSaulniers Design Drumm http://designdrumm.com -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] Storing multiple items in one MySQL field?
Yeah, I was being somewhat facetious about the colors of a shirt. :) I agree on the items and attributes drill-down before implementation though. There will be more than T-Shirts. Watches, book-covers, etc, etc. So I need to find a general logic to cover the items and attributes of each item? And this will make it scalable? Best, On Jan 11, 2012, at 4:39 PM, tamouse mailing lists wrote: I am thinking of limiting the colors to 10 for now (after all there are only so many ways to die a shirt. =) Oh, please. There are lots more than 10 dyes in the world. Take a look at a women's clothing catalog sometime or other... Just look at this one t-shirt item alone: http://store.americanapparel.net/2102.html Individual item characteristics are going to be a lot different than categories. You'll need expandable attributes for all kinds of things. Colour is the obvious one here. Also: Size: not everything comes in S, M, L, or is measured in that way. If this is for an apparel store that sells a variety of different items, you'll need to solve this generally across a whole lot of different types of clothing. I'd really suggest you do a deep analysis of the different types of items that are going to be sold, the attributes of each one, and figure out how to best represent that breadth and depth. Karl DeSaulniers Design Drumm http://designdrumm.com -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] Storing multiple items in one MySQL field?
Karl, I'm somewhat limited to reading posts, so I might have missed something, but can you explain why you wanted to avoid the 3rd table solution ? Because depending on that question, I can offer two other solutions, but they have their own limitations. Solution 1: the 2,5nd table. you create an extra table and write a procedure that makes a unique ID for every combination of attributes (and this procedure needs to be run every time you update/add attributes). when you want to query items for a combination of attributes, you first select the attribute combination ID, then query your item list for all items with this specific ID. It isn't exactly a 3rd table solution as it doesn't involve individual cross-references between items and a set of attributes, and thus saves you an INNER JOIN. Solution 2: an alphanumeric bit-wise selection. Instead of saving your combinations as binary number, you could extend it to, for example, a string of 4 characters per attribute: 4 characters times 26 characters makes 456976 possible combinations per attribute : define MENS = ; define WOMENS = AAAB ; define GREEN = ; define RED= AAAB ; you write one PHP function that converts an array of combinations into an ISO wildcard filter (or does the MySQL syntax provide regular expressions ?), and apply this in a SELECT FROM ... WHERE ... LIKE statement. $findTheseAttribs = new array ( MENS, GREEN ); $SQLlike=createSQLwildcard($findTheseAttribs); // returns $mySql = SELECT * FROM t_myTable WHERE itemAttribute LIKE . $SQLlike; The advantage is that you can assign different filter widths per attribute: MENS/WOMENS only need 1 character, size only needs 2 ( XXXS, XXS, XS, S, ... pretty limited), while colour can have op to 10 characters to encode. This leaves reasonable room to expand/scale. The drawback is that, when you add new attributes, your filter string expands, and the LIKE-statement might fail on the difference in string lengths. Bert On 12/01/12 02:18, Karl DeSaulniers wrote: Yeah, I was being somewhat facetious about the colors of a shirt. :) I agree on the items and attributes drill-down before implementation though. There will be more than T-Shirts. Watches, book-covers, etc, etc. So I need to find a general logic to cover the items and attributes of each item? And this will make it scalable? Best, On Jan 11, 2012, at 4:39 PM, tamouse mailing lists wrote: I am thinking of limiting the colors to 10 for now (after all there are only so many ways to die a shirt. =) Oh, please. There are lots more than 10 dyes in the world. Take a look at a women's clothing catalog sometime or other... Just look at this one t-shirt item alone: http://store.americanapparel.net/2102.html Individual item characteristics are going to be a lot different than categories. You'll need expandable attributes for all kinds of things. Colour is the obvious one here. Also: Size: not everything comes in S, M, L, or is measured in that way. If this is for an apparel store that sells a variety of different items, you'll need to solve this generally across a whole lot of different types of clothing. I'd really suggest you do a deep analysis of the different types of items that are going to be sold, the attributes of each one, and figure out how to best represent that breadth and depth. Karl DeSaulniers Design Drumm http://designdrumm.com -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php