Select rows where any of field's values matches values from array

Question

I have a pretty complicated task. I need to select rows which match any of an array's value - BUT the field contains many comma-seperated values as well.

$teamlist = "25,26,27,28,29,30"

MYSQL-Row 1 = "26,29,31,35,36"
MYSQL-Row 2 = "30,31,32,36,39"
MYSQL-Row 3 = "31,35,36,37,38"
MYSQL-Row 4 = "23,26,29,30,31"

As result Rows 1,2 and 4 should be selected.

I tried something like:

mysqli_query($con,"SELECT * FROM user_meta WHERE team IN '".$ids."'");

But that only works if the fields only contain one id, not multiple. I am stuck. Any ideas?

Thanks!

There's a vote to reopen. It seems that @Strawberry may not have used the right duplicate to close the question with. [That Q&A](https://stackoverflow.com/questions/3653462/is-storing-a-delimited-list-in-a-database-column-really-that-bad) is more opinionated oriented if you ask me. That vote to reopen isn't mine, since if it would have been, it would have already been reopened. — Funk Forty Niner, Mar 11 '20 at 20:44
See https://meta.stackoverflow.com/questions/333952/why-should-i-provide-a-minimal-reproducible-example-for-a-very-simple-sql-query — Strawberry, Mar 11 '20 at 22:16

GMB · Answer 1 · 2020-03-11T20:39:51.733

1

You could pass your parameters as a derived table and then use find_in_set() to search the csv column:

select t.*
from mytable t
inner join (
    select 25 team_id
    union all select 26
    union all select 27
    ...
) x on find_in_set(x.team_id, t.team)

This leaves you with the work of building the derived table from your application. In very recent versions of MySQL, the VALUES() statement makes the task a litte easier:

select t.*
from mytable t
inner join (values row(26),row(27), ...) x(team_id)
    on find_in_set(x.team_id, t.team)

However, you should not be storing lists in a database columns. This is hurtful in many ways, as explained in this famous SO answer. You should be storing each value of the each list on a separate row.

edited Mar 11 '20 at 20:39

answered Mar 11 '20 at 20:34

GMB

216,147
25
84
135

The answer given appears to answer the question. Have you noticed the vote to reopen and [my comment](https://stackoverflow.com/questions/60644027/select-rows-where-any-of-fields-values-matches-values-from-array#comment107291952_60644027) under the question? – Funk Forty Niner Mar 11 '20 at 20:46
@FunkFortyNiner: thanks. Yes questions related to CSV tend to get closed as duplicate to this other question (which I linked in my answer by the way), while relevant answers may exists in some cases. – GMB Mar 11 '20 at 20:59
@FunkFortyNiner: from your comment, I understand that only one vote mysql gold badge bearer is needed to reopen, is that so? – GMB Mar 11 '20 at 21:00
Welcome. Yes, given that that member has it for a given tag that was originally tagged by the OP. In your opinion, should the question be reopened? – Funk Forty Niner Mar 11 '20 at 22:24
It seems that the question was already reopened before I commented above this one. – Funk Forty Niner Mar 11 '20 at 22:25
@FunkFortyNiner: I was wondering - but usually I tend not to make use of the gold badges powers... – GMB Mar 11 '20 at 22:37
Ok. Well, that badge is given for a reason and the many questions that come up more often are usually closed with proper duplicates. I tend to think that I am not abusing that privilege. Some tell me when I may have closed with an invalid link and it gets resolved rather fast and friendly like :-) – Funk Forty Niner Mar 11 '20 at 22:55

Select rows where any of field's values matches values from array

1 Answers1