Re^4: mysql DBI Nested Queries

[finhagen]

I have spent several hours researching and experimenting (well groping) with this issue to no avail so I am going to ask for help (again).

The overall program logic:

1. Input $employeeID

2. Query mysql database for employee’s fname, lname, title, rank and supervisorID based on WHERE eid =’$employeeID’ statement

3. Print employee’s first name, last name, title and ranking

4. Return employee’s direct supervisorID as $next_level_supervisor

5. Call next_level_supervisor sub routine

The next_level_supervisor sub routine logic:

WHILE $next_level_supervisor ne ‘CEO’ {

1. Input $next_level_supervisor as argument to sub routine

2. Query database for $next_level_supervisor’s fname, lname, title, rank and (his/her) next_level_supervisor2 based on WHERE supervisor =’$next_level_supervisor’ statement

3. Print $next_level_supervisor first name, last name, title and ranking

4. Return next_level_supervisor2

}

Input next_level_supervisor2 as $next_level_supervisor to next_level_supervisor sub routine (what could go wrong? ;-)

The key challenge is the conversion of the $next_level_supervisor variable. When I approach the problem iteratively, I just change the variable name each time so the variable returned is $supervisor2, $supervisor3…. until there are no additional supervisors. (Although I can add a WHILE $supervisorX ne ‘CEO’ the program seems to close properly even though I iterate through 10 supervisors without that WHILE condition).

How to I differentiate the input $next_level_supervisor from the output $next_level_supervisor? Somehow I have to successfully convert the $next_level_supervisor2 value into the $next_level_supervisor as input to the sub routine. I’m stumped.

Here is the revised iterative code that works:

use strict;
use warnings;
use DBI;
use DBD::mysql;
use Spreadsheet::WriteExcel;

our $lname;
our $fname;
our $title;
our $rank;
our $eid;
our $employee ='employeeID';
our $supervisor;
our $supervisor2;
our $supervisor3;
our $supervisor4;
our $supervisor5;
our $supervisor6;
our $supervisor7;
our $supervisor8;
our $supervisor9;
my $row=23;

sub add_aref_to_sheet{
        my ($aref, $worksheet0,) =@_;
        
my ($fname, $lname, $title, $rank, $supervisor) = @$aref;


                #write data to screen & spreadsheet row by row
                
                #print captured output
                print "$fname\n";
                print "$lname\n";
                print "$title\n";
                print "$rank\n";
                print "\n";
                print "=====================\n";
                print "\n";

                    $worksheet0->write($row++, 2, $fname, $format_top2
+);
                    $worksheet0->write($row++, 2, $lname, $format_midd
+le);
                    $worksheet0->write($row++, 2, $title, $format_midd
+le);
                    $worksheet0->write($row++, 2, $rank, $format_botto
+m);
                    $worksheet0->write($row++, 2, $supervisor, $format
+_bottom);
                    $row++;
            
            return $supervisor;
}



