## RUN THIS FIRST
CREATE TABLE `networks_groups` (
`group_id` int NOT NULL AUTO_INCREMENT,
`parent_id` int DEFAULT NULL,
`name` varchar(255) NOT NULL,
PRIMARY KEY (`group_id`),
UNIQUE KEY `ng_unique_group_name` (`name`),
KEY `ng_parent_group_id` (`parent_id`),
CONSTRAINT `ng_parent_group_id_fk` FOREIGN KEY (`parent_id`) REFERENCES `networks_groups` (`group_id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB;
INSERT INTO `networks_groups` (`group_id`, `parent_id`, `name`)
VALUES
(1, NULL, 'Saint-Gobain'),
(2, 1, 'Sekurit'),
(3, 2, 'Noyon'),
(4, 2, 'Aniche'),
(5, 1, 'Abbrasives'),
(6, 5, 'Kolow');
<?php
$query = "SELECT * FROM networks_groups;";
$stmt = $pdo->prepare($query);
$stmt->execute();
$rows = $stmt->fetchAll(PDO::FETCH_ASSOC);
printf("group_id\tparent_id\tgroup name\n");
foreach ($rows as $row) {
printf("%s\n", implode("\t\t", array_map(fn($column) => $column ?: 'null', $row)));
}
function buildTreeNative(array $nodes, $parentId = null)
{
return array_values(
array_reduce(
array_filter(
$nodes,
fn($node) => $node['parent_id'] === $parentId
),
function (array $carry, array $node) use ($nodes) {
$node['children'] = buildTreeNative($nodes, $node['group_id']);
$carry[] = $node;
return $carry;
},
[]
)
);
}
$tree = buildTreeNative($rows);
function printTree(array $nodes, string $prefix = '', bool $isRoot = true): void
{
foreach ($nodes as $index => $node) {
if (isset($node['group_id']) && isset($node['name'])) {
$isLastNode = ($index === array_key_last($nodes));
printf("%s%s%s\n", $prefix, ($isRoot ? '' : ($isLastNode ? '└── ' : '├── ')), $node['name']);
if (isset($node['children']) && !empty($node['children'])) {
printTree($node['children'], $prefix . ($isLastNode ? ' ' : '│ '), false);
}
} elseif (!empty($node['children'])) {
printTree($node['children'], $prefix, $isRoot);
}
}
}
printf("\n\nGroup hierarchy:\n");
printTree($tree);