How can we use search conditions (.. and ..or..) for columns instead of rows in mysql?

Question

Im working on a php-mysql real estate project and its nearly done. But im in trouble with "detailed property search" part. I do not know if I could express my question sufficiently title, but im gonna explain you all.

In the critical part, i have 3 tables in my project: "properties","property_details", and "features". The properties table stores the fundemantal informations about each properties like id,title,category,sub category,city ext. The features table stores the all possible spesific features for properties. And it has 3 columns: id,title and value. The title column refers to another table, so it is irrelevant for this question. And lastly, the property_details table has 3 columns: property_id,feature_id, and value. You can ask me "why you use value column twice in two tables?" but some features has fixed values (like has garage or not) and some features values are typing by user like number of rooms.

In "detailed property search" page, the program lists all proper features for selected property category and by the user's choises, it creates a long sql where condition like : "(property_details.feature_id = 4 and property_details.value < 10) Or (...) And (...) ext. My problem starts here, i know mysql runs queries row by row, but in my property_details table, a property can be more than one and have too many different features. But i cant use my where condition because of different rows. Is there any way to do queries with my spesific "where" contidions by columns instead of rows? Or any other advices? I would be grateful for your help!

I want to show you what i need exactly: property_details table:

property_id| feature_id | value

 1     |     3      |  10
 1     |     4      |  11
 2     |     3      |  9
 1     |     2      | 200

My table is looks like that now. And i need to find a property which (feature_id = 3 AND feature_id = 4) Or (feature_id = 2 AND value > 100). I cant make this query naturally. But i need to do. Because there are lots of different features (about 400-500) and making every feature as a column is not logical. I need to use this table as such.

Answer 1

You have to use EXISTS here:

SELECT * FROM   properties prp
WHERE EXISTS
(
    SELECT * FROM property_details pd
    WHERE  pd.property_id = prp.id
    AND    pd.feature_id = 3
)
AND EXISTS
(
    SELECT * FROM property_details pd
    WHERE  pd.property_id = prp.id
    AND    pd.feature_id = 4
)
OR EXISTS
(
    SELECT * FROM property_details pd
    WHERE  pd.property_id = prp.id
    AND    pd.feature_id = 2
    AND    pd.value > 100
)

It's not elegant, but that's the consequence of using an EAV model. I know by experience that sometimes EAV is inevitable, so I won't urge you not to use it, but it won't ever be convenient in querying and presenting.