Search comma delimited values in the Database with SELECT MYSQL query

Question

I have an issue of getting results from the database. I have a column called "Regions" which as a value contains all the regions user had chosen via newsletter signup form with commas i.e "Middle East, Europe"

I am creating a filter which will need to get all the users list who signup for a particular or multiple regions. Users can select multiple regions. From the frontend am getting all the selected regions in this format "Global,Middle East" which i need to use in my SQL query to find all the users who has the Global and Middle East as a chosen region.

I tried to use FIND_IN_SET but it is not really helping. Tried the below

 $trimmedRegions = rtrim($sortType,', ');

 $query = "SELECT * FROM health_alerts_subscribers
            WHERE FIND_IN_SET(Regions, $trimmedRegions)";

Any ideas how I can achieve the needed result please?

The best solution would be to refactor your database and use a seperate table and alink table to store this data. Columns containing delimited text data will always cause you more heart ache in the end rather than spending a few minutes designing the database correctly — RiggsFolly, Feb 22 '19 at 15:00
*"a value contains all the regions user had chosen via newsletter signup form with commas"* - I guess you're regretting that design decision now. *"The data depends on the key [1NF], the whole key [2NF] and nothing but the key [3NF] so help me [Codd](https://en.wikipedia.org/wiki/Edgar_F._Codd)."* Oddly enough #1 result on Google for "the key the whole key and nothing but" lands you on another [SO question about data normalisation](https://stackoverflow.com/questions/723998/what-are-database-normal-forms-and-can-you-give-examples). — CD001, Feb 22 '19 at 15:00
The `FIND_IN_SET` should work. Can you share how it was used and how it performed? I'd go the normalized route though, delimited data will always cause problems. — user3783243, Feb 22 '19 at 15:04
@user3783243 i updated my question. So you suggest to keep all the regions in seprate and have the values like yes or no? — Ana DEV, Feb 22 '19 at 15:15
I think if you want all the rows from global you can just use like %Global%, so you will need to do a query per region. — Vidal, Feb 22 '19 at 15:16
@Vidal what if i have multiple chosen for the filter like Global and Europe? — Ana DEV, Feb 22 '19 at 15:21
see my answer, you do 2 like where region like ("%Global%") and region like("%Europe%") — Vidal, Feb 22 '19 at 15:22
You need a `FIND_IN_SET` for each individual value. `WHERE FIND_IN_SET(Regions, 'Middle East') and FIND_IN_SET(Regions, 'Global')`. I would make a different table that has a relation to each record. — user3783243, Feb 22 '19 at 15:35

blupointmedia · Accepted Answer · 2019-02-22T15:34:36.497

1

I recommend what user3783243 is saying. I would go with FIND_IN_SET. Here is an example.

SELECT {columns} FROM {table} WHERE FIND_IN_SET({item_to_search}, {comma-delimited column})

A better way would be to create a regions table and add your regions, and then create a related table called, regionsRelated (example) and then add an id from region and the linking table (as a many to many)

Table structure example

Table region
id,region
1, Middle East
2, Global

Table user
id,name
1, John
2, Jan

Table userRegion
userId,regionId
1,1
2,1

SELECT user.id,user.name,region.region from user
left join userRegion ON region.userId=user.id
left  join region
where region.region in('Middle East','Global');

In your drop down list just select all regions and when the user selects the ones they want then you would store it in the userRegion table with INSERT INTO userRegion (userid,regionId) (1,2); and so on.

edited Feb 22 '19 at 15:34

answered Feb 22 '19 at 15:07

blupointmedia

564
2
10

in the place item_to_search what should i put? the string with comma delimited regions selected? and {comma-delimited column} i put the name of the column? – Ana DEV Feb 22 '19 at 15:23
So, do you recommend or not? – Strawberry Feb 22 '19 at 15:33
If i just have each region as a separate column and with a value "yes" or "no" will it solve my issue? – Ana DEV Feb 22 '19 at 15:38
Anahit, then you would have to keep adding a new column for every new region. That isn't the proper way. I recommend my example above. – blupointmedia Feb 22 '19 at 15:40
If a user writes you an email and says their region isn't listed. Then all you have to do is add the new region to the region table and everything else will continue to work. You won't have to change the structure of your database. – blupointmedia Feb 22 '19 at 15:42
The thing is that Regions are fixed, they are 6 Global, Africa, Mid East, Americas, Asia and Europe – Ana DEV Feb 22 '19 at 15:44
You an go that route too. There are technically 8. I would create a boolean column for each one and 1 for yes/true and 0 for no/false – blupointmedia Feb 22 '19 at 15:46
Ok and then with that my query will be like select where the value is 1 ? – Ana DEV Feb 22 '19 at 15:53
SELECT {columns} from {table} WHERE africa=1 OR asia=1 and so on... You would have to build the query based on the user's selection – blupointmedia Feb 22 '19 at 15:55
If it works out , please accept the answer. Thanks and good luck. – blupointmedia Feb 22 '19 at 16:11
Worked :) Thanks a lot – Ana DEV Feb 22 '19 at 17:48

