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

Yes, just transform the single dimensional array into a multi dimensional one — Strawberry
– Strawberry, Commented Aug 13, 2018 at 6:57
Do you want any output for Section 2, which has no subsections? — Nick
– Nick, Commented Aug 13, 2018 at 6:57
If you are asking if the query would output an array like that, the answer is: No. But you can easily iterate through this results and create the array you need. I'm gonna write an example. — Rafael
– Rafael, Commented Aug 13, 2018 at 7:00
I dont think i can only transform this output, because there is actually no link-logic in these datas. And i want a output for section two just with an empty array "t2". Thanks for helping guys. — J. Doe
– J. Doe, Commented Aug 13, 2018 at 7:02
Doesn't the title work as the link-logic? Well, I'm upvoting your question because that's a good way to display table data. :) — Rafael
– Rafael, Commented Aug 13, 2018 at 7:10

Nick · Accepted Answer · 2018-08-13 07:34:13Z

3

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

edited Aug 13, 2018 at 7:34

answered Aug 13, 2018 at 7:05

Nick

147k23 gold badges67 silver badges106 bronze badges

Sign up to request clarification or add additional context in comments.

7 Comments

Rafael Over a year ago

I edited my answer because it seemed like I was exactly talking about your answer. Not what I meant, sorry. :)

Nick Over a year ago

@Rafael no problem at all I think we both have good but different answers.

J. Doe Over a year ago

Thanks for your help guys. I did it with this :) But ive got another small problem with this. I've got some more columns in this table that saves the display order of both tables. In the "sections" table its sorder and in the sub_sections table its "iorder". If i try to show the sorder column it shows me 0 every time. I edited my Query like these: SELECT section_titel as t1, group_concat(sub_section_titel) as t2, tb1.sorder as sorder FROM sections tb1 LEFT JOIN sub_sections tb2 ON tb1.section_id=tb2.sId

Nick Over a year ago

Since this question is solved, you could post another about ordering the data?

Rafael Over a year ago

If you changed the code using this answer, man, you should choose this answer for the question. :)

|

Rafael · Accepted Answer · 2018-08-13 07:17:17Z

2

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

edited Aug 13, 2018 at 7:17

answered Aug 13, 2018 at 7:07

Rafael

1,5361 gold badge15 silver badges26 bronze badges

2 Comments

Nick Over a year ago

Strictly speaking, this code doesn't give the output OP wanted as the top level array keys are Section One, Section Two etc. instead of 0, 1 etc.

Rafael Over a year ago

Most often, the keys of arrays that are numbers are used for iteration purposes rather than indexes, adopting the index as the title is merely using them for transforming into the output data he desired with the less amount of code as possible. I consider the main array key a technicality.

Collectives™ on Stack Overflow

PHP SQL Join Query merge content in Multi-Array

2 Answers 2

7 Comments

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

7 Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related