Leaderboard

You can simplify things by always ensuring categories have at least one subcategory, and always linking images to the subcategories then you have so that mysql> SELECT c1.cat_name -> , count(i.id) as total -> FROM prefix_channels c1 -> JOIN prefix_channels c2 ON c2.child_of = c1.cat_id -> JOIN prefix_images i ON i.category = c2.cat_id -> GROUP BY c1.cat_name; +-------------------+-------+ | cat_name | total | +-------------------+-------+ | parent_category_1 | 4 | | parent_category_2 | 2 | | parent_category_3 | 1 | +-------------------+-------+

This calls out for a recursive solution $res = $pdo->query("SELECT parent , id , name , title FROM main "); $emps = $res->fetchAll(PDO::FETCH_GROUP); // results array grouped by parent getChildren($emps[0], $emps); $chart = $emps[0][0]; // view result echo '<pre>' . print_r($chart, 1) . '</pre>'; /** * recursive function to build hierarchical array * * @param array $kids array of current children * @param array $emps orig array of all employees */ function getChildren(&$kids, &$emps) { foreach ($kids as $k => &$emp) { $children = $emps[$emp['id']] ?? []; getChildren($children, $emps); $emp['children'] = $children; } } ?> <!-- DATA ---------------------------------------------------------------------- CREATE TABLE `main` ( `id` int(11) NOT NULL, `name` varchar(45) DEFAULT NULL, `title` varchar(45) DEFAULT NULL, `parent` int(11) DEFAULT NULL, PRIMARY KEY (`id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8; '1', 'happy', 'Sales Manager', '5' '2', 'comet', 'Sales Rep', '1' '3', 'grumpy', 'Accountant', '5' '4', 'prancer', 'Secretary', '6' '5', 'bashful', 'General Manager', '0' '6', 'dancer', 'Accounts clerk', '3' '7', 'doc', 'Operations Manager', '5' '8', 'blitzen', 'HR Clerk', '10' '9', 'dasher', 'Technician', '7' '10', 'rudolph', 'Personal Assistant', '5' '11', 'vixen', 'Sales Rep', '1' '12', 'cupid', 'Sales Assistant', '2' '13', 'donner', 'Secretarial Assistant', '4' -------------------------------------------------------------------------------> Results Array ( [id] => 5 [name] => bashful [title] => General Manager [children] => Array ( [0] => Array ( [id] => 1 [name] => happy [title] => Sales Manager [children] => Array ( [0] => Array ( [id] => 2 [name] => comet [title] => Sales Rep [children] => Array ( [0] => Array ( [id] => 12 [name] => cupid [title] => Sales Assistant [children] => Array ( ) ) ) ) [1] => Array ( [id] => 11 [name] => vixen [title] => Sales Rep [children] => Array ( ) ) ) ) [1] => Array ( [id] => 3 [name] => grumpy [title] => Accountant [children] => Array ( [0] => Array ( [id] => 6 [name] => dancer [title] => Accounts clerk [children] => Array ( [0] => Array ( [id] => 4 [name] => prancer [title] => Secretary [children] => Array ( [0] => Array ( [id] => 13 [name] => donner [title] => Secretarial Assistant [children] => Array ( ) ) ) ) ) ) ) ) [2] => Array ( [id] => 7 [name] => doc [title] => Operations Manager [children] => Array ( [0] => Array ( [id] => 9 [name] => dasher [title] => Technician [children] => Array ( ) ) ) ) [3] => Array ( [id] => 10 [name] => rudolph [title] => Personal Assistant [children] => Array ( [0] => Array ( [id] => 8 [name] => blitzen [title] => HR Clerk [children] => Array ( ) ) ) ) ) )

OK - I've added the sort usort($test, fn($a, $b) => $b['itemCount']<=>$a['itemCount']); // sort descending itemCount $seen = []; foreach ($test as $k => &$rec) { $rec['rolanID'] = array_diff($rec['rolanID'], $seen); // find new ids if ($rec['rolanID']) { // if there are some new ones ... $rec['itemCount'] = count($rec['rolanID']); // count them $seen = array_merge($seen, $rec['rolanID']); // add the new ones to those already seen } else unset($test[$k]); // if no ids, remove the array item } and I now get this (no duplicate 123)... Array ( [0] => Array ( [supplier] => TEST2 DEPO [rolanID] => Array ( [0] => 456 [1] => 188 [2] => 200 [3] => 123 ) [itemCount] => 4 ) [1] => Array ( [supplier] => TEST DEPO [rolanID] => Array ( [1] => 234 ) [itemCount] => 1 ) [2] => Array ( [supplier] => DIFFERENT DEPO [rolanID] => Array ( [0] => 897 [1] => 487 [2] => 100 ) [itemCount] => 3 ) )