Argh, Dynamic Querying

Anon

Right now, I'm developing an application that has dynamic fields. In my table structure:

id|fieldname|fieldvalue

When this begins to populate, searching for data becomes difficult.

IE:

Now, I want to run a query to find out if there is a match where (first_name = john and last_name = doe) or (first_name=jack and last_name=moe).

Hopefully you can see the problem. Ppl have suggested naming the tables with the fieldnames instead, but this is dynamic fieldnames. The end user could be entering in 50 fieldnames and be removing them on the fly.

MySQL 4.0 has the function called UNION which would be perfect for me, however, we don't want to upgrade from 3.23.41 yet.

So my question is whether there is a better database structure for this type of querying or a work around to simulate UNION queries using PHP.

Anon

How can you tell which fields belong to one record?

Anon

Well, I forgot to put another column, a contact_id column that would identify what records belong to who.

Some contacts, may contain street addresses while others do not. And some may contain other info that really doesn't mean anything to other contacts, ie, if the user has 5 different phone numbers whereby in this table, it lists, the cell phone, fax, home, office, and boat.

ID|contact_id|fieldname|fieldvalue

Anon

Right now I have some dummy data:

ID|UserID|FieldName|FieldValue

Now, my query wants to get a result where State is GA and City is Atlanta.

Works fine if query was just one criteria:

SELECT * FROM test WHERE (fieldname = 'State' AND fieldvalue='GA')

But appending some additional querying looks impossible. Right now, I'm stumped with the db structure, that is if there is a better way of handling when you don't know what fields will be create and what values are.

MySQL's UNION would be perfect in this setup, but how would I get around this?

Anon

You might save yourself some time by determining which fields you are going to need for your contact information.

ie.
First name,Last Name, Phone, Email, City, State, Zip

Then your query would simply be.
SELECT * FROM test WHERE state='GA' and city='Atlanta'

You could also easily append additional criteria to this.
SELECT * FROM test WHERE state='GA' and city='Atlanta' and first_name='Jonathan'

You could make this dynamic fairly easily also.

if(!empty($first_name)){
$where .= "and first_name='$first_name'";
}

Append the string to $where for all the criteria than tie it into a query

$query = "SELECT * FROM test WHERE " . $where;

Hope this is helps..

John