Results 1 to 3 of 3
  1. #1
    Join Date
    Nov 2009
    Posts
    2

    Unanswered: Help collating multiple records from multiple tables into a single record

    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.

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by oorin View Post
    This table was designed to allow a web site administrator a high level of configuration for collecting data.
    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
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Nov 2009
    Posts
    2
    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.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •