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’ statement3. 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); } } } } } } } }
In reply to Re^4: mysql DBI Nested Queries
by finhagen
in thread mysql DBI Nested Queries
by finhagen
| For: | Use: | ||
| & | & | ||
| < | < | ||
| > | > | ||
| [ | [ | ||
| ] | ] |