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 :)

Are you using EAV pattern and you want to query by custom fields? As far as I understand you need to have a users table and table with custom fields for user, right? I don't quite understand the structure of your data. — Oleg, Mar 01 '12 at 10:33
The categories you are looking for ("location", "job title") are indicated by the `field_id`, right? If so, you can use sub queries which only select those categories and go on from there. — TPete, Mar 01 '12 at 10:33
@tpete well, location is london manchester, job title web designer accounting, I can't make different tables, as this was an open source project and will create more problems that solve them this small problem :) — Val, Mar 01 '12 at 10:36

Konerak · Accepted Answer · 2012-03-01T10:44:29.017

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")

edited Mar 01 '12 at 10:44

answered Mar 01 '12 at 10:31

Konerak

39,272
12
98
118

did you forget the `as` keyword? `... as y1`? or is that optional? – Val Mar 01 '12 at 10:34
2

It's optional, but if you think it more clear, feel free to add it. – Konerak Mar 01 '12 at 10:35
1

you should join on `user_id`, otherwise, the query won't return anything – newtover Mar 01 '12 at 10:42
Where was you the past two days lol,? I have tried everything, inner join, join, left join, so many combinations I new I was tooooo close to getting it done, but this does work :) || also if someone should want to use the `LIKE %%` on the `IN()` function should check this out :) http://stackoverflow.com/questions/1127088/mysql-like-in many thnks guys, muchly appriciated. – Val Mar 01 '12 at 10:50

score 1 · Answer 2 · answered Mar 01 '12 at 11:10

   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%')

MySQL correct way of selecting

2 Answers2