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

Question

Hi, I have a mysql table containing 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

Answer 1

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)")

Answer 2

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.

Answer 3

Use:

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

Reference:

• INSTR
• CONCAT

Answer 4

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