PHPize Online / SQLize Online  /  SQLtest Online

<?php // get categoriies into array >>> $sth = $pdo->prepare("select * from prod_category"); $sth->execute(); $result = $sth->fetchAll(PDO::FETCH_ASSOC); $categories = []; foreach ($result as $c) { $categories[$c['id']] = $c; }; // <<< get categoriies into array // recursive function get product main category function get_main_category($cat) { global $categories; return $categories[$cat]['parent_category'] == 0 ? $categories[$cat]['name'] : get_main_category($categories[$cat]['parent_category']); } $sth = $pdo->prepare("select * from products"); $sth->execute(); $result = []; while ($prod = $sth->fetch(PDO::FETCH_ASSOC)) { $prod['main_category'] = get_main_category($prod['category_id']); if (isset($res[$prod['main_category']])) $res[$prod['main_category']]++; else $res[$prod['main_category']] = 1; } print_r($res);