我有一个具有动态用户信息的系统。基本上,我有一个具有ID,名称,电子邮件和密码(必需的基本知识)的用户表。而且,如果系统管理员希望添加用户字段,则可以通过在user_fields表中添加新条目来添加用户字段,然后将该字段的数据存储在user_fields_data中。

用户:

id | name      | email         | password
=============================================
1  | bob smith | [email protected] | asdfasdfasdf
...


user_fields:

id | field
==========
1  | address_1
2  | address_2
...


user_fields_data:

user_id | field_id | value
===============================
1       | 1        | 123 abc st
1       | 2        | suite 314


基本上,我的目标是执行一个查询,以获取所有信息。

array(
    'name'      => 'bob',
    'email'     => '[email protected]',
    'password'  => 'asdfasdfasdf',
    'address_1' => '123 abc st',
    'address_2' => 'suite 314'
)


我整天将表连接在一起,但是我从来不需要以这种方式(行/列)连接一个表。

提前致谢!

最佳答案

select 'name' as name, name as value from user where id = 1
union all
select 'email', email from user where id = 1
union all
select 'password', password from user where id = 1
union all
select uf.field, ufd.value
from user_fields_data ufd
inner join user_fields uf on ufd.field_id = uf.id
where ufd.user_id = 1


SQL Fiddle Example

输出:

|      NAME |         VALUE |
-----------------------------
|      name |     bob smith |
|     email | [email protected] |
|  password |  asdfasdfasdf |
| address_1 |    123 abc st |
| address_2 |     suite 314 |

关于php - MySQL联合/联合行与列,我们在Stack Overflow上找到一个类似的问题:https://stackoverflow.com/questions/12287922/

10-13 00:49