SQL Query For Dynamic Fields

wxdqz

New Member
The engine that I'm building requires dynamic fields, that is, a form's fields are built dynamically from fieldname/fielddefaultvalues from the database.

So the way data is stored into the database is:

ID|fieldname|fieldvalue

The ID is form's ID that when selected as a group, pulls out the fieldname/fieldvalues associated with the form's ID.

The problem I'm having though is being able to select data based on various information. IE,

1|address|PO BOX 727
1|state|GA
1|fname|John
1|lname|Doe2
2|address|PO BOX 1983
2|state|TN
2|fname|John2
2|lname|Doe2

They query I need to structure is a way so that I can select WHERE fname equals John2 and lname equals Doe2. However, since the column name in the table are labeled, fieldname|fieldvalue respectively, the query is incredibly hard for this situation to create.

I want to be able to select for any fieldname for a particular fieldvalue and return the ID of the forms that matches based on the criteria.

Here's my query so far:

SELECT * FROM table1 WHERE (fieldname='fname' AND fieldvalue='John2') AND (fieldname='lname' AND fieldvalue='Doe2');

You probably seee the problem as you can't match two rows with one 'AND' clause. I can't use 'OR' as the more clauses formed will narrow search results. Can this be achieved in SQL or should I bet better off querying for one particular data then get the result set, store that into a temp table, query the temp data with the new search criteria and recursively handle it till it completes all search criteria fields? Any suggestions?
 
Back
Top