SELECT WHERE any value in csv in array

Question

Suppose I have this array:

$city = ["toronto", "chicago", "potato"];

and my table looks like this:

Table Name: city_table
id    city
1     toronto, orlando
2     buffalo, toledo
3     orlando, tomato
4     potato, chicago, nanaimo

Is it possible to have a query that gives me this result?

id    city
1     toronto, orlando
4     potato, chicago, nanaimo

I tried this but it obviously doesn't work:

$city_string = "'toronto', 'chicago', 'potato'";
SELECT * from city_table WHERE city IN $city_string

Don't use comma-separated values in SQL, normalize your data. — Barmar
– Barmar, Commented May 14, 2015 at 16:17
I'd parse the csv/array into a constructed query and still use IN, you could use FIND_IN_SET as the answers below suggest, but I am pretty sure it would ignore the benefits of any indexes...actually neither will work, as @Barmar pointed out, you city field is loaded with CSVs as well...you're effectively looking for intersecting arrays, with neither array represented effectively. — Uueerdo
– Uueerdo, Commented May 14, 2015 at 16:36
I just thought that it'd better to use one column for storing answers to checkboxes separated by commas than to use 20 columns for 20 checkboxes but I guess that's a bad idea? — Vincevna
– Vincevna, Commented May 14, 2015 at 17:32

Barmar · Accepted Answer · 2015-05-14 17:29:45Z

2

To search a comma-separated value in MySQL you use FIND_IN_SET. It can only search for 1 item at a time, so you need to call it separately for each name $city, and combine them with AND.

SELECT *
FROM city_table
WHERE FIND_IN_SET('$city[0]', city_table)
OR FIND_IN_SET('$city[1]', city_table)
OR FIND_IN_SET('$city[2]', city_table)

In your actual code you should use implode() to construct the WHERE clause dynamically.

Note that you should not have spaces after the commas in the table, FIND_IN_SET will not work with the CSV the way you've written it.

However, it would be better to normalize your schema so you don't have a comma-separated value. FIND_IN_SET can't be optimized with an index, so it will have to perform a full table scan.

edited May 14, 2015 at 17:29

answered May 14, 2015 at 16:22

Barmar

789k57 gold badges554 silver badges669 bronze badges

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

2 Comments

Vincevna Over a year ago

I changed your query, replacing AND with OR, city_table (in WHERE clause) with city, and removed the spaces as you suggested and it worked. Thanks.

Barmar Over a year ago

I didn't read your example carefully enough, I thought you wanted all the cities, not just any of them.

Willian Keller · Accepted Answer · 2015-05-14 16:28:19Z

1

You can use FIND_IN_SET at MySQL.

Example

SELECT *
FROM city_table
WHERE FIND_IN_SET('$city[0]', city_table)
AND FIND_IN_SET('$city[1]', city_table)
AND FIND_IN_SET('$city[2]', city_table)

answered May 14, 2015 at 16:28

Willian Keller

4295 silver badges14 bronze badges

Collectives™ on Stack Overflow

SELECT WHERE any value in csv in array

2 Answers 2

2 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related