CODEWITHSUNDEEP
phpmysqlsearch
SGuerreiro
SGuerreiro

Reputation: 57

Mysql query where column has several values

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.

Upvotes: 0

Views: 100

Answers (2)

faerin
faerin

Reputation: 1925

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.

Upvotes: 1

chiptuned
chiptuned

Reputation: 96

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

Upvotes: 1

Related Questions