Mysql query where column has several values

Question

I've been trying to find a way to get a value from a column using mysql query.

The column (lets talk about colors) has several foreign keys (with no foreign constraint). So imagine a shirt can have blue and black, the column "color" will have "1,10" being 1-Blue and 10-Black, both foreign keys.

I want to create query to search if the color blue is being used so I can prevent deleting that color from his original table. Although it sounds easy, I can't use:

SELECT * FROM shirt WHERE color LIKE '%$id%'

The problem if it there's no blue color being used it will still find the black (10) and give a true value to the query.

chiptuned · Accepted Answer · 2015-01-19 16:32:08Z

1

SELECT * FROM shirt WHERE FIND_IN_SET($id, color) > 0

answered Jan 19, 2015 at 16:32

chiptuned

961 silver badge3 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

SGuerreiro Over a year ago

Thank you very much, it did work. I was unware of that precious function

faerin · Accepted Answer · 2015-01-19 16:42:57Z

1

Well.. if I understand your question right. You are storing the values as a comma seperated string. You want to search whether any t-shirt holds that color.

$colorToSearchFor = '1'; //blue.. sepearte it by comma to check for several colors at the same time e.g '1,10'
$sql = "SELECT * FROM shirt WHERE color IN(colorToSearchFor)";

//count the results, if it's more than one then a t-shirt has the specifik color assigned.

It would be recommendable to have two different tables. One for the t-shirts and one for the colors.

answered Jan 19, 2015 at 16:42

faerin

1,92517 silver badges32 bronze badges

2 Comments

SGuerreiro Over a year ago

They are in different tables and i wan't to avoid deleting colors being used. This works perfectly, thank you

faerin Over a year ago

Oh, and one table for what colors each t-shirt holds :) that would be a better structure.

Collectives™ on Stack Overflow

Mysql query where column has several values

2 Answers 2

1 Comment

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related