PHP SQL连接查询在多数组中合并内容 [英] PHP SQL Join Query merge content in Multi-Array

问题描述

我在使用SQL查询时遇到了问题，我想继续使用PHP. 这是我的数据库的结构:

i've got a problem with a SQL-Query with i would like to proceed with PHP. Here is the structure of my Database:

表格:部分

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

表格:小节

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

各节通过第二个表(sub_sections)中的"sId"链接. 因此，标题为"SubOne"的小节是ID为1的小节(来自小节表)的子小节.

The sections are linked through the "sId" in the second table (sub_sections). So subsection with the titel "SubOne" is the child-sub from the section(from sections table) with the id 1.

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

I'm using this Query to get the results:

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"的多个结果.我需要这样的结果:

Soo the problem is, that i get multiple results of the table "sections". I need an result like these:

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

                )

        )

)

有没有办法做到这一点?在此先谢谢了！

Is there any way to do this? Many many thanks in advance!

谢谢， J. Doe;)

Thanks, J. Doe ;)

推荐答案

您可以结合使用PHP和MySQL.将查询更改为此:

You could do this with a combination of PHP and MySQL. Change your query to this:

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

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

This will give you a result table like this:

t1              t2
Section One     SubOne,SubTwo
Section Three   SubThree

(如果要获取Section Two的结果，请从查询中删除HAVING t2 IS NOT NULL条件)

(If you want a result for Section Two, remove the HAVING t2 IS NOT NULL condition from the query)

然后在您的PHP中(我假设mysqli具有连接$conn)

Then in your PHP (I'm assuming mysqli with a connection $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
                )
        )
)

这篇关于PHP SQL连接查询在多数组中合并内容的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！