whitt Posted December 8, 2014 Share Posted December 8, 2014 I'm attempting to create a SQL command that can sort through a concatenated string in my database it is listed at Colors: blue,red,green,yellow is SELECT * FROM `shoes` WHERE CONCAT(',',color,',') LIKE '%,blue,red%' How do I filter the data where it can come up with shoes that are available in red or blue or red and blue and so on? I could use where find_in_set('blue', colors) > 0 and find_in_set('red', colors) > 0 Is there a better way of doing this? Shoe name: shoe AShoe size: 7,8,9 Shoe color: black , brown, greyFor example could have a shoe available in black , size 8 or 9 or 8 and 9. Link to comment https://forums.phpfreaks.com/topic/292976-better-way-of-sorting-data/ Share on other sites More sharing options...
CroNiX Posted December 8, 2014 Share Posted December 8, 2014 This is a problem with databases that aren't normalized. There shouldn't be any comma separated values in a single column really. Ideally you'd have a table that stored the colors with a reference ID back to whatever is using it. Link to comment https://forums.phpfreaks.com/topic/292976-better-way-of-sorting-data/#findComment-1499050 Share on other sites More sharing options...
whitt Posted December 8, 2014 Author Share Posted December 8, 2014 I thought of something along that line , but how would i deal with shoes that are multiple colors? I want to use input radio buttons to filter through my inventory. Shoes shoe_IDbrand_idsize_idcolor_id brands brand_id brand sizingsize_idsize colors color_id colorExampleshoe is in size 8 and 10 in black and brown available in brand A and Bshoe id 1 brand_id shoe A,b size id 8,9 color_id 1,2 sizing size id 8 size 8 size id size 9 colors color_id 1 color brown color_Id 2 color black Link to comment https://forums.phpfreaks.com/topic/292976-better-way-of-sorting-data/#findComment-1499051 Share on other sites More sharing options...
CroNiX Posted December 8, 2014 Share Posted December 8, 2014 A short example: Shoes: -ID -Name Colors: -ID -Name Shoe_colors -ID -Shoe_ID -Color_id so let's say you had: Shoes: ID = 1, Name = 'Nike Free' Colors ID = 1, Name = 'Grey' ID = 2, Name = 'Yellow' Shoe_colors: ID = 1, Shoe_id = 1, Color_id = 1 ID = 2, Shoe_id = 1, Color_id = 2 So you have Nike Free available in Grey and Yellow You'd use a JOIN to get all data in a single query Link to comment https://forums.phpfreaks.com/topic/292976-better-way-of-sorting-data/#findComment-1499052 Share on other sites More sharing options...
whitt Posted December 8, 2014 Author Share Posted December 8, 2014 I would assume i wouldd have to use AJAX or php to dynamically add the numbers to the table because a person is not always going to choose shoe size before color or visa vie . Link to comment https://forums.phpfreaks.com/topic/292976-better-way-of-sorting-data/#findComment-1499053 Share on other sites More sharing options...
CroNiX Posted December 8, 2014 Share Posted December 8, 2014 That would really depend on how your shopping cart/app is layed out. I only gave you an example for multiple colors to keep it short as it would apply to most of the other shoe metadata you described Link to comment https://forums.phpfreaks.com/topic/292976-better-way-of-sorting-data/#findComment-1499054 Share on other sites More sharing options...
whitt Posted December 8, 2014 Author Share Posted December 8, 2014 If its ok i will look into this and get back to you Link to comment https://forums.phpfreaks.com/topic/292976-better-way-of-sorting-data/#findComment-1499055 Share on other sites More sharing options...
whitt Posted December 8, 2014 Author Share Posted December 8, 2014 how about if say theres a shoe type saytypestype_IDtype but i wanted a shoe to be for basket ball and running Shoes: ID = 1, Name = 'Nike Free' Colors ID = 1, Name = 'Grey' ID = 2, Name = 'Yellow' Type type_id 1 type running type_id 2 type basketball Shoe_colors: ID = 1, Shoe_id = 1, Color_id = 1 type = 1 and 2 ?? ID = 2, Shoe_id = 1, Color_id = 2 Link to comment https://forums.phpfreaks.com/topic/292976-better-way-of-sorting-data/#findComment-1499056 Share on other sites More sharing options...
kicken Posted December 9, 2014 Share Posted December 9, 2014 You'd have a shoe_types table just like you have a shoe_colors table. If not every color is available in every type, then you'd want to combine the shoe_colors table and shoe_types table into something like a shoe_style table which would look like: - ShoeID - ColorID - TypeID If you come up with any other options you'd add them to that table as well. Link to comment https://forums.phpfreaks.com/topic/292976-better-way-of-sorting-data/#findComment-1499080 Share on other sites More sharing options...
Recommended Posts
Archived
This topic is now archived and is closed to further replies.