PHP SQL Join Query merge content in Multi-Array

Question

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

Table: sections

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

Table: sub_sections

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

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; 

My Output looks like this:

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

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

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!

Thanks, J. Doe ;)

Answer 1

You have the array with the data you need. If you only want to present it in a different way, you can iterate through it and generate the one you want. I'm using $arrayInput with the same structure you provided.

$arrayOutput = array();
foreach ($arrayInput as $itemInput) {
    $arrayOutput[$itemInput['t1']]['t1'] = $itemInput['t1'];
    $arrayOutput[$itemInput['t1']]['t2'][] = $itemInput['t2'];
}

echo "<pre>";
print_r($arrayOutput);

Answer 2

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

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

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);

Output:

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

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