in reply to Re^2: How to add columns with new row name using perl from mysql query?
in thread How to add columns with new row name using perl from mysql query?

The hash won't preserve the sort order from the SQL so you have to retain that in an array which I have called @top

#!perl
use DBI; 
use strict; 

my $DBH = get_dbh();
my $sql = 'SELECT queue_name,jobs_pend,jobs_run 
           FROM queues 
           ORDER BY jobs_run DESC';
my $sth = $DBH->prepare( $sql );
$sth->execute();

# input
my %table = ();
my $recno = 0;
my @top   = ();
while (my ($name,$pend,$run) = $sth->fetchrow_array){
  my $key = ($recno++ < 5) ? $name : 'other' ;
  $table{$key}{'pend'} += $pend;
  $table{$key}{'run'}  += $run;
  push @top,$key unless ($top[-1] eq 'other');
}

# output
for my $key (@top){
  my @col = ($key);# col[0]
  $col[1] = $table{$key}{'pend'};
  $col[2] = $table{$key}{'run'};
  printf "| %-25s | %3d | %3d |\n",@col;
}

[download]

Update : you could just use an array without the hash

# input
my @top = ();
my $max = 5; my $recno = 0;
while (my ($name,$pend,$run) = $sth->fetchrow_array){
  my $ix = ($recno > $max) ? $max : $recno ;
  $top[$ix][0]  = $name;
  $top[$ix][1] += $pend;
  $top[$ix][2] += $run;
  ++$recno;
}
$top[$max][0]  = 'other';

# output
for (@top){
  printf "| %-25s | %3d | %3d |\n",@$_;
}

[download]
poj

Replies are listed 'Best First'.
Re^4: How to add columns with new row name using perl from mysql query?
by finddata (Sexton) on Apr 05, 2017 at 12:13 UTC
    i had tried to print the output block in the perl cgi format which as follows.But nothing is printed for me.Let me know what mistake i had done in the following?
    print "var data_run=[$row_array{$key}{'pend'}];\n";
print "var data_pend=[$row_array{$key}{'run'}];\n";

    [download]

    For my previous code which i used for questioning there i had printed which as follows.Here it works fine.which as follows
    print "var data_running = [$var_data_running]; \n";
print "var data_pending = [$var_data_pending]; \n";
<br>
Here the if and else statements variables are printed outside in perl 
+cgi format<br>
<c>    
 while(my @row_array=$sth->fetchrow_array)
    {
        if ($tmp == 0) {
        $var_data_running .= "\[\"$row_array[0] \($row_array[2]\)\",$r
+ow_array[2]\]";
        $var_data_pending .= "\[\"$row_array[0] \($row_array[1]\)\",$r
+ow_array[1]\]";
        
        $tmp++;
        }
        else {
        $var_data_running .= ",\[\"$row_array[0] \($row_array[2]\)\",$
+row_array[2]\]";
        $var_data_pending .= ",\[\"$row_array[0] \($row_array[1]\)\",$
+row_array[1]\]";
        }
    
    }

    [download]
    </c>
    Likewise the above if and else statement variables i had tried to print the same varibales for the code which you mentioned but i had failes in those cases.
    Thanks for any help.
[reply]
[d/l]
[select]

      poj replied to perlanswers you've responded as finddata. Did you forget which account you were logged in with?

[reply]

      To format the data for javascript use the JSON module

      #!perl
use strict;
use warnings;
use JSON 'encode_json';

my @top = (
  ["adice_short", 254, 192],
  ["ncsim_short", 0, 84],
  ["ncsim_long", 41, 78],
  ["adice_long", 5, 39],
  ["normal", 170, 30],
  ["other", 1, 34],
);

my @pend=(); my @run =();
for (@top){
  push @pend,["$_->[0] ($_->[1])",$_->[1]];
  push @run, ["$_->[0] ($_->[2])",$_->[2]];
}
my $json_data_pend = encode_json( \@pend );
my $json_data_run  = encode_json( \@run );
print "var data_run = $json_data_run \n";

      [download]
      poj
[reply]
[d/l]

      back to your pie charts now huh?

      It might help if you understood what your var data_run= statment was supposed to look like. If i remember correctly it is an array of arrays. The interior arrays contain a label and a value. 

      var data_run=[ ["label a",1],["label b",2],["label_c",99]];

      [download]
       
use strict; use warnings; 
use DBI; 

my $storagefile='finddata';
my $DBH = DBI->connect( "dbi:SQLite:dbname=".$storagefile ) || die "Ca
+nnot connect: $storagefile $DBI::errstr";


my $sql = 'SELECT queue_name,jobs_pend,jobs_run 
           FROM queues 
           ORDER BY jobs_run DESC';
my $sth = $DBH->prepare( $sql );
$sth->execute();

# input
my %table = ();
my $recno = 0;
my @top   = ();
while (my ($name,$pend,$run) = $sth->fetchrow_array){
  my $key = ($recno++ < 5) ? $name : 'other' ;
  push @top,$key unless (defined ($top[-1]) && $top[-1] eq 'other');
  $table{$key}{'pend'} += $pend;
  $table{$key}{'run'}  += $run;
}

#So first you need to make your interior arrays
my @iarray_run; 
for my $key (@top){
  push @iarray_run,'["'.$key.'",'.$table{$key}{'run'}.']'; 
}
# then you join them and assign them 
my $orun=join(',',@iarray_run); 
print "var data_run=[$orun];\n";

      [download]
      Result 
      var data_run=[["adice_short",192],["ncsim_short",84],["ncsim_long",78]
+,["adice_long",39],["normal",30],["other",34]];

      [download]
      They must be hard up for programmers where you work. It takes you days to produce something that anyone competent would do in an hour.

[reply]
[d/l]
[select]
        ' It takes you days to produce something that anyone competent would do in an hour.'. Wrong. They post here, you do their job and complain, goto 10. Keep it up
[reply]

In Section Seekers of Perl Wisdom