return all results from a mysql row seperated by commas matching a value

I have a mysql table containing开发者_JS百科 news data and categories and i'm trying to slect all data within a certain category. I didn't think far enough ahead so i'm currently not using a normalised table at some point i may go back and re-write that portion to use a normalised table.

Here is my table structure

+---------+------------+------------+--------+----------+   
| news_id | news_title | news       | cat_id | date     |      
+---------+------------+------------+--------+----------+    
| 1       | title1     | blahblah   |1,2,4,6 |2009-11-24|                    
+---------+------------+------------+--------+----------+
| 2       | new title  | text 123   | 2      |2009-01-24|                    
+---------+------------+------------+--------+----------+ 
| 3       | new title3 | text 3     | 2,19,6 |2009-02-24|                    
+---------+------------+------------+--------+----------+   

Let's say I want to return all results for category 2 I am trying to use this statement.

$query= mysql_query("SELECT id FROM `news_data` WHERE FIND_IN_SET('" . str_replace(',',"',cat_id) OR FIND_IN_SET('",$cat_id)."',cat_id)") or die(mysql_error());

This returns the results for news id's 2 and 3 because they both start with "2". The first news id is not selected because "2" is the second value. I know it's something simple wrong with the mysql statement and I'm hoping someone will be able to help me out.

Thanks, Brooke

Just remove the quotes from the number inside find_in_set. Worked for me.

" WHERE FIND_IN_SET(" . str_replace(',',",cat_id) OR FIND_IN_SET(",$cat_id).",cat_id)")

another cool way:

select id from news_data where cat_id regexp '(^|[^0-9])2([^0-9]|$)';

...works with any delimiter / spacing.

in addition to the other excellent answers

This is a bad design. It breaks one of the normalization rules for schema design (first? second? I can't recall).

It's a many-to-many relationship, because it looks like a news item can have many categories, and a category can belong to many news items. Model it that way and you'll have a better design.

Use:

 WHERE INSTR(CONCAT(t.cat_id, ','), '2,') > 0

Reference:

• INSTR
• CONCAT