Beefy Boxes and Bandwidth Generously Provided by pair Networks Frank
Syntactic Confectionery Delight
 
PerlMonks  

Re: mysql DBI Nested Queries

by roboticus (Canon)
on Dec 23, 2012 at 14:09 UTC ( #1010099=note: print w/ replies, xml ) Need Help??


in reply to mysql DBI Nested Queries

finhagen:

If you're doing a report of employees by supervisor, or similar, another way to handle the project is to make the database do some of the work for you. For example, you might be able to make a query give you the information sorted conveniently, and then add a control break. Perhaps something like:

our $lname; my $sth = $dbh->prepare(<<EOSQL); SELECT sup.fname, sup.lname, sup.title, sup.supervisor, emp.fname, emp.lname, emp.title, emp.supervisor FROM employeedb sup JOIN employeedb emp ON ..... ORDER BY sup.fname, sup.lname, emp.fname, emp.lname EOSQL $sth->execute or die $sth->errstr; my $previous_supervisor = ''; while (my $aref = $sth->fetchrow_arrayref) { my ($sup_fname, $sup_lname, $sup_title, $emp_fname, $emp_lname, $emp_title) = @$aref; # control-break: if a new supervisor is found, skip a few lines and + add a header my $current_supervisor = "$sup_fname $sup_lname"; if ($current_supervisor ne $previous_supervisor) { $R += 3; $C = 0; $worksheet0->write($R, $C++, "SUPERVISOR: ", $format_HEADER); $worksheet0->write($R, $C++, $current_supervisor, $format_HEADER +); $R++; $C=0; $worksheet0->write($R, $C++, "First Name", $format_COL_HEADER); $worksheet0->write($R, $C++, "Last Name", $format_COL_HEADER); $worksheet0->write($R, $C++, "Title", $format_COL_HEADER); $worksheet0->write($R, $C++, "Supervisor", $format_COL_HEADER); } # Add employee to sheet $R++; $C=0; $worksheet0->write($R, $C++, $emp_fname, $format_NORMAL); $worksheet0->write($R, $C++, $emp_lname, $format_NORMAL); $worksheet0->write($R, $C++, $emp_title, $format_NORMAL); $worksheet0->write($R, $C++, $emp_supervisor, $format_NORMAL); }

(Yes, I know this doesn't match your task exactly, it's just an example you might be able to adapt.)

Your example shows nearly duplicated code, which indicates that you might be able to restructure your code to use a subroutine:

sub process_employee { my ($worksheet0, $employee, $format, $col, $row) = @_; my $sth = $dbh->prepare("SELECT fname, lname, title, supervisor " ."FROM employeedb WHERE eid = ?"); $sth->execute($employee) or die $sth->errstr; my ($fname, $lname, $title, $supervisor); while (my $aref = $sth ->fetchrow_arrayref) { ($fname, $lname, $title, $supervisor) = @$aref; $worksheet0->write($row++, $col, $fname, $format); $worksheet0->write($row++, $col, $lname, $format); $worksheet0->write($row++, $col, $title, $format); $worksheet0->write($row++, $col, $supervisor, $format); } return $supervisor } my $supervisor = process_employee($worksheet0, $employee1, $format_PRD +middle, 19, 26); if (defined $supervisor) { process_employee($worksheet0, $supervisor, $format_AMCtop, 23, 11) +; }

...roboticus

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


Comment on Re: mysql DBI Nested Queries
Select or Download Code
Re^2: mysql DBI Nested Queries
by finhagen (Sexton) on Dec 23, 2012 at 22:40 UTC

    I stumbled onto a reporting chain approach that works, but it's ugly. Looking at your sub process comment, I am wondering if you and the the Monks can help me turn the following code into a sub process?

    I replaced the CEO's supervisor field (which was blank) with "CEO" which should allow for a while ( $supervisor ne 'CEO') {}

    I tried a couple of approaches but they all ended up in an infinite loop.

    Here's the code that works:

    our $lname; our $fname; our $title; our $eid; our $supervisor; our $supervisor2; our $supervisor3; our $supervisor4; our $supervisor5; our $supervisor6; our $supervisor7; our $supervisor8; #select fname, lname, csg from acnse where supervisor ='pierre.nanterm +e'; my $sth = $dbh->prepare("SELECT fname, lname, title, supervisor FROM a +cnse WHERE eid ='employeID';"); $sth->execute or die $sth->errstr; my $row=23; while ( my $aref = $sth ->fetchrow_arrayref) { my ($fname, $lname, $title, $supervisor) = @$aref; #write data to spreadsheet row by row $worksheet0->write($row++, 2, $fname, $format_HRto +p2); $worksheet0->write($row++, 2, $lname, $format_HRmi +ddle); $worksheet0->write($row++, 2, $title, $format_HRmi +ddle); $worksheet0->write($row++, 2, $supervisor, $format +_HRbottom); $row++; $supervisor2 = $supervisor; my $sth = $dbh->prepare("SELECT fname, lname, title, super +visor FROM acnse WHERE eid ='$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($row++, 2, $fname, $format_HRto +p2); $worksheet0->write($row++, 2, $lname, $format_HRmi +ddle); $worksheet0->write($row++, 2, $title, $format_HRmi +ddle); $worksheet0->write($row++, 2, $supervisor2, $forma +t_HRbottom); $row++; $supervisor3 = $supervisor2; my $sth = $dbh->prepare("SELECT fname, lname, title, super +visor FROM acnse WHERE eid ='$supervisor3';"); $sth->execute or die $sth->errstr; while ( my $aref = $sth ->fetchrow_arrayref) { my ($fname, $lname, $title, $supervisor3) = @$aref; #write data to spreadsheet row by row $worksheet0->write($row++, 2, $fname, $format_HRto +p2); $worksheet0->write($row++, 2, $lname, $format_HRmi +ddle); $worksheet0->write($row++, 2, $title, $format_HRmi +ddle); $worksheet0->write($row++, 2, $supervisor3, $forma +t_HRbottom); $row++; $supervisor4 = $supervisor3; my $sth = $dbh->prepare("SELECT fname, lname, title, super +visor FROM acnse WHERE eid ='$supervisor4';"); $sth->execute or die $sth->errstr; while ( my $aref = $sth ->fetchrow_arrayref) { my ($fname, $lname, $title, $supervisor4) = @$aref; #write data to spreadsheet row by row $worksheet0->write($row++, 2, $fname, $format_HRto +p2); $worksheet0->write($row++, 2, $lname, $format_HRmi +ddle); $worksheet0->write($row++, 2, $title, $format_HRmi +ddle); $worksheet0->write($row++, 2, $supervisor4, $forma +t_HRbottom); $row++; $supervisor5 = $supervisor4; my $sth = $dbh->prepare("SELECT fname, lname, title, super +visor FROM acnse WHERE eid ='$supervisor5';"); $sth->execute or die $sth->errstr; while ( my $aref = $sth ->fetchrow_arrayref) { my ($fname, $lname, $title, $supervisor5) = @$aref; #write data to spreadsheet row by row $worksheet0->write($row++, 2, $fname, $format_HRto +p2); $worksheet0->write($row++, 2, $lname, $format_HRmi +ddle); $worksheet0->write($row++, 2, $title, $format_HRmi +ddle); $worksheet0->write($row++, 2, $supervisor5, $forma +t_HRbottom); $row++; $supervisor6 = $supervisor5; my $sth = $dbh->prepare("SELECT fname, lname, title, super +visor FROM acnse WHERE eid ='$supervisor6';"); $sth->execute or die $sth->errstr; while ( my $aref = $sth ->fetchrow_arrayref) { my ($fname, $lname, $title, $supervisor6) = @$aref; #write data to spreadsheet row by row $worksheet0->write($row++, 2, $fname, $format_HRto +p2); $worksheet0->write($row++, 2, $lname, $format_HRmi +ddle); $worksheet0->write($row++, 2, $title, $format_HRmi +ddle); $worksheet0->write($row++, 2, $supervisor6, $forma +t_HRbottom); $row++; $supervisor7 = $supervisor6; my $sth = $dbh->prepare("SELECT fname, lname, title, super +visor FROM acnse WHERE eid ='$supervisor7';"); $sth->execute or die $sth->errstr; while ( my $aref = $sth ->fetchrow_arrayref) { my ($fname, $lname, $title, $supervisor7) = @$aref; #write data to spreadsheet row by row $worksheet0->write($row++, 2, $fname, $format_HRto +p2); $worksheet0->write($row++, 2, $lname, $format_HRmi +ddle); $worksheet0->write($row++, 2, $title, $format_HRmi +ddle); $worksheet0->write($row++, 2, $supervisor7, $forma +t_HRbottom); $row++; $supervisor8 = $supervisor7; my $sth = $dbh->prepare("SELECT fname, lname, title, super +visor FROM acnse WHERE eid ='$supervisor8';"); $sth->execute or die $sth->errstr; while ( my $aref = $sth ->fetchrow_arrayref) { my ($fname, $lname, $title, $supervisor8) = @$aref; #write data to spreadsheet row by row $worksheet0->write($row++, 2, $fname, $format_HRto +p2); $worksheet0->write($row++, 2, $lname, $format_HRmi +ddle); $worksheet0->write($row++, 2, $title, $format_HRmi +ddle); $worksheet0->write($row++, 2, $supervisor8, $forma +t_HRbottom); $row++; } } } } } } } }
    Hagen Finley Boulder, CO

      finhagen:

      Sure thing. Just take it step by step. First, notice that your code is structured something like this:

      initialization execute an SQL statement while (my $aref=$sth->fetchrow_arrayref) { ... write to spreadsheet ... prepare & execute an SQL statement while (my $aref=$sth->fetchrow_arrayref) { ... write to spreadsheet ... prepare & execute an SQL statement while (my $aref=$sth->fetchrow_arrayref) { ... write to spreadsheet ... prepare & execute an SQL statement while (...) { <<< same thing for several more levels } } } }

      The first thing you need to do is identify a repeated chunk of code that's relatively easy to separate from the surrounding code. There are several things you might choose, but the one that jumped out at me was the code that converts the array reference into a set of variables and then writes the variables to a worksheet.

      It's easy to separate from the surrounding code because there's only three variables on the input side ($aref, $worksheet0 and $row) and one variable on the output side ($supervisor). Since the $row is going to change everywhere, and since it would be pain to have to pass it around and return it everywhere, I decided to leave it a global variable. So what I wound up with is this:

      sub add_aref_to_sheet { my ($aref, $worksheet0) = @_; my ($fname, $lname, $title, $supervisor) = @$aref; #write data to spreadsheet row by row $worksheet0->write($row++, 2, $fname, $format_HRtop2); $worksheet0->write($row++, 2, $lname, $format_HRmiddle); $worksheet0->write($row++, 2, $title, $format_HRmiddle); $worksheet0->write($row++, 2, $supervisor, $format_HRbottom); $row++; return $supervisor; }

      We return the supervisor variable so you can use it in the next level. When you pull that code out into a subroutine, your code simplifies a good bit:

      our $lname; our $fname; our $title; our $eid; our $supervisor; our $supervisor2; our $supervisor3; our $supervisor4; our $supervisor5; our $supervisor6; our $supervisor7; our $supervisor8; #select fname, lname, csg from acnse where supervisor ='pierre.nanterm +e'; my $sth = $dbh->prepare("SELECT fname, lname, title, supervisor FROM a +cnse WHERE eid ='employeID';"); $sth->execute or die $sth->errstr; my $row=23; while ( my $aref = $sth ->fetchrow_arrayref) { $supervisor2 = add_aref_to_sheet($aref, $worksheet0); my $sth = $dbh->prepare("SELECT fname, lname, title, supervisor FRO +M acnse WHERE eid ='$supervisor2';"); $sth->execute or die $sth->errstr; while ( my $aref = $sth ->fetchrow_arrayref) { $supervisor3 = add_aref_to_sheet($aref, $worksheet0); my $sth = $dbh->prepare("SELECT fname, lname, title, supervisor +FROM acnse WHERE eid ='$supervisor3';"); $sth->execute or die $sth->errstr; while ( my $aref = $sth ->fetchrow_arrayref) { $supervisor4 = .... ... continue for several more levels ... } } } sub aref { ... same as shown earlier ... }

      There are a good few tuneups you can do to this program. You might want to read up on placeholders in DBI. That can simplify your code a bit, too. Then, depending on how many supervisors a person may have, whether you want to go as many levels as required without hardcoding, etc., there are several ways you could go from here. I'll stop here, as I don't want to take *all* the fun out of it! ;^) But I *will* provide a hint: If each person has only one supervisor, you can turn the successive level of while loops into a simple loop. Give it a try and let me know if you run into any stumbling blocks.

      ...roboticus

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

        Thanks!

        At present I took your recommendations and updated my code successfully.

        Each employee has just one supervisor so I should be able to reduce it to one loop - I'll let you know how I faire.

        Hagen Finley Boulder, CO

        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); } } } } } } } }
        Hagen Finley Boulder, CO

Log In?
Username:
Password:

What's my password?
Create A New User
Node Status?
node history
Node Type: note [id://1010099]
help
Chatterbox?
and the web crawler heard nothing...

How do I use this? | Other CB clients
Other Users?
Others chanting in the Monastery: (16)
As of 2014-04-23 18:43 GMT
Sections?
Information?
Find Nodes?
Leftovers?
    Voting Booth?

    April first is:







    Results (553 votes), past polls