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. Quote 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 (edited) 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. Edited December 8, 2014 by CroNiX Quote 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 (edited) 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 Edited December 8, 2014 by whitt Quote 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 Quote 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 . Quote 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 Quote 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 Quote 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 Quote 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. Quote 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
Join the conversation
You can post now and register later. If you have an account, sign in now to post with your account.