recurse thru a category structure and count items in categories with perl and mysql

Loop thru the categories table (must have category_code AND category_parent),
and inside the loop call the subroutine. sub db_query is just a simple query wrapper.

view source

print?

01.# loop thru the categories and count all prods underneath

02.my $prodcount=0;

03.my $cat_loop_q = "SELECT category_code,category_parent FROM $categories";

04.my $sth = &db_query($cat_loop_q);

05.while (my $ref = $sth->fetchrow_arrayref) {

06. $prodcount = &countTree($$ref[0]);

07. my $update_q = "UPDATE $categories SET count = '$prodcount' WHERE category_code = '$$ref[0]'";

08. my $update = &db_query($update_q);

09. $prodcount=0;

10.}

11.sub countTree {

12. my ($root) = shift;

13. my $drill_q = "SELECT category_code FROM $categories WHERE category_parent = '$root'";

14. #print "$drill_q\n";

15. my $sth = &db_query($drill_q);

16. my $rc = $sth->rows;

17. if ($rc>0) {

18.  while (my $ref = $sth->fetchrow_arrayref) {

19.   &countTree($$ref[0]);

20.  }

21. }

22. my $sql_q = "SELECT

23.  a.category_name,

24.  a.category_code,

25.  a.category_parent,

26.  COUNT(DISTINCT b.skuid)

27.  FROM $categories AS a, $products_to_categories AS b

28.  WHERE a.category_code=b.category_code

29.  AND a.category_code='$root'

30.  GROUP BY a.id";

31.   

32. my $sth = &db_query($sql_q);

33. my ($name,$parent,$code,$count);

34. while (my $ref = $sth->fetchrow_arrayref) {

35.  $name=$$ref[0];

36.  $code=$$ref[1];

37.  $parent=$$ref[2];

38.  $count=$$ref[3];

39.  $prodcount=$prodcount+$count;

40.   

41. }

42. return($prodcount);

43.  

44.}