MySQL SELECT Little issue

[cocoscrappy]

Hello!

 

I have a little problem when trying to select some products from a table.

This is how the table looks like:

 

The query I want to run is SELECT * FROM myTable WHERE category "contains 2 or is 2"

 

I tried LIKE condition (.. WHERE category LIKE '%2%') and it returns only the products whitch has CATEGORY = 2

 

Help please!

[kickstart]

Hi

 

LIKE should work for that (although %2% would also match for 20, 102, etc), but have categories concatenated into a single field like that is a really bad idea.

 

Better to use a second table with multiple rows per product, with one row per product and category.

 

All the best

 

Keith

[cocoscrappy]

Thanks kickstart!

I'll find an alternative.

[fenway]

If they're really just integers separated by commas, you can "cheat" for now with FIND_IN_SET().

[cocoscrappy]

If they're really just integers separated by commas, you can "cheat" for now with FIND_IN_SET().

 

I can use find_in_set, but when i search for '2', it will find me all the numbers containing 2 (210, 12..)

It is similar to LIKE, i think 

[cocoscrappy]

Intead, i use a little php code for this:

$cat_wanted = 2; // $_POST[];
$res = mysql_query("SELECT product, category FROM myTable");
while($arr = mysql_fetch_assoc($res)) {

	$arrs = explode(",", $arr['category']);

	if(in_array($cat_wanted, $arrs))
		echo "Product: ".$arr['product']." is in categories: ".$arr['category']."<br />";
}

 

Sorry for double post! 

 

Please post if you find better solutions.

[fenway]

If they're really just integers separated by commas, you can "cheat" for now with FIND_IN_SET().

 

I can use find_in_set, but when i search for '2', it will find me all the numbers containing 2 (210, 12..)

It is similar to LIKE, i think 

No, it won't.

[cocoscrappy]

If they're really just integers separated by commas, you can "cheat" for now with FIND_IN_SET().

 

I can use find_in_set, but when i search for '2', it will find me all the numbers containing 2 (210, 12..)

It is similar to LIKE, i think 

No, it won't.

 

Ooops!  You know you're right

My bad, i tried again and it works both ways!

Thank you for your support !