Display only such root level categories that either have any products or their subcategories have any products

user_10677300 0
Viewed 118

Please tell me, there are 2 tables:

1 - table of categories, and links (cat2 and cat3 are subject to cat1)

prod_category
id|name|parent_category|
1 |cat1|       0       |
2 |cat2|       1       |
3 |cat3|       1       |
4 |cat4|       3       |
5 |cat5|       3       |
6 |cat6|       0       |
7 |cat7|       6       |
8 |cat8|       0       |
9 |cat9|       8       |
10|cat10|      9       |

2 - table of products and their categories

products
id| name|category_id|
1 |prod1|     0     |
2 |prod2|     1     |
3 |prod3|     2     |
4 |prod4|     3     |
5 |prod5|     4     |
6 |prod6|     5     |
7 |prod7|     6     |
8 |prod8|     7     |
9 |prdo9|     8     |
10|prod10|    9     |

I am doing sql parent category loop:

$stmt = $pdo->query("SELECT * FROM prod_category WHERE parent_category='0'");
while ($row = $stmt->fetch()) {
if ($count_products_in_category > 0) {
echo $row['name'];
}
else {}
}

Next, I need to calculate whether this category is fixed in the products (whether there are products in it). If not, then take the category that is subordinate (child) and count whether there are products in it, and until the end of the relationship. If there are products in any category (child or in the parent itself), then display $row['name'], if not, then do not display.

pdo mysql php
1 Answers

Three steps

SQL

Get all categories with count of products

select c.id, c.parent, c.name, count(p.id) cnt from categories c 
left join products p on p.cat_id=c.id 
group by c.id

PDO

use that special PDO ability to return the array indexed by the first field, id in our case

$data = $pdo->query($sql)->fetchAll(PDO::FETCH_UNIQUE);

PHP

Now for each category, go up to the root, the same way I showed you before

$roots = [];
foreach ($data as $cat) {
    $cnt = 0;
    do {
        $cnt += $cat['cnt'];
        $cat = $data[$cat['parent']];
    } while($cat['parent']);

    $roots[$cat['id']] = $cat;
    $roots[$cat['id']]['has_products'] ?? 0;
    $roots[$cat['id']]['has_products'] = max((bool)$cnt, $roots[$cat['id']]['has_products']);
}

it should give you an array with root categories, each having a flag, whether it has any products or not.

With a more versatile category structure implementation, such as Materialized path, it would be possible to get all the required data in one SQL query

user_2855870
Related