DBD:Pg getting output in an array

terrykhatri has asked for the wisdom of the Perl Monks concerning the following question:

Hi,

I have been googling around to see if I can find an example on getting query results in arrays so I can use it in charts but no luck therefore I am knocking your doors once again to get the solution, here is my query.

my $sql = qq! SELECT 
                 a."FirstName"::TEXT || ' ' ||a."LastName" AS "Labels"
+,  
                 ROUND(SUM((b."UnitPrice" - b."Discount") * b."Quantit
+y")) AS "Data"  
              FROM "Employees" a, "Order_Details" b, "Orders" c  
              WHERE a."EmployeeID" = c."EmployeeID" AND  
                    b."OrderID" = c."OrderID"  
              GROUP BY 1  
              ORDER BY 2 DESC  
              LIMIT 10!; 
my $sth=$dbh->prepare("$sql"); 
$sth->execute() || quit(); 
while (my $ref = $sth->fetchrow_hashref()) { 
 
my $labels  = $ref->{'Labels'}; 
my $data    = $ref->{'Data'}; 
}
[download]

The required ouput format is as under :

$labels = [label1, label2, label3.....];
$data   = [data1, data2, data3.........];
[download]

As usual your help would be much appreciated.

Many many thanks!!

Terry

Comment on DBD:Pg getting output in an array Select or Download Code

Replies are listed 'Best First'.
Re: DBD:Pg getting output in an array by Corion (Patriarch) on Jul 27, 2014 at 17:52 UTC
I'm not sure what you really expect to be in `$ref`, and what you get in `$ref`, but I guess that most of your question does not relate to DBD::Pg but simple DBI usage. If you want to output your results as a table to the screen, I would use (say) Text::Table the statement: `$sth->execute or quit(); my @headers= @{ $sth->{NAME} }; my $rows= $sth->fetchall_arrayref(); my $t= Text::Table->new( @$rows ); print $t;` [download]	[reply] [d/l] [select]
Re^2: DBD:Pg getting output in an array by terrykhatri (Acolyte) on Jul 27, 2014 at 19:23 UTC
Actually I need to use the arrays to generate a chart.	[reply]
Re^3: DBD:Pg getting output in an array by poj (Abbot) on Jul 27, 2014 at 19:33 UTC
Try `my $labels = []; my $data = []; while (my $ref = $sth->fetchrow_hashref()) { push @$labels,$ref->{'Labels'}; push @$data,$ref->{'Data'}; }` [download] poj	[reply] [d/l]
Re^4: DBD:Pg getting output in an array by terrykhatri (Acolyte) on Jul 27, 2014 at 21:30 UTC
Re^3: DBD:Pg getting output in an array by Corion (Patriarch) on Jul 27, 2014 at 19:27 UTC
So instead of using the parts to output them as a table, maybe use the two separate parts to generate your chart? I'm not exactly sure where your problem lies in taking the data and putting it to use, so I'm uncertain about how to help you better. Maybe you can tell me what parts you have problems with?	[reply]