[SOLVED] How to match/compare comma-separated values in query?

[cparekh]

Hi,

 

I'm struggling with what must be a simple query but I can't crack it. Hopefully someone can point me in the right direction.

 

The scenario:

 

I have 2 tables, 'articles' and 'categories'.

 

Each article can have multiple categories so e.g. I'm writing to the category column for an article the string "5,11" where the article belongs to 2 categories, 5 and 11 (these being the item ids in the categories table).

 

The query I'm writing wants to take into account a user's category profile e.g. User1 can view articles in category 1 and 5 but not 11.

 

How would I structure the condition of the query when a user views the articles listing? I tried LIKE and as expected it returned the articles with category 11 as it successfully matched "1 LIKE 11".

 

What's a good condition to use or am I writing the category IDs the wrong way i.e. as a string like '5,11'?

 

Hope this makes sense.

 

Any help or direction is much appreciated.

 

Thanks,

C.

 

[trq]

You need to take a look into database normalization, your current design is (as youv'e discovered) hard to manage.

 

Theres a book in my signiture (Hudzilla) which has a chapter on the subject.

[cparekh]

Hi Thorpe,

 

thanks for the link - I checked out the normalization chapter and I could be wrong but isn't that how I've structured my database? I have 2 tables one for the Articles and another for the Categories and instead of storing the titles of each category in the Articles table I'm storing the item ID.

 

However, the issue is that I need to run a comparison, in the sql query, which doesn't trip up like 'LIKE' does as a string '1' matches a string '3,11' whereas I need to use an operator that matches exactly so that e.g. '1' would return true against '1,3,5' but false against '3,5,11'.

 

I'm not sure what MySQL comparison operator would do this?

 

Thanks again,

 

C.

[trq]

You need a third table so you can relate each article to a catigory.

 

Example.

 

CREATE TABLE articles (
  article_id INT,
  name
);

CREATE TABLE catigories (
  catigory_id INT,
  name
);

CREATE TABLE article_catigories (
  article_id INT,
  catigory_id
);

 

Now lets make some catigories.

INSERT INTO catigories (catigory_id, name) VALUES (1, 'programing');
INSERT INTO catigories (catigory_id, name) VALUES (2, 'databases');
INSERT INTO catigories (catigory_id, name) VALUES (3, 'cooking');

 

Now, we create an article.

INSERT INTO articles (article_id, name) VALUES (1, 'database normalization');

 

Obviously this belongs in the database catigory, but it probably also belongs in programing. Lets make it so....

INSERT INTO article_catigories (article_id, catigory_id) VALUES (1, 1);
INSERT INTO article_catigories (article_id, catigory_id) VALUES (1, 2);

 

See the relationship?

[cparekh]

Ahhh yes, ok I see what you mean.

 

So the query would use a table join and we're able to query against each article and category using the '=' operator for an exact match?

 

Right, thanks very much, that makes a lot more sense to me.

 

Thanks again,

C.

[trq]

Cool. Its a much easier to deal with design.