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?
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?