MySql select by column value. Separeta operator for columns.

Question

Hi all, i have a mysql table like this

+-----+---------+-----------+-----------------+-------+
| id  | item_id | item_type | field_name      | data  |
+-----+---------+-----------+-----------------+-------+
| 258 |      54 | page      | field_interests | 1     | 
| 257 |      54 | page      | field_interests | 0     | 
| 256 |      54 | page      | field_author    | value | 
+-----+---------+-----------+-----------------+-------+

And, I need build query like this

SELECT * FROM `content_fields_data` WHERE 
(`field_name`='field_author' AND `data`  LIKE '%lu%')
AND
(`field_name`='field_interests' AND `data`='1')

But it returns 0 rows. If I change it or

SELECT * FROM `content_fields_data` WHERE 
(`field_name`='field_author' AND `data`  LIKE '%lu%')
**OR**
(`field_name`='field_interests' AND `data`='1')    

I need to select all rows 
WHERE 
  field_name.field_author.data LIKE '%text%'
AND
  field_name.field_interests.data = '1'

I can explain it like a simple function:

IF table.field_name = 'field_author' THAN USE LIKE operator
IF table.field_name = 'field_interests' THAN USE equal(=) operator

Cant anybody help me?

Thanks.

Answer 1

I think you have to use OR instead of AND (otherwise it's clear you get no rows! a thing can't be equal to two different things...). So

SELECT * FROM table WHERE
(`field_name`='field_author' AND `field_author.data` LIKE '%jo%')
 OR
(`field_name`='field_interests' AND `field_interests.data`='0')
 OR
(`field_name`='field_interests' AND `field_interests.data`='1')