score 0 · Answer 2 · answered Feb 22 '19 at 15:07

0

You pretty much need to format your sql like this:

SELECT
    *
FROM
    table
WHERE
    regions = 'Europe' or
    regions like 'Europe,%' or
    regions like '%,Europe' or
    regions like '%,Europe,%' or
    regions = 'Middle East' or
    regions like 'Middle East,%' or
    regions like '%,Middle East' or
    regions like '%,Middle East,%'

In PHP you will need to loop through the regions which the user has selected and dynamically build a query like this.

answered Feb 22 '19 at 15:07

MonkeyZeus

20,375
4
36
77

Why so many likes, you can use one wild and the start and another at the end. like "%value%" – Vidal Feb 22 '19 at 15:14
2

@Vidal I have no idea what the full list of regions looks like so if `regions` contains something like `Global,Middle East,Europe,East,Asia` then `%East%` would return too many results. – MonkeyZeus Feb 22 '19 at 15:29
@Vidal I'm sure it's possible to `REGEXP_LIKE()` this whole situation but I don't have the patience to figure it out for OP. They should normalize their tables and move on with their life. It's gonna be a real sh*t-show if a region ever changes their name, capitalization, or breaks into multiple regions or something. – MonkeyZeus Feb 22 '19 at 15:35

score 0 · Answer 3 · answered Feb 22 '19 at 15:07

0

I can suggest this approach.

SELECT * FROM table
WHERE 
   region like("%Global%")
   and region like ("%Middle East%")

In this way you can get the people subscribe to both of them. You can tweak it for your needs, obviously is not going to be the best solution, but you can use it to create a pivot table in case you want to update you db structure.

answered Feb 22 '19 at 15:07

Vidal

2,605
2
16
32

but like these i will have many requests and i need to select the chosen results and not static – Ana DEV Feb 22 '19 at 15:24
You want to get the subscribe list to send an email? what is the purpose to see if I can get you a better answer? – Vidal Feb 22 '19 at 15:27
Yes i will need to get the list which then will be imported to the mailing list by the regions. – Ana DEV Feb 22 '19 at 15:28
ok, you can do the query [SELECT * FROM table WHERE region like("%Global%")] and get all the global subscriber and then save them to the Global Mailing list, then make a query for the next region Middle East and son on. – Vidal Feb 22 '19 at 15:32
ok but what i do with the users who has multiple regions as a value? With a single query it is not bringing that users – Ana DEV Feb 22 '19 at 15:33
I think this is not a one shot query, you may have to query all the different regions individually. The users that are subscribe to multiple regions they will be in their corresponding mailing list maybe multiple as they selected. – Vidal Feb 22 '19 at 15:35
So if i have all the regions in separate columns with a value of "yes" or "no" will i achieve the result? The user need to receive one email with all the regions and not multiple emails – Ana DEV Feb 22 '19 at 15:40
OK now I am confused, if you are going to mail the user his/her regions, just query the table without any where or region filter. What you want to get from the table? What is the purpose if you can share that? – Vidal Feb 22 '19 at 15:43
I need to email the user news about his selected regions so i need to know which users are for example for Europe & Asia so i export that list – Ana DEV Feb 22 '19 at 15:45
You send a different email for each region ? – Vidal Feb 22 '19 at 15:47
OK then my 4th comment is what you need., you will need to do a query per region. – Vidal Feb 22 '19 at 15:49

Rohit Mittal · Answer 4 · 2019-02-22T17:49:46.933

There is not exact function for your requirement but you can use below code:

$trimmedRegions = rtrim($sortType,', ');
$trimmedRegionsCollection = explode(",", $trimmedRegions);
$trimmedRegionsRegex = implode("|", $trimmedRegionsCollection); // make string like Global|Middle East

$query = 'SELECT * from health_alerts_subscribers WHERE CONCAT(",", `Regions`, ",") REGEXP ",('.$trimmedRegionsRegex.'),"';

Hope it helps you.

Search comma delimited values in the Database with SELECT MYSQL query

4 Answers4