php counting totals for category and subcategory

[richrock]

Hi all,

 

I've got a category table in mysql with categories and subcategories.

Typeidparent

category10

subcategory21

From the table, you can see that a main category will always have a parent of 0.  Sub-categories will have a parent relative to the id of whichever parent category they belong to.

 

I'm trying to build a counter for items in each of these.  The items are stored in a separate table, and their category is defined by the id number in the table above.

 

I need to do two things:

 

1: count all items that belong to a parent category.

2: count items per category (main or sub).

 

Here's a sample of a csv export:

 

id > catname > parent

1 Beds 0

2 Beds 1

3 Four Poster Beds 1

4 Headboards 1

5 Bed Frames 1

6 Folding Beds 1

7 Sofa Beds 1

8 Cots 1

9 Wall Accessories 0

10 Picture Frames 9

11 Mirrors 9

12 Curtains 9

13 Blinds 9

14 Net Curtains 9

15 Wall Art 9

16 Bathroom Accessories 9

17 Tiles 9

18 Flooring 0

19 Carpets 18

20 Rugs 18

21 Laminate 18

22 Lino 18

23 Ceramic Tiles 18

24 Carpet Tiles 18

25 Mats 18

 

So if I have ten items that belong to id# 22, 3 items in #19, the total for that section (parent id #18) should be 13.  I can count the items per category, as you can see in the code below.  I can't figure out for the life of me how to count a total INCLUDING the original parent category.

 

Here's the code for the per category:

 

$database->setQuery("select * from #__categories where parent='0' order by ordering,catname");
$parentcats = $database->loadObjectList();
$nr_pcats = count($parentcats);
$html_tree ='<div id="Tree" align="left" class="Tree" style="padding-top:'.$padding_top.'px;padding-bottom:'.$padding_bottom.'px">';
$j=1;
for($i=0;$i<$nr_pcats;$i++){
    // TODO add counters here.
    $database->setQuery("select * from #__categories where parent='".$parentcats[$i]->id."'");
    $allcats = $database->loadObjectList();
    
    foreach($allcats as $row){

        if($row->parent == $parentcats[$i]->id) {

            $catid = $row->id;
            $database->setQuery("select * from #__items where cat = '".$catid."'");
            $catids = $database->loadObjectList();
            $totalpercat = count($catids);
            $finaltotal += $totalpercat;
            echo $totalpercat."<br />";


            }


            //echo $finaltotal."<br />";

        }

 

And this will spew out a list of numbers per category and subcategory of how many items.  But how would I do a total based on each parent?

 

TIA

 

Rich