That is probably a horrible description of what I need.

What I have is a two tables, one that contains "field" information. This table was designed to allow a web site administrator a high level of configuration for collecting data.

Table Layout:

id int(10)
type varchar(255)
name varchar(255)
tips text
visible tinyint(1)
required tinyint(1)
searchable tinyint(1)
options text
fieldcode varchar(255)

The other table simply contains the values for these configurable fields:

id int(10)
user_id int(11) -> user data contained in another table
field_id int(10) -> ties to id field in previous table
value text

Now what I want to do is create a tmp table based off of search criteria... but I want to collate the user data into a single record using the fieldcodes from the first table as columns. Because as you can imagine, if the admin has created 13 fields each user has 13 entries in the "values" table.

Can this be done with SQL or does it have to be done using multiple steps with a secondary processing language (in my case php)?

Thank you very much for your help.

while at the same time no consideration for how hard it would be to extract meaningful information

do a search for EAV (entity-attribute-value)

you will find a litany of woe, and warnings from experienced database designers to avoid this scheme if possible

you're gonna have to write some pretty slick application logic to produce the reports you want

Oh believe me... I know. I unfortunately joined the team late... this also means I'm not allowed to change what the previous developer did.

So what you're telling me - if I understand - is I need to go outside let out a primal scream and handle this outside of a SQL query.

Thank you for replying.