Results 1 to 2 of 2
  1. #1
    Join Date
    Jun 2009

    Unanswered: Options table JOIN

    I'm just looking for some advice on a good way to handle the following situation:

    I have a users table which contains a standardized row set (username, password, email, etc). However, each user can also have numerous settings and other various attributes linked to their account. These are not standardized and one user may have many more attributes than another.

    Currently, I have two tables: users and settings in an a one-to-many setup, which I make possible via an INNER JOIN. With a query a little like this:


    The settings table consists of three rows: ID (which is a foreign key linking to the users table), attribute and value. These are pretty self-explanatory. You have an attribute name and a value for that attribute.

    The problem here is that the above query returns the user tables column set (username, password, etc) with attribute and value as column names. So, for each settings row that belongs to the user, I'm currently getting a result row containing the users details (username, password, etc) along with two columns named attribute and value.

    Essentially, what I'm looking to do is have each attribute name be returned as a column name. So, say for example two rows from the settings table were found for a particular user:

    ID: 1 | attribute: type | value: 1
    ID: 1 | attribute: role | value: 4

    Rather than getting the following results:

    ID: 1 | username: bob | email: | attribute: type | value : 1
    ID: 1 | username: bob | email: | attribute: role | value : 4

    I'd get:

    ID: 1 | username: bob | email: | type: 1 | role: 4

    I'm fairly certain this isn't possible using just MySQL, but I'd like input on how other people would handle this situation. I'm using PHP to interpret any SQL results, if that makes any difference.


  2. #2
    Join Date
    Apr 2002
    Toronto, Canada
    what you have there is called an "EAV" design -- entity-attribute-value

    it's deceptively simple, isn't it -- until it comes time to extract meaningful data

    i could give you links to articles which outline all the problems with EAV, except you prolly don't want to see that info

    pull your data into php and manipulate it there | @rudydotca
    Buy my SitePoint book: Simply SQL

Posting Permissions

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