Beefy Boxes and Bandwidth Generously Provided by pair Networks
Pathologically Eclectic Rubbish Lister
 
PerlMonks  

Re: mysql DBI Queries to variables

by space_monk (Chaplain)
on Dec 12, 2012 at 15:48 UTC ( #1008519=note: print w/ replies, xml ) Need Help??


in reply to mysql DBI Queries to variables

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.


Comment on Re: mysql DBI Queries to variables
Download Code
Replies are listed 'Best First'.
Re^2: mysql DBI Queries to variables
by finhagen (Sexton) on Dec 12, 2012 at 23:59 UTC
    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: note [id://1008519]
help
Chatterbox?
and the web crawler heard nothing...

How do I use this? | Other CB clients
Other Users?
Others browsing the Monastery: (15)
As of 2015-07-31 14:41 GMT
Sections?
Information?
Find Nodes?
Leftovers?
    Voting Booth?

    The top three priorities of my open tasks are (in descending order of likelihood to be worked on) ...









    Results (278 votes), past polls