SQL/PHP find_in_set

I'm trying to pull results where 1 row from tableA (profiles.category) matches 1 row from tableB (projects.categorysecond) however I'm not getting results.

*IMPORTANT projects.categorysecond will vary between having only 1 category to several category deliminated by ; EXAMPLE: OTTAWA OR OTTAWA;TORONTO;MONTREAL OR OTTAWA;MONTREAL OR TORONTO;MONTREAL

profiles.category will always only have 1 category, never deliminated.

I need to make sure that regardless if I have OTTAWA OR OTTAWA;TORONTO;MONTREAL in profiles.category it PULLS results as long as 1 word matches.

I'm currently trying the following query:

SELECT p.*, up.* FROM users_profiles up INNER JOIN projects p ON find_in_set(up.category, p.categorysecond) > 0

Solution

FIND_IN_SET() only understands comma as a separator. Read https://dev.mysql.com/doc/refman/5.7/en/string-functions.html#function_find-in-set

So you could substitute ; with , and then do the comparison:

SELECT p.*, up.* 
FROM users_profiles up 
INNER JOIN projects p 
  ON FIND_IN_SET(up.category, REPLACE(p.categorysecond, ';', ',')) > 0

I have to comment that this is not a good way to store data if you want to write expressions that find individual words in your semicolon-separated string. See my answer to Is storing a delimited list in a database column really that bad?

You should store one project category per row in a project_categories table. Then your query would be easier:

SELECT p.*, up.* 
FROM users_profiles up 
INNER JOIN project_categories pc
  ON up.category = pc.category
INNER JOIN projects p 
  ON p.project = pc.project;

With a compound index on project_categories(category,project), this query should be optimized pretty well.