我正在尝试编写一个 mysql 查询来提取这种格式的数据:

<table>
    <caption>table title and/or explanatory text</caption>
    <thead>
        <tr>
            <th>User ID</th>
            <th>First Name</th>
            <th>Last Name</th>
            <th>Field Name 1</th>
            <th>Field Name 2</th>
            <th>Field Name 3</th>
            <th>Field Name 4</th>
            <th>Field Name 5</th>
        </tr>
    </thead>
    <tbody>
    <?php 

        while ($row = mysqli_fetch_array($query)) {

        echo "<tr>
                <td>" .$row{'user_id'}. "</td>
                <td>" .$row{'first_name'}. "</td>
                <td>" .$row{'last_name'}. "</td>
                <td>" .$row{'field_name_1'}. "</td>
                <td>" .$row{'field_name_2'}. "</td>
                <td>" .$row{'field_name_3'}. "</td>
                <td>" .$row{'field_name_4'}. "</td>
                <td>" .$row{'field_name_5'}. "</td>
            </tr>";
        }
    ?>
    </tbody>
</table>

数据库中的表格式如下。

表:用户数据

user_id      |     first_name     |    last_name     |
------------------------------------------------------
1            |     Jeff           |    Smith         |
------------------------------------------------------
2            |     Bob            |    Smith         |
------------------------------------------------------
3            |     Steve          |    Smith         |
------------------------------------------------------
4            |     Mary           |    Smith         |
------------------------------------------------------
5            |     Anna           |    Smith         |
------------------------------------------------------

表:自定义字段

custom_field_id   |    name         |
-------------------------------------
3                 |    field name   |
-------------------------------------
5                 |    field name   |
-------------------------------------
7                 |    field name   |
-------------------------------------
9                 |    field name   |
-------------------------------------
11                |    field name   |
-------------------------------------

表:custom_field_data

user_id      |     custom_field_id    |    value     |
------------------------------------------------------
1            |     3                  |    XXXX      |
------------------------------------------------------
1            |     5                  |    BBBB      |
------------------------------------------------------
1            |     7                  |    CCCC      |
------------------------------------------------------
1            |     9                  |    ZZZZ      |
------------------------------------------------------
1            |     11                 |    YYYY      |
------------------------------------------------------
2            |     3                  |    XXXX      |
------------------------------------------------------
2            |     5                  |    BBBB      |
------------------------------------------------------
2            |     7                  |    CCCC      |
------------------------------------------------------
2            |     9                  |    ZZZZ      |
------------------------------------------------------
3            |     3                  |    XXXX      |
------------------------------------------------------
3            |     5                  |    BBBB      |
------------------------------------------------------
3            |     9                  |    ZZZZ      |
------------------------------------------------------
3            |     11                 |    YYYY      |
------------------------------------------------------

我正在寻找查询数据然后使用 PHP 或 AJAX 将其打印到屏幕的最佳解决方案。这可能吗？用json会不会更好？ 一个示例查询会很棒。

仅供引用:从长远来看，所有提取的数据都需要在屏幕上进行过滤。感谢您的帮助。

我想要的输出是

user_id      |     first_name     |    last_name     |  custom_field_3  |   custom_field_5  |   custom_field_7  |   custom_field_9  |   custom_field_11     |
-------------------------------------------------------------------------------------------------------------------------------------------------------------
1            |     Jeff           |    Smith         |  XXXX            |   BBBB            |   CCCC            |   ZZZZ            |   YYYY                |
-------------------------------------------------------------------------------------------------------------------------------------------------------------
2            |     Bob            |    Smith         |  XXXX            |   BBBB            |   CCCC            |   ZZZZ            |   YYYY                |
-------------------------------------------------------------------------------------------------------------------------------------------------------------
3            |     Steve          |    Smith         |  XXXX            |   BBBB            |   CCCC            |   ZZZZ            |   YYYY                |
-------------------------------------------------------------------------------------------------------------------------------------------------------------
4            |     Mary           |    Smith         |  XXXX            |   BBBB            |   CCCC            |   ZZZZ            |   YYYY                |
-------------------------------------------------------------------------------------------------------------------------------------------------------------
5            |     Anna           |    Smith         |  XXXX            |   BBBB            |   CCCC            |   ZZZZ            |   YYYY                |
-------------------------------------------------------------------------------------------------------------------------------------------------------------

最佳答案

您可以使用group_concat 函数，https://dev.mysql.com/doc/refman/5.0/en/group-by-functions.html#function_group-concat ，我之前的查询是为每个用户获取一条记录...

如果你有一个 SQLfiddle 我可以测试它但是我没有任何数据可以测试......

select ud.firstname, ud.lastname, group_concat(cf.name) 
from user_data as ud 
join custom_field_data as cfd 
on cfd.user_id = ud.user_id 
join custom_fields as cf 
on cf.custom_field_id = cfd.custom_field_id

我用类似的设置在 3 个表上测试了它，所以我认为它应该可以工作；名称可能需要调整。

更新:

select ud.firstname, ud.lastname, group_concat(cf.name) 
from user_data as ud 
join custom_field_data as cfd 
on cfd.user_id = ud.user_id 
join custom_fields as cf 
on cf.custom_field_id = cfd.custom_field_id
group by ud.user_id

关于php - 如何做 MySQL Join 并使用 PHP 获取值，我们在Stack Overflow上找到一个类似的问题： https://stackoverflow.com/questions/31908893/