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

Question

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:

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!

rr- · Accepted Answer · 2013-07-05 05:57:59Z

1

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.

answered Jul 5, 2013 at 5:57

rr-

14.9k6 gold badges49 silver badges68 bronze badges

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

1 Comment

Benjamin Oman Over a year ago

Just to help a little more, if you implement the many to many data model your query would probably look something like this:

SELECT * FROM table_a LEFT JOIN junction_table ON table_a.cID = junction_table.cID WHERE junction_table.subid IN ( $subids )

You may want to also add GROUP BY table_a.primary_key so you get a distinct record from table_a even if there are multiple matched subids.

Tech Consultant · Accepted Answer · 2013-07-05 06:15:53Z

0

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

answered Jul 5, 2013 at 6:15

Tech Consultant

3701 silver badge7 bronze badges

Comments

Arvindkumar · Accepted Answer · 2013-07-05 06:16:25Z

0

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

answered Jul 5, 2013 at 6:16

Arvindkumar

1843 silver badges16 bronze badges

Comments

DayTimeCoder · Accepted Answer · 2013-07-05 06:17:50Z

0

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,

answered Jul 5, 2013 at 6:17

DayTimeCoder

4,3325 gold badges41 silver badges64 bronze badges

Collectives™ on Stack Overflow

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

4 Answers 4

1 Comment

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

1 Comment

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related