Beefy Boxes and Bandwidth Generously Provided by pair Networks
Your skill will accomplish
what the force of many cannot
 
PerlMonks  

mysql DBI Queries to variables

by finhagen (Sexton)
on Dec 12, 2012 at 15:09 UTC ( #1008515=perlquestion: print w/ replies, xml ) Need Help??
finhagen has asked for the wisdom of the Perl Monks concerning the following question:

I have a script which queries a mysql 5.5 database which is working well. However, the output is more rigid than I would like - my output is informing a Spreadsheet::WriteExcel based report. What I would like is to assign each member of each row a variable so I can place the data in the spreadsheet cell of my choosing. Here's the code snippet I am using:
###################################################################### +############# # # Account Contacts Tab # use strict; use warnings; use DBI; use DBD::mysql; use Spreadsheet::WriteExcel; our $acnse; our $emc; my $worksheet1 = $workbook->add_worksheet('Contacts'); my $acnse = $dbh->prepare("SELECT a.`function`, b.`fname`,b.`lname`, b +.`email`, b.`ophone`, b.`mobile`, b.`loffice` FROM `acct` a, `acnse` +b WHERE a.`cid` ='BBYPRD' AND a.`eid` = b.`eid` order by function;"); $acnse->execute or die $acnse->errstr; my $row = 7; my $col = 1; $worksheet1->write_row($row++,$col,['Role','First Name','Last Name','E +mail', 'Office Phone', 'Cell', 'Office'], $format_body1); while(my @data = $acnse ->fetchrow_array) { $worksheet1->write_row($row++,$col,\@data); } $worksheet1->set_column(6, 2, 15); $worksheet1->set_column(6, 3, 15); $worksheet1->set_column(6, 4, 30); $worksheet1->set_column(6, 5, 20); $worksheet1->set_column(6, 6, 20); $worksheet1->set_column(6, 7, 25); my $emc = $dbh->prepare("SELECT a.`function`, b.`fname`, b.`lname`, b. +`email`, b.`ophone`, b.`cphone`, b.`geo` FROM `acct` a, `emc` b WHERE + a.`cid` ='BBYPRD' AND b.`eid` = a.`eid` order by function;"); $emc->execute or die $emc->errstr; my $row = 21; my $col = 1; $worksheet1->write_row($row++,$col,['Role','First Name','Last Name +','Email', 'Office Phone', 'Cell', 'Office'], $format_body1); while(my @data = $emc ->fetchrow_array) { $worksheet1->write_row($row++,$col,\@data); } $worksheet1->write (0, 0, $acct, $format_title); $worksheet1->write (0, 7, $ymd, $format_body); $worksheet1->write (1, 0, 'Contact Report', $format_title3); $worksheet1->write (5, 0, 'Accenture Contacts', $format_title3 +); $worksheet1->write (20, 0, 'EMC Contacts', $format_title3);
That works but the output includes the column header information and iterates one row at a time:
Contacts Role First Name Last Name Email Office Phone Cell + Office *** xxxxx xxxxx xxxxxx 999-999-9999 Somwhere *** xxxxx xxxxx xxxxxx 999-999-9999 Somwhere *** xxxxx xxxxx xxxxxx 999-999-9999 Somwhere *** xxxxx xxxxx xxxxxx 999-999-9999 Somwhere *** xxxxx xxxxx xxxxxx 999-999-9999 Somwhere
I would prefer to capture the output into an array and use the array_name[0] to identify the output members and write them to the spreadsheet with Spreadsheet::WriteExcel format strings. Thanks in advance for your wisdom.
Hagen Finley Boulder, CO

Comment on mysql DBI Queries to variables
Select or Download Code
Re: mysql DBI Queries to variables
by space_monk (Chaplain) on Dec 12, 2012 at 15:48 UTC

    Isn't a simple way to map the data array to a new array in the order you want?

    my @reordered = @elements[1,2,0];
    A Monk aims to give answers to those who have none, and to learn from those who know more.
      Never mind - I found a code example from an old question I posted on this site which answers my question:
      $sth= $dbh->prepare("select dedate,de,den,op,so,rep,customer,dealn,cc +o,ebv,fcost,fmarg,bookad,tir,swmaint,hwmaint,svsums,svcost,svmarg,flo +or from quote"); $sth->execute(); #capture output into array and variablize the results while ( my $aref = $sth->fetchrow_arrayref ) { my ($dedate,$de,$den,$op,$so,$rep,$customer,$dealn,$cco,$ +ebv,$fcost,$fmarg,$bookad,$tir,$swmaint,$hwmaint,$svsums,$svcost,$svm +arg,$floor) = @$aref; #write data to spreadsheet row by row $worksheet->write_date_time($row+2, 0, $dedate,$alignc +d); $worksheet->write_number($row+2, 1, $de,$alignc); $worksheet->write_number($row+2, 2, $den,$alignc); $worksheet->write_number($row+2, 3, $op,$alignc); $worksheet->write_number($row+2, 4, $so,$alignc); $worksheet->write_string($row+2, 5, $rep,$alignl); $worksheet->write_string($row+2, 6, $customer,$alignl) +; $worksheet->write_string($row+2, 7, $dealn,$alignl); $worksheet->write_number($row+2, 8, $cco,$alignld); $worksheet->write_number($row+2, 9, $ebv,$alignld); $worksheet->write_number($row+2, 10, $fcost,$alignld); $worksheet->write_formula($row+2, 12, '=1-(K.($row+2)/ +J.($row+2))'
      Thanks again for your kind assistance. Thank your for your reply. The goal isn't to reorder the elements in the array, it's to address the elements so I can place them in the spreadsheet cells wherever I please. However, to your point, since, in my script, the output of my query appears to be placed into the variable $emc and then added to the @data array, why can't I address the elements of the @data array as $data[0], $data1 and so forth?

      It would be helpful to understand more clearly what is happening to the output of the SQL query - how is perl variablizing that output?

      When I try addressing the data as $data[0], I get an error "Not an array ref in call to write_row() at ./AcntRptTest2.pl line 222". Line 222 is

       while(my @data = $emc ->fetchrow_array) .

      My revised code which produces that error is:

      my $emc = $dbh->prepare("SELECT a.`function`, b.`fname`, b.`lname`, b +.`email`, b.`ophone`, b.`cphone`, b.`geo` FROM `acct` a, `emc` b WHER +E a.`cid` ='BBYPRD' AND b.`eid` = a.`eid` order by function;"); $emc->execute or die $emc->errstr; my $row = 21; my $col = 1; $worksheet1->write_row($row++,$col,['Role','First Name','Last Name +','Email', 'Office Phone', 'Cell', 'Office'], $format_body1); while(my @data = $emc ->fetchrow_array) { $worksheet1->write_row($row++,$col,$data[6],$data[5], $data[4],$d +ata[3], $data[2], $data[1], $data[0]); }
      I'm sure my error is simple and probably obvious to the wise monks who toil for our benefit, so I appreciate your patient assistance.
      Hagen Finley Boulder, CO

Log In?
Username:
Password:

What's my password?
Create A New User
Node Status?
node history
Node Type: perlquestion [id://1008515]
Approved by 2teez
help
Chatterbox?
and the web crawler heard nothing...

How do I use this? | Other CB clients
Other Users?
Others wandering the Monastery: (8)
As of 2014-09-18 08:13 GMT
Sections?
Information?
Find Nodes?
Leftovers?
    Voting Booth?

    How do you remember the number of days in each month?











    Results (109 votes), past polls