PHP SQL连接查询在多数组中合并内容

admin

PHP SQL连接查询在多数组中合并内容

sql

我有一个SQL查询问题，我想继续使用PHP。这是我的数据库的结构：

表：部分

+------------+---------------+-----------------+--+--+
| section_id | section_titel | section_text    |  |  |
+------------+---------------+-----------------+--+--+
| 1          | Section One   | Test text blaaa |  |  |
+------------+---------------+-----------------+--+--+
| 2          | Section Two   | Test            |  |  |
+------------+---------------+-----------------+--+--+
| 3          | Section Three | Test            |  |  |
+------------+---------------+-----------------+--+--+

表格：sub_sections

+----------------+-------------------+------------------+-----+--+
| sub_section_id | sub_section_titel | sub_section_text | sId |  |
+----------------+-------------------+------------------+-----+--+
| 1              | SubOne            | x1               | 1   |  |
+----------------+-------------------+------------------+-----+--+
| 2              | SubTwo            | x2               | 1   |  |
+----------------+-------------------+------------------+-----+--+
| 3              | SubThree          | x3               | 3   |  |
+----------------+-------------------+------------------+-----+--+

这些部分通过第二个表中的“ sId”链接（sub_sections）。因此，标题为“ SubOne”的小节是ID为1的小节（来自小节表）的子小节。

我正在使用此查询来获取结果：

SELECT section_titel as t1, sub_section_titel as t2 
FROM sections LEFT JOIN sub_sections ON section_id = sId;

我的输出看起来像这样：

Array
(
    [0] => Array
        (
            [t1] => Section One
            [t2] => SubOne
        )

    [1] => Array
        (
            [t1] => Section One
            [t2] => SubTwo 
        )
)

那么问题是，我得到表“ sections”的多个结果。我需要这样的结果：

   Array
(
    [0] => Array
        (
            [t1] => Section One
            [t2] => Array
                (
                    [0] => SubOne
                    [1] => SubTwo

                )

        )

)

有什么办法吗？在此先谢谢了！

谢谢，J. Doe;）

阅读 195

2021-06-07

共1个答案

admin

您可以结合使用PHP和MySQL。将您的查询更改为此：

SELECT section_titel as t1, GROUP_CONCAT(sub_section_titel) as t2 
FROM sections LEFT JOIN sub_sections ON section_id = sId
GROUP BY t1
HAVING t2 IS NOT NULL

这将为您提供如下结果表：

t1              t2
Section One     SubOne,SubTwo
Section Three   SubThree

（如果您想要的结果Section Two，请HAVING t2 IS NOT NULL从查询中删除条件）

然后在您的PHP中（我假设mysqli有一个连接$conn）

$result = mysqli_query($conn, $sql) or die(mysqli_error($conn));
$out = array();
while ($row = mysqli_fetch_array($result)) {
   $out[] = array('t1' => $row['t1'], 't2' => explode(',', $row['t2']));
}
print_r($out);

输出：

Array
(
    [0] => Array
        (
            [t1] => Section One
            [t2] => Array
                (
                    [0] => SubOne
                    [1] => SubTwo
                )    
        )

    [1] => Array
        (
            [t1] => Section Three
            [t2] => Array
                (
                    [0] => SubThree
                )
        )
)

2021-06-07