MySQL Select list In Array

Question

Each Article can have unlimited categories.

Categories are saved in the database like this

example article_category: '1','3','8'

OR

example article_category: '2','3'

So when I search for a listing under, lets say category 3

$category_id = $_REQUEST['category_id'];  //3
$SQL = "SELECT * FROM ARTICLES WHERE article_category = '$category_id'";

If it were just one the above would work fine.

I hate to say it but I'm completely lost.. Would I use IN ?

Answer 1

You can use FIND_IN_SET here, but it may not be very efficient. I suggest that you change your table structure and follow @jdias' answer.

If your article_category is saved as 1,2,3:

$SQL = "SELECT * FROM ARTICLES WHERE FIND_IN_SET('$category_id', article_category)";

Or if it's saved as '1','2','3':

$SQL = "SELECT * FROM ARTICLES WHERE FIND_IN_SET('\'$category_id\'', article_category)";

Answer 2

The way I would implement this is by creating a table for categories, which you probably already have. Something like:

cat_id    cat_name
1         animals
2         sports
3         locations

Then create a table to link articles to categories. Something like:

table artcat
article_id    cat_id
1             1
1             3
2             1
2             2
2             3

The above table basically replaces the field article_category you have currently. In the above example

article 1 is linked to categories 1 and 3
article 2 is linked to categories 1, 2 and 3

And when you need to get all the articles for a given category, all you would run a query like:

SELECT article_id FROM artcat WHERE cat_id=3

You could even do a join with articles database to output the article titles if you wish.

I hope this helps. Good luck!

Answer 3

= (equals) check against the complete string. what you want to do could be done using LIKE and the % comodin (it's like * for files in dir or the like)

$SQL = "SELECT * FROM ARTICLES WHERE article_category LIKE \"%'$category_id'%\"";

however, i reccommend that you normalize the database and have categories as a subtable