finhagen has asked for the wisdom of the Perl Monks concerning the following question:
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; }
I hope that explanation is clear enough to illicit the wise advice of the Perl Monks. Thank you!
|
---|
Replies are listed 'Best First'. | |
---|---|
Re: mysql DBI Nested Queries
by atcroft (Abbot) on Dec 23, 2012 at 05:49 UTC | |
Re: mysql DBI Nested Queries
by CountZero (Bishop) on Dec 23, 2012 at 14:00 UTC | |
Re: mysql DBI Nested Queries
by roboticus (Chancellor) on Dec 23, 2012 at 14:09 UTC | |
by finhagen (Sexton) on Dec 23, 2012 at 22:40 UTC | |
by roboticus (Chancellor) on Dec 23, 2012 at 23:55 UTC | |
by finhagen (Sexton) on Dec 26, 2012 at 23:34 UTC | |
by finhagen (Sexton) on Dec 28, 2012 at 14:47 UTC | |
by roboticus (Chancellor) on Dec 28, 2012 at 15:50 UTC | |
Re: mysql DBI Nested Queries
by Anonymous Monk on Dec 24, 2012 at 15:53 UTC |