#select fname, lname, csg from acnse where supervisor ='supervisorID';
my $sth = $dbh->prepare("SELECT fname, lname, title, rank, supervisor 
+FROM acnse WHERE eid ='$employee';");
$sth->execute or die $sth->errstr;


        while ( my $aref = $sth ->fetchrow_arrayref)

            {$supervisor2 = add_aref_to_sheet($aref, $worksheet0);

#select fname, lname, csg from acnse where supervisor ='supervisorID';
my $sth = $dbh->prepare("SELECT fname, lname, title, rank, supervisor 
+FROM acnse WHERE eid ='$supervisor2';");
$sth->execute or die $sth->errstr;


        while ( my $aref = $sth ->fetchrow_arrayref)

            {$supervisor3 = add_aref_to_sheet($aref, $worksheet0);

#select fname, lname, csg from acnse where supervisor ='supervisorID';
my $sth = $dbh->prepare("SELECT fname, lname, title, rank, supervisor 
+FROM acnse WHERE eid ='$supervisor3';");
$sth->execute or die $sth->errstr;


        while ( my $aref = $sth ->fetchrow_arrayref)

            {$supervisor4 = add_aref_to_sheet($aref, $worksheet0);

#select fname, lname, csg from acnse where supervisor ='supervisorID';
my $sth = $dbh->prepare("SELECT fname, lname, title, rank, supervisor 
+FROM acnse WHERE eid ='$supervisor4';");
$sth->execute or die $sth->errstr;


        while ( my $aref = $sth ->fetchrow_arrayref)

            {$supervisor5 = add_aref_to_sheet($aref, $worksheet0);

#select fname, lname, csg from acnse where supervisor ='supervisorID';
my $sth = $dbh->prepare("SELECT fname, lname, title, rank, supervisor 
+FROM acnse WHERE eid ='$supervisor5';");
$sth->execute or die $sth->errstr;


        while ( my $aref = $sth ->fetchrow_arrayref)

            {$supervisor6 = add_aref_to_sheet($aref, $worksheet0);
                
#select fname, lname, csg from acnse where supervisor ='supervisorID';
my $sth = $dbh->prepare("SELECT fname, lname, title, rank, supervisor 
+FROM acnse WHERE eid ='$supervisor6';");
$sth->execute or die $sth->errstr;


        while ( my $aref = $sth ->fetchrow_arrayref)

            {$supervisor7 = add_aref_to_sheet($aref, $worksheet0);
                
#select fname, lname, csg from acnse where supervisor ='supervisorID';
my $sth = $dbh->prepare("SELECT fname, lname, title, rank, supervisor 
+FROM acnse WHERE eid ='$supervisor7';");
$sth->execute or die $sth->errstr;


        while ( my $aref = $sth ->fetchrow_arrayref)

            {$supervisor8 = add_aref_to_sheet($aref, $worksheet0);
                
#select fname, lname, csg from acnse where supervisor ='supervisorID';
my $sth = $dbh->prepare("SELECT fname, lname, title, rank, supervisor 
+FROM acnse WHERE eid ='$supervisor8';");
$sth->execute or die $sth->errstr;


        while ( my $aref = $sth ->fetchrow_arrayref)

            {$supervisor9 = add_aref_to_sheet($aref, $worksheet0);
                
            }
        }
    }
}
}
}
}
}

[roboticus]

finhagen:

As part of the thinking process, we make assumptions to make the problem simpler. Computers, however, can't make assumptions, so we can get into trouble when translating our solution to a programming language. So, when we program, we have to learn how to be aware of the assumptions we're making, so we can question them. It's pretty difficult, and after some 35 years, I'm still tripping over my own assumptions.

That's just a long-winded way of getting to your question. The assumptions you're tripping over at the moment are in these two statements:

The key challenge is the conversion of the $next_level_supervisor variable. When I approach the problem iteratively, I just change the variable name each time so the variable returned is $supervisor2, $supervisor3…. until there are no additional supervisors. (Although I can add a WHILE $supervisorX ne ‘CEO’ the program seems to close properly even though I iterate through 10 supervisors without that WHILE condition).

How to I differentiate the input $next_level_supervisor from the output $next_level_supervisor? Somehow I have to successfully convert the $next_level_supervisor2 value into the $next_level_supervisor as input to the sub routine. I’m stumped.

As I see it there are two assumptions you're making here that are causing you grief: (1) That one supervisor is somehow different from another, and (2) that a supervisor isn't exactly an employee.

In your problem, when you're given an employee, you want to add the employee to the spreadsheet, and then add their supervisor to the spreadsheet, and then their supervisor to the spreadsheet. Since you're treating different supervisors differently from each other, and not taking advantage that a supervisor is also an employee, you're not seeing the simplification.

So, what if one supervisor is the same as any other supervisor? Then you don't really need a different variable for it.

Let's take a different but similar problem: Let's factor a number. The method you're currently using is similar to this:

my ($f1, $f2, $f3, $f4, $f5);
my $c = <>; # get number
($f1,$c) = get_a_factor($c);
print "$f1, ";
($f2,$c) = get_a_factor($c);
print "$f2, ";
($f3,$c) = get_a_factor($c);
print "$f3, ";
($f4,$c) = get_a_factor($c);
print "$f4, ";
($f5,$c) = get_a_factor($c);
print "$f5\n";

sub get_a_factor {
   my $num = shift;
   ... compute factor ...
   return ($factor, $num/$factor);
}

The problems here are (1) that we're treating each factor as a different type of thing, and (2) we have to know how many factors we can get.

But once we print the factor, nothing in our code really cares about it any longer. We simply want the list of remaining factors. So we can simplify it like so:

my $f1;
my $c = <>; # get number

# Get first factor
($f1,$c) = get_a_factor($c);
print $f1;

# Get rest of 'em
while ($c > 1) {
   ($f1,$c) = get_a_factor($c);
   print ", $f1";
}

print "\n";

sub get_a_factor {
   my $num = shift;
   ... compute factor ...
   return ($factor, $num/$factor);
}

As you can see, we no longer have to worry how many factors there may ultimately be, as we can simply get all of them, until the loop terminates.

However, we are still treating the first factor as being somehow a different thing than the others. Since the supervisor number is just another employee ID, you could combine the loop with the first step if you rearrange a little more:

my $f1;
my $c = <>; # get number

# Get factor and the new number to test
while ($c > 1) {
   ($f1,$c) = get_a_factor($c);
   print ", $f1";
}

print "\n";

sub get_a_factor {
   my $num = shift;
   ... compute factor ...
   return ($factor, $num/$factor);
}

Does this give you the hints you need? If not let me know and I'll give you some more.

...roboticus

When your only tool is a hammer, all problems look like your thumb.