MySQL correct way of selecting

Question

I have a table like the following

id    field_id     field_value         user_id
1     4           'london'             12
2     4           'manchester'         33
3     25          'accounting'         12
4     25          'web designer'       37
5     27          'food'               12
6     27          'wine'               34

MY problem now is that I need to query it in human terms as follow,

I need to split the table somehow on a select query so I can search like so,

I need to search all users, which have ("london") AND ("accounting" OR "web design) AND (food OR wine)

My problem is I cannot split the field_value to seperate "location", "job title", "interests" if they where on different fields I would find it easy to do, but as they are on the same field, I am finding it difficult to use the AND and OR combination,

If the questions is difficult to understand I will try and explain it further. many thnx

Also I would not Mind if there is a combination of php and mysql solution :)

Answer 1

   SELECT *
    FROM table
    WHERE user_id IN(SELECT user_id FROM table WHERE field_value LIKE '%london%')
    AND user_id IN (SELECT user_id FROM table WHERE field_value LIKE '%accounting%' OR field_value LIKE '%web design%')
    AND user_id IN (SELECT user_id FROM table WHERE field_value LIKE '%food%' OR field_value LIKE '%wine%')

Answer 2

You are facing the problem because your table is designed using the EAV (Entity–attribute–value model) (anti)pattern.

A better table design would be to have a table 'locations', a table 'job title' and a table 'interests'. It would also solve your problem.

However, you CAN work with this design, but it's a but more troublesome.

I need to search all users, which have ("london") AND ("accounting" OR "web design) AND (food OR wine)

SELECT y1.* 
FROM yourtable y1
JOIN yourtable y2
ON y1.USER_ID = y2.USER_ID
JOIN yourtable y3
ON y2.USER_ID = y3.USER_ID
WHERE y1.field_id = 4
AND y1.field_value = "london"
AND y2.field_id = 25
AND y2.field_value IN ("accounting", "web design")
AND y3.field_id = 27
AND y3.field_value IN ("food", "wine")