MySQL Query - Find_in_set on comma separated columns

Question

I have an issue with a Query I'm conducting to do a search on a Database of events. The purpose is about sports and the structure is:

id_event  event_sport   event_city
   1          10           153
   2          12           270
   3          09           135

The table sports is like:

sport_id     sport_name
   1         Basketball

and the table cities is:

city_id     city_name
   1         NYC

So things get complicated, because my events table is like:

id_event  event_sport   event_city
   1         10,12       153,270
   2         7,14        135,271
   3         8,12        143,80

and I have a multi-input search form, so that people can search for events in their city for multiple sports or for multiple cities. I'm using Chosen

The search resultant from Chosen is, for example:

City = 153,270 (if user selected more than one city)
Sport = 12 (if user only selected one sport, can be "9,15")

So what I need is to search for multiple values on cities and sports in the same column, separated by commas, knowing that sometimes we can be searching only for one value, if user didn't input more than one.

My current query is:

SELECT * FROM events e
LEFT JOIN cities c ON e.event_city=c.city_id
LEFT JOIN sports s ON e.event_sport=s.sport_id
WHERE FIND_IN_SET('1CITY', e.event_city) AND FIND_IN_SET('1SPORT', e.event_sport)
;

Which is good to search for one city, but if the user searches for two or more, I don't have way to show it.

Can you please help me?

Thanks in advance.

You might want to look at changing your data model, it's bad practice to store comma separated values in cells as it gives you less flexibility. What I would do would be a second table linked to event with all the cities, and another for all the sports. — JanR, Jun 28 '13 at 00:49
I will also suggest having a look at this https://stackoverflow.com/questions/5015403/mysql-find-in-set-with-multiple-search-string gem of an answer. — Afshar, Feb 20 '20 at 08:45

score 7 · Accepted Answer · answered Jun 28 '13 at 00:54

When the user inputs multiple cities and/or sports, split it on commas, and then the query should look like:

SELECT * FROM events e
LEFT JOIN cities c on e.event_city = c.city_id
LEFT JOIN sports s ON e.event_sport = s.sport_id
WHERE (FIND_IN_SET('$city[0]', e.event_city) OR FIND_IN_SET('$city[1]', e.event_city) OR ...)
AND (FIND_IN_SET('$sport[0]', e.event_sport) OR FIND_IN_SET('$sport[1]', e.event_sport) OR ...)

Using PHP you can build up those OR expressions with:

$city_list = implode(' OR ', array_map(function($x) { return "FIND_IN_SET('$x', e.event_city)"; }, explode(',', $_POST['cities'])));

Do the same to make $sport_list, and then your SQL string would contain:

WHERE ($city_list) AND ($sport_list)

As you can see, this is really convoluted and inefficient, I recommend you normalize your schema as suggested in the comments.

@mcv You really should normalize your data instead of storing a comma-separated list in a table column — Barmar, Nov 02 '15 at 17:20
oh i agree but the site i'm working upon is no where close to a good standard. I'm working towards it. That is a later stage in the game. I wish it was the first, but I walked in and I have literally scrapped tons of spaghetti code of looping queries mixed with php and html and javascript. A developers nightmare if you will. — mcv, Nov 02 '15 at 17:48

MySQL Query - Find_in_set on comma separated columns

1 Answers1