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 ?

@Michael: Don't use values from $_REQUEST directly in your SQL query. You should escape them or use PDO. — gen_Eric
– gen_Eric, Commented Sep 16, 2011 at 17:37
@Einacio I haven't had any good responses. I might be asking my questions wrong — MHowey
– MHowey, Commented Sep 16, 2011 at 17:46
@Rocket This is how I request, but I didnt think I needed to show the full request. $new_keyword = htmlspecialchars($_REQUEST["new_keyword"], ENT_QUOTES); $new_keyword = preg_replace("/[^0-9]/","", $new_keyword); — MHowey
– MHowey, Commented Sep 16, 2011 at 17:47
then rework your questions so that the community can help you better, as it is recommended — Einacio
– Einacio, Commented Sep 16, 2011 at 17:50
You can also select the answer that helped you most, one must not 100% answer your question. — hakre
– hakre, Commented Sep 16, 2011 at 17:55

gen_Eric · Accepted Answer · 2011-09-16 17:57:18Z

1

= (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

edited Sep 16, 2011 at 17:57

gen_Eric

228k42 gold badges304 silver badges343 bronze badges

answered Sep 16, 2011 at 17:23

Einacio

3,5422 gold badges19 silver badges21 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

MHowey Over a year ago

Question about like. if I have this '1','3','8','33 would like pick up the 3 and the 33?

jdias · Accepted Answer · 2011-09-16 17:38:15Z

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!

gen_Eric · Accepted Answer · 2011-09-16 18:15:06Z

0

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

edited Sep 16, 2011 at 18:15

answered Sep 16, 2011 at 18:03

gen_Eric

228k42 gold badges304 silver badges343 bronze badges

Collectives™ on Stack Overflow

MySQL Select list In Array

3 Answers 3

1 Comment

Comments

Comments

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

1 Comment

Comments

Comments

Linked

Related