Query output in mysql grouping

Question

I need help for a query grouped in mysql. Can anyone help me?

Tables

CREATE TABLE `tb_tree` (
  `idtree` int(255) NOT NULL,
  `idrec` varchar(255) NOT NULL,
  `idusrec` varchar(255) NOT NULL,
  `idreg` varchar(255) NOT NULL,
  `idleg` varchar(255) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

INSERT INTO `tb_tree` (`idtree`, `idrec`, `idusrec`, `idreg`, `idleg`) VALUES
(1, '2', '1', '2', 'D'),
(2, '2', '4', '2', 'D'),
(3, '2', '5', '5', 'D'),
(4, '2', '6', '2', 'D'),
(5, '1', '9', '2', ''),
(6, '1', '10', '2', '');

CREATE TABLE `tb_regioes` (
  `idreg` int(255) NOT NULL,
  `nregion` varchar(255) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

INSERT INTO `tb_regioes` (`idreg`, `nregion`) VALUES
(1, 'Sede'),
(2, 'Eldorado'),
(3, 'Riacho'),
(4, 'Industrial'),
(5, 'Ressaca'),
(6, 'Nacional'),
(7, 'Petrolândia'),
(8, 'Várzea das Flores');

Consultation:

$sqltree = "SELECT t.idtree,t.idrec,t.idusrec,t.idleg,t.idreg, r.nregion FROM tb_tree t 
INNER JOIN tb_regioes r on t.idreg = r.idreg 
WHERE idleg = 'D' GROUP BY t.idreg";
$query = $conn->query($sqltree);
$linhas = $query->num_rows;

while($rowtree = $query->fetch_assoc()) {
$idtree = $rowtree["idtree"];
$idrec = $rowtree["idrec"];
$idusrec = $rowtree["idusrec"];
$idreg = $rowtree["idreg"];
$idleg = $rowtree["idleg"];
$nregion = $rowtree["nregion"];
}

It’s coming out like this:

Eldorado
1
Ressaca
5

But I recite this exit:

Eldorado
1
4
6
Ressaca
5

Answer 1

Use the GROUP_CONCAT aggregation function.
Correcting field name in GROUP BY:

SELECT r.nregion, GROUP_CONCAT(t.idusrec SEPARATOR '-')
FROM tb_tree t 
INNER JOIN tb_regioes r on t.idreg = r.idreg 
WHERE idleg = 'D' 
GROUP BY r.nregion;

Exit:

nregion     GROUP_CONCAT(t.idusrec SEPARATOR '-')
Eldorado    1-4-6
Ressaca     5