0

I have a db of personnel which every person can have one or multiple countries. The countries can be added from a multiple choice drop down to db. A person could be like this 

name   country   
john   USA, UK, Poland   
joe    USA  
Jack   Germany

user can filter people by changing the value of <%= country %> I am trying to filter people based on user's choice. I am using this query 

 SELECT *
FROM `personnel`
WHERE <%= country %> LIKE `country`
ORDER BY `industry` ASC, `name` ASC

the problem is this select filters only rows(people) with single country, and it doesn't show people who has more that one country. I tried to use = instead of LIKE but that one didn't work also

Korbin
  • 578
  • 5
  • 18
  • 4
    Fix you data structure, so you have a table called `PersonnelCountry`, with one row per person and country. – Gordon Linoff May 30 '17 at 10:57
  • the db is personnel, my bad. yes every person has one row and in country column they can have either one or multiple countries – Korbin May 30 '17 at 10:59
  • But the best solution would be to normalise your table structure as @GordonLinoff has suggested. – Shadow May 30 '17 at 11:06
  • @Shadow, the find_in_set was the answer tnx, this fixed my problem find_in_set(<%= country %>, `country`) – Korbin May 30 '17 at 11:16

1 Answers1

0

You need to add % in LIKE sentence. LIKE '%country%'
% allows search in all the string, no matter what has after and before.
If you LIKE 'country' it show only the person with single country.
Cheers

Roy Bogado
  • 4,299
  • 1
  • 15
  • 31