Beefy Boxes and Bandwidth Generously Provided by pair Networks
The stupid question is the question not asked
 
PerlMonks  

Re^3: How to add columns with new row name using perl from mysql query?

by poj (Abbot)
on Apr 05, 2017 at 07:12 UTC ( #1187071=note: print w/replies, xml ) Need Help??


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; }

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",@$_; }
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";

    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]\]"; } }
    </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.

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

      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";
      poj

      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]];
      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";
      Result
      var data_run=[["adice_short",192],["ncsim_short",84],["ncsim_long",78] +,["adice_long",39],["normal",30],["other",34]];
      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.

        ' 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

Log In?
Username:
Password:

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

How do I use this? | Other CB clients
Other Users?
Others having an uproarious good time at the Monastery: (2)
As of 2020-10-25 14:41 GMT
Sections?
Information?
Find Nodes?
Leftovers?
    Voting Booth?
    My favourite web site is:












    Results (249 votes). Check out past polls.

    Notices?