How to show related table data in an organized way by topics

Question

How to show related table data in an organized way by topics

Navigation

#1 by (1 votes)

2

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

Title 1 of parent table: Data 1 of daughter table related to id 1 of   parent table Given 2 of the daughter table related to the id 1 of the parent table   Daughter table data 3 related to parent table id 1

Title 2 of the parent table: Given 1 of the child table related to id 2 of   parent table Given parent table 2 of parent table id 2

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

Title 1 of the parent table: Given 1 of the child table related to id 1 of   parent table

Title 1 of the parent table: Given 2 of the child table related to id 1 of   parent table

Title 1 of parent table: Data 3 of daughter table related to id 1 of   parent table

Title 2 of the parent table: Given 1 of the child table related to id 2 of   parent table

Title 2 of the parent table: Given 1 of the child table related to id 2 of   parent table

The select looks something like this:

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

mysql sql

asked by anonymous 08.01.2015 / 18:37

1 answer

Help with Stack in PHP Catch the attributes of a Thread object?

score 1 · Answer 1

As it seems to me, you're looking for the data in the right way, but you're using a single loop to loop through it. You need an external loop to loop through the data and internal loops to move the data from the child table to a certain stop condition.

In this example the stop condition is the id (or any other data you want, such as 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;
    }
}

Output:

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, you must sort the results first by the id of the parent table, and then by the child table ids:

ORDER BY pai.id, filha.id