0

I am making a news website with Codeigniter, and I have an Articles MySQL table like ID,Title,Body,Categories,Created etc...

In Categories field I have category separated with comma(,) like...

  • Article 1 Categories : National,Crime,Cinema
  • Article 2 Categories : National,City,Drama
  • Article 3 Categories : Funny,International,Cinema
  • Article 4 Categories : National,Crime,Cinema

I want to fetch article with Specific Category.. like National (1,2,4). I tried many methods but nothing seems to work.

Please Help Thanks.

Amit Kumar Khare
  • 565
  • 6
  • 17
  • 2
    Since it is not finished, i would really consider changing of db structure... you should have separate category table, and one additional table for article-category connection (id,cat_id, article_id). Then you can get what you want relatively easy. If you want to keep this scheme: http://stackoverflow.com/questions/9302967/running-a-select-on-a-list-of-comma-separated-values-in-php maybe this could help. – sinisake Jul 08 '13 at 18:50

1 Answers1

2

you can use FIND_IN_SET method to query your Categories field

FIND_IN_SET('Crime', your_table.Categories)

Your approach has a number of shortcomings, It would def be more scalable in the long run to change your tables relationship to Categories. You can use a manytomany relationship and a join table to more easily query your categories.

FIND_IN_SET will do a full table scan, and using this comma seperated way will be very difficult to aggregate, and get article/category counts.

Is storing a delimited list in a database column really that bad?

Bill Karwin has included this anti pattern as the first chapter in his excellent book.

Community
  • 1
  • 1
dm03514
  • 54,664
  • 18
  • 108
  • 145