CODEWITHSUNDEEP
phpmysqlfilter
Ana DEV
Ana DEV

Reputation: 1030

Search comma delimited values in the Database with SELECT MYSQL query

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?

Upvotes: 1

Views: 67

Answers (4)

Rohit Mittal
Rohit Mittal

Reputation: 2104

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.

Upvotes: 0

blupointmedia
blupointmedia

Reputation: 604

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.

Upvotes: 1

Vidal
Vidal

Reputation: 2621

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.

Upvotes: 0

MonkeyZeus
MonkeyZeus

Reputation: 20737

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.

Upvotes: 0

Related Questions