0

I have a string array var and a DB table column containing comma-separated values (varchar) and I am trying to set up a query that would return all rows containing matching elements. See below:

// an array of random strings
$ItemsOfInterest=["item1", "item99", "item5"];
$imploded_items = implode("','", $ItemsOfInterest);

$query = "SELECT * FROM table_name WHERE `items` IN ('$imploded_items');

Let's say in the items column of my table I have these five rows of sample data (VARCHAR):

(row1) item43,item60,item1
(row2) item1,item78
(row3) item24,item83,item5
(row4) item5
(row5) item71,item5,item93

The query above will only return a single row (the one containing only item5).

What I need is a query that will return all rows containing at least one of the comma separated values, which in the above example would be all five rows. Thanks.

tellurian
  • 1
  • 3
    *"a DB table column containing comma-separated values"* No. This is design smell that goes against the purpose of using a RDBMS. Fix your design and use [database normalization](https://en.wikipedia.org/wiki/Database_normalization) – Cid Jul 21 '21 at 17:21
  • Thank you for your response. Those items are being added as updates to the column during a period of time in the form of various user preferences. Not sure what format would be best for those to be stored in. – tellurian Jul 21 '21 at 18:11
  • Since a single element can have many items and a single item can be shared between many elements, this is a **many-to-many** relation. That's usually done with 3 tables, the main table `table_name` (the one in your example), the table that contains the items and a 3rd table that holds the relation between both tables `(main_table_id, item_id)` – Cid Jul 22 '21 at 06:37
  • [How to implement one-to-one, one-to-many and many-to-many relationships while designing tables?](https://stackoverflow.com/questions/7296846/how-to-implement-one-to-one-one-to-many-and-many-to-many-relationships-while-de) – Cid Jul 22 '21 at 06:40

0 Answers0