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:

SELECT * FROM USERS INNER JOIN settings ON users.id=settings.id

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: bob@bob.com | attribute: type | value : 1
ID: 1 | username: bob | email: bob@bob.com | attribute: role | value : 4

I'd get:

ID: 1 | username: bob | email: bob@bob.com | 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.

Thanks!

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