PHP MYSQL - Search comma separated list with a comma separated list

0
votes

Dear Stackoverflowers,

I have a mysql query which checks if some sub categories are part of a categoryID to show all products from subcategories in the main category.

For example:

  1. Category
    • Subcategory 1
    • Subcategory 2
    • Subcategory 3

For example products are added to subcats but are ALL beeing shown in the head category. This works fine and i do this with WHERE cID in (' . $subids . ')

But now comes it, the cID used to hold just one value but since it has to be possible to add products to multiple categories i now save the multiple selected ids as comma separated in the cID field.

So im searching for a way to basicly find a match from a comma seperated list within a comma separated list because cID has now become comma separated ID's and FIND_IN_SET does not work with this.

I hope someone can shine some light, thank you all!

4
phpmysql

4 Answers

1
votes

Have a read of these:

Implementing this, as opposed to comma-separated values, should probably save you and your coworkers a lot of headache and simplify your queries, effectively eliminating the need of complex queries. It should be faster, too.

0
votes

Add full-text search index on this column in the database and then use following sql query Ref - http://dev.mysql.com/doc/refman/5.0/en/fulltext-search.html

0
votes

Storing a CSV in a single field is not a good idea. Searching on that requires use of LIKE clause.

where CID LIKE '$cid1,' or CID LIKE ',$cid1,' or CID LIKE ',$cid1' or 
      CID LIKE '$cid2,' or CID LIKE ',$cid2,' or CID LIKE ',$cid2' or
      .... so on

Also FIND_IN_SET would work too.

where FIND_IN_SET( $cid1, CID ) OR FIND_IN_SET($cid2, CID) .. so on
0
votes

i now save the multiple selected ids as comma separated in the cID field. That's a very bad approach ,why would you store a CSV in a single field ,you shud change your design ,make a seperate table where each value will have a new row and will be mapped to a FK.

What you really need to do,is work on your DB design,read on Normalization and other related topics,I have so added some links,

  1. MSDN Link on Many to Many
  2. Databae Normalization