select all rows where column contains value from comma separated values

Question

I have mysql table (say mytable1) where values are stored as comma separated values in a column.

Now I want to fetch all rows in another table (say mytable2) where column unique_id contains value from above comma separated values.

e.g. mytable1 contains multiple rows with different unique_ids including following two given in $string.

I want to fetch only those two rows where row one unique_id is ABCD1 And row two where unique_id is BCDE2. these two unique_ids are not fixed. they may be different from different rows in mytable1

e.g.

$string = "ABCD1,BCDE2"; (retrieved from mytable1)

$query = "select * from mytable2 where unique_id = "values from $string ????";

What right $query should be ?

I couldn't figure it out with IN or FIND_IN_SET ...

your help is appreciated....

WHERE unique_id IN ("ABCD1,BCDE2") – Ôrel May 03 '20 at 20:48 — Ôrel, May 03 '20 at 20:48

score 1 · Answer 1 · answered May 03 '20 at 20:48

1

Here:

$query = "select * from mytable2 where unique_id IN ('ABCD1','BCDE2');

or in one line:

$query = "select * from mytable2 where unique_id IN (select unique_id from mytable1 where variable='somecondition');

this nested query for mytable1 should return only 1 column

answered May 03 '20 at 20:48

Ron

5,900
2
20
30

thnx.. I will try with this solution and will let you know... – darshan May 03 '20 at 20:55

score 1 · Answer 2 · answered May 03 '20 at 20:52

1

$idsArray = explode(",", $string);       // this will contain ["ABCD1", "BCDE2", ...]
$idsQuoted = array_map(function ($id) {  // each id will be surrounded by single-quotes '
    return "'{$id}'";
}, $idsArray);
$idsString = join(", ", $idsQuoted);     // join all ids by a comma
$query = "select * from mytable2 where unique_id in ({$idsString})";

Note that this won't work, if the $string var is an empty string, because databases usually don't support empty IF clause.

answered May 03 '20 at 20:52

Martin Heralecký

5,649
3
27
65

thnx.. I will surely try with this solution and will let you know... I think this is what I need..... – darshan May 03 '20 at 20:56
I tried this. It worked but it is fetching only one record with value at first in comma separated list. – darshan May 04 '20 at 05:33

score 0 · Answer 3 · answered May 03 '20 at 20:49

0

you can use this function to "explode" the string, and then you need just:

$query = "select * from mytable2 where unique_id IN SPLIT_STRING('$string', ',', 1)";

be sure to sanitize the string, which should not contains any '

answered May 03 '20 at 20:49

Alberto Sinigaglia

12,097
2
20
48

thnx.. I will try with this solution and will let you know... – darshan May 03 '20 at 20:55

score 0 · Answer 4 · answered May 03 '20 at 20:56

Following your $string = "ABCD1,BCDE2"; (retrieved from mytable1)

You have to split the string with comma separated.

e.g. $string = explode(",", $string);, so it will be $string[0] = "ABCD1" and $string[1] = "BCDE2". And you can customize your query with these.

select all rows where column contains value from comma separated values

4 Answers4