nix, shell, perl, php, mysql and mac os x tips and tricks

Saturday, October 25, 2003

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.
# loop thru the categories and count all prods underneath
my $prodcount=0;
my $cat_loop_q = "SELECT category_code,category_parent FROM $categories";
my $sth = &db_query($cat_loop_q);
while (my $ref = $sth->fetchrow_arrayref) {
 $prodcount = &countTree($$ref[0]);
 my $update_q = "UPDATE $categories SET count = '$prodcount' WHERE category_code = '$$ref[0]'";
 my $update = &db_query($update_q);
 $prodcount=0;
}
sub countTree {
 my ($root) = shift; 
 my $drill_q = "SELECT category_code FROM $categories WHERE category_parent = '$root'";
 #print "$drill_q\n";
 my $sth = &db_query($drill_q);
 my $rc = $sth->rows;
 if ($rc>0) {
  while (my $ref = $sth->fetchrow_arrayref) {
   &countTree($$ref[0]);
  }
 } 
 my $sql_q = "SELECT 
  a.category_name,
  a.category_code,
  a.category_parent,
  COUNT(DISTINCT b.skuid) 
  FROM $categories AS a, $products_to_categories AS b 
  WHERE a.category_code=b.category_code 
  AND a.category_code='$root'
  GROUP BY a.id";
  
 my $sth = &db_query($sql_q);
 my ($name,$parent,$code,$count);
 while (my $ref = $sth->fetchrow_arrayref) {
  $name=$$ref[0];
  $code=$$ref[1];
  $parent=$$ref[2];
  $count=$$ref[3];
  $prodcount=$prodcount+$count;
  
 }
 return($prodcount);
 
}

No comments:

Post a Comment