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

Thursday, May 16, 2013

This perl code recurses down thru a category hierarchy and indents. You pass into it the category you want to drill into
# call the sub
&drillTree($code,1);
sub drillTree {
 my ($root,$level) = @_;
 my $tab = "";
 for (my $i=1;$i<=$level;$i++) {
  $tab .= "\t";
 }
 my $drill_q = "SELECT category_code,category_name FROM $categories WHERE category_parent = '$root'";
 my $sth = &db_query($drill_q);
 my $rc = $sth->rows;
 if ($rc>0) {
  $level++;
  while (my $ref = $sth->fetchrow_arrayref) {
   print "${tab}sub_code: $$ref[0] sub_name: $$ref[1]\n";
   &drillTree($$ref[0],$level);
  }
 } else {
  $level--;
 }
}
this one will print out the full path
# Launch the category drill-down sub
&drillTree($dbh,"100",0);
my @path = ();
$path[0] = "Products";
my @codes = ();
$codes[0] = "0";

sub drillTree {

 my ($dbh,$root,$level) = @_;
 my $tab = "";
 for (my $i=1;$i<=$level;$i++) {
  $tab .= "\t";
 }
 my $drill_q = "SELECT category_code,category_name FROM $categories WHERE category_parent = '$root'";
 my $sth = &db_query($dbh,$drill_q);
 my $rc = $sth->rows;
 if ($rc>0) {
  $level++;
  while (my $ref = $sth->fetchrow_arrayref) {
   $path[$level] = $$ref[1];
   $codes[$level] = $$ref[0];
   my $fullpath;
   my $fullcodes;
   for (my $i=1;$i<=$level;$i++) {
    $fullpath .= $path[$i];
    $fullcodes .= $codes[$i];
    if ($i<$level) {
     $fullpath .= "|";
     $fullcodes .= ";";
    }
   }
   $fullpath =~ s/®//g;
   $fullpath =~ s/é/e/g;
   print "level: $level ${tab}sub_code: $$ref[0] sub_name: $$ref[1] $fullpath $fullcodes\n";
   
   &drillTree($dbh,$$ref[0],$level);
  }
 } else {
  $level--;
 }
} # end drillTree sub