I have a mysql employee database which I access via the mysql DBI module and push into the Spreadsheet:WriteExcel module to a spreadsheet for reporting purposes. That (remarkably) all works fine.

Each employee record has a employee ID (eid) supervisor (supervisor) column (among other columns in the database). I can perform a simple SELECT statement (SELECT eid, supervisor from employeedb WHERE eid = 'xxxxxx') and extract that employee's supervisor to a variable $supervisor.

What I need is a WHILE loop wherein I enter an employee ID and walk that employee's reporting chain backwards or up the chain. In my mind that means capturing the supervisor data in a variable and inserting that into another SELECT statement (SELECT eid, supervisor from employeeID WHERE supervisor = '$supervisor';) and iterating up the reporting chain until I arrive at the CEO.

My problem (among many ;-) is I don't know how to pass the $supervisor variable from SELECT statement to SELECT statement given it changes for each iteration and the variable scope has to pass from one WHILE {} to the next WHILE {}.

Here's some code that doesn't work (I am pretty proficient writing code that doesn't work ;-): 
 
our $lname;
our $fname;
our $title;
our $eid;
our $supervisor;
our $supervisor2;
our $supervisor3;
        
;
my $sth = $dbh->prepare("SELECT fname, lname, title, supervisor FROM e
+mployeedb WHERE eid ='employee1';");
$sth->execute or die $sth->errstr;

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

            {
             my ($fname, $lname, $title, $supervisor) = @$aref;

                #write data to spreadsheet row by row 

                    $worksheet0->write(19, 26, $fname, $format_PRDmidd
+le);
                    $worksheet0->write(20, 26, $lname, $format_PRDmidd
+le);
                    $worksheet0->write(21, 26, $title, $format_PRDmidd
+le);
                    $worksheet0->write(22, 26, $supervisor, $format_PR
+Dbottom);
our $supervisor2 = $supervisor;    
            }


my $sth = $dbh->prepare("SELECT fname, lname, title, supervisor FROM e
+mployeedb WHERE supervisor ='$supervisor2';");
$sth->execute or die $sth->errstr;

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

            {
             my ($fname, $lname, $title, $supervisor2) = @$aref;

                #write data to spreadsheet row by row 

                    $worksheet0->write(23, 11, $fname, $format_AMCtop2
+);
                    $worksheet0->write(24, 11, $lname, $format_AMCmidd
+le);
                    $worksheet0->write(25, 11, $title, $format_AMCmidd
+le);
                    $worksheet0->write(26, 11, $supervisor2, $format_A
+MCbottom);
our $supervisor3 = $supervisor2;
            }

[download]

I hope that explanation is clear enough to illicit the wise advice of the Perl Monks. Thank you!

Hagen Finley Boulder, CO

In reply to mysql DBI Nested Queries by finhagen

Title:
Use:  <p> text here (a paragraph) </p>
and:  <code> code here </code>
to format your post, it's "PerlMonks-approved HTML":


  • Posts are HTML formatted. Put <p> </p> tags around your paragraphs. Put <code> </code> tags around your code and data!
  • Titles consisting of a single word are discouraged, and in most cases are disallowed outright.
  • Read Where should I post X? if you're not absolutely sure you're posting in the right place.
  • Please read these before you post! —
  • Posts may use any of the Perl Monks Approved HTML tags:
    a, abbr, b, big, blockquote, br, caption, center, col, colgroup, dd, del, details, div, dl, dt, em, font, h1, h2, h3, h4, h5, h6, hr, i, ins, li, ol, p, pre, readmore, small, span, spoiler, strike, strong, sub, summary, sup, table, tbody, td, tfoot, th, thead, tr, tt, u, ul, wbr
  • You may need to use entities for some characters, as follows. (Exception: Within code tags, you can put the characters literally.)
            For:     Use:
    & &amp;
    < &lt;
    > &gt;
    [ &#91;
    ] &#93;
  • Link using PerlMonks shortcuts! What shortcuts can I use for linking?
  • See Writeup Formatting Tips and other pages linked from there for more info.