How to display related table data organized by topics

Question

I have a question in SQL that is the following: I have two tables, the parent table, and the daughter table, where the daughter table receives the id of the parent table as a foreign key, however, I don’t know how to show the entered data in an organized way. I need the data to appear on the screen as follows:

Parent table title 1: Daughter table given 1 related to id 1 of Father table Given 2 of the daughter table related to father table id 1 Child table data 3 related to parent table id 1

Father table title 2: Daughter table data 1 related to id 2 of Father table Data 2 of daughter table related to father table id 2

And so on, however, my data is showing up on the screen as follows on the screen:

Father table title 1: Daughter table data 1 related to id 1 of parent table

Father table title 1: Daughter table data 2 related to id 1 of parent table

Father table title 1: Daughter table data 3 related to id 1 of parent table

Father table title 2: Daughter table data 1 related to id 2 of parent table

Father table title 2: Daughter table data 1 related to id 2 of parent table

select is more or less like this:

SELECT *
FROM pai
LEFT JOIN filha ON (pai.id_tabelaPai = filha.id_tabelaPai)

Answer 1

As far as I’m concerned, you’re searching the data the right way but you’re using a single loop to travel them. You need a loop external to traverse data and loops internal to go through data from the daughter table to a certain stop condition.

In this example the stop condition is the id (or any other data you want, for example the title) of the parent table:

<?php

$resultSet = [
    [ 'id_pai' => 1, 'id_filha' => 1 ],
    [ 'id_pai' => 1, 'id_filha' => 2 ],
    [ 'id_pai' => 1, 'id_filha' => 3 ],
    [ 'id_pai' => 2, 'id_filha' => 4 ],
    [ 'id_pai' => 2, 'id_filha' => 5 ]
];

if (sizeof($resultSet) > 0) {
    $idTabelaPai = $resultSet[0]['id_pai'];
    echo 'tabela pai   = ' . $idTabelaPai . PHP_EOL;
    echo 'tabela filha = ' . $resultSet[0]['id_filha'] . PHP_EOL;
    for ($i=1, $count=sizeof($resultSet); $i<$count; $i++) {
        if ($resultSet[$i]['id_pai'] !== $idTabelaPai) {
            $idTabelaPai = $resultSet[$i]['id_pai'];
            echo 'tabela pai   = ' . $idTabelaPai . PHP_EOL;
        }
        echo 'tabela filha = ' . $resultSet[$i]['id_filha'] . PHP_EOL;
    }
}

Exit:

tabela pai   = 1
tabela filha = 1
tabela filha = 2
tabela filha = 3
tabela pai   = 2
tabela filha = 4
tabela filha = 5

For the above solution to work, it is necessary to sort the results first by the parent table id, and then by the daughter table ids:

ORDER BY pai.id, filha.id