mysql rbar situation

Question

What would be the best way to handle this situation?

I'm working on off the shelf software, and it is storing user defined field names in the database as a row instead of adding a new column to the table. It looks like this:

   ID fieldName  fieldValue propertyId
    1 latitude   23.192     id_property1
    2 longitude -10.323     id_property1
    3 latitude   23.192     id_property2
    4 longitude -11.824     id_property2
    5 latitude   25.254     id_property3
    "                                   "

I need to filter this table based on user input for fieldName and fieldValue, so if the user enters fieldName="latitude" AND fieldValue="23.192" it would return id_property1 and id_property2. But if the user enters fieldName="latitude" AND fieldValue="23.192" AND fieldName="longitude " AND fieldValue="-10.323" it would just return id_property1.

I made an approach using for loops in php, but I'm concerned that it would bog down under a lot of records.

Thanks!

Answer 1

Use a self-join:

SELECT p0.propertyId
FROM propsTable AS p0
JOIN propsTable AS p1 ON p1.propertyId=p0.propertyId
WHERE p0.fieldName='latitude' AND p0.fieldValue=23.192
AND p1.fieldName='longitude' AND p1.fieldValue=-10.323