Better way of sorting data

[whitt]

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 A
Shoe size: 7,8,9

Shoe color:  black , brown, grey

For example could have a shoe available in black , size 8 or 9 or 8 and 9.

[CroNiX]

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

[whitt]

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_ID
brand_id
size_id
color_id

 

brands

brand_id

brand 

 

sizing
size_id
size

 

 

colors

color_id

color

Example
shoe is in size 8 and 10 in black and brown available in brand A and B

shoe 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

[CroNiX]

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

[whitt]

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 .

[CroNiX]

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

[whitt]

If its ok i will look into this and get back to you

[whitt]

how about if say theres a shoe type say

types
type_ID
type 
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

[kicken]

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.