MySQL Query and Storage Help

[madk]

Hello,

I am problem that I need to solve. I am working on a php/mysql site that stores all article information with the database. The table has the following rows:

article_id
author_id
cat_ids
subject
body
datetime

My problem is the storage and retrieval of the cat_ids. When an article is posted it can be posted under many different categories. For example:

1 - Games
2 - Sports
3 - Food
etc..

If you choose multiple categories is stores the ids separated by a comma. For example:

cat_id = 1,4,12

During output the ids are split apart and each category name is listed.

The problem is that now I need to write a page that lists all articles under a certain category. I had been using a query like:

$cat_id = 1;
$query = "SELECT * FROM articles WHERE cat_id LIKE '%" . $cat_id . "%' ORDER BY datetime DESC";

The problem with this is that it will return all the articles with the cat_id of 1 and also all the artcles with cat ID id 10,11,12,13 and so on.

Is there any way I can be more specific and actually search or would it just be easier for me to use this query then write a seperate if statement to verify the ID before I output it?

Any suggestions would be greatly appreciated.

[justageek]

You really should build a lookup table to hold the cat_ids but if you want to use the same as you have now then the easiest solution would be to put a delimiter inbetween your ids. So 10 is really ¦10¦ and 11 is really ¦11¦ and so on. Then do a like '%¦$cat_id¦%' in your query.

Wait...how are you finding the numbers 10,11,12,13 and so on now? You have them delimited with a comma?

JAG