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 huck (Parson)
on Apr 05, 2017 at 08:10 UTC ( #1187072=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?

Gee thats funny. Given

use strict; use warnings; use DBD::SQLite; use DBI; my $table='queues'; my $ccode='queue_name TEXT, jobs_pend INTEGER, jobs_run INTEGER'; my $storagefile='finddata'; my $dbh = DBI->connect( "dbi:SQLite:dbname=".$storagefile ) || die "Ca +nnot connect: $storagefile $DBI::errstr"; remake($table,$ccode); my $insert; { my $sql='INSERT INTO '.$table.'( queue_name , jobs_pend , jobs_run ) +values( ?,?,?)'; $insert=$dbh->prepare($sql); } while (my $line=<DATA>) { $line=~s/ //g; my (undef,$qn,$jp,$jr)=split('[|]',$line); $insert->execute($qn,$jp,$jr); } my $select; { my $sql="select * from ".$table; $select=$dbh->prepare($sql); } $select->execute(); while(my $row=$select->fetchrow_arrayref){ printf "| %-25s | %3d | %3d |\n",@$row; # print join ('|',@$row)."\n"; } # ROW $select->finish; exit; sub remake { my $table=shift; my $code=shift; $table='queues' unless ($table); $code='queue_name TEXT, jobs_pend INTEGER, jobs_run INTEGER' unless + ($code); $dbh->do("DROP TABLE IF EXISTS ".$table); my $sql="CREATE TABLE ".$table.' ('.$code.')'; print $sql."\n"; $dbh->do($sql); } # redo; exit; __DATA__ | adice_long | 5 | 39 | | adice_ncsim | 0 | 6 | | adice_short | 254 | 192 | | calibre | 0 | 0 | | dsp_ncsim_gls | 0 | 2 | | dsp_ncsim_hp | 0 | 2 | | dsp_ncsim_lp | 0 | 5 | | dsp_ncsim_mp | 0 | 5 | | hcg_ncsim_comp | 0 | 0 | | hcg_ncsim_hp | 0 | 9 | | hcg_ncsim_lp | 0 | 0 | | hcg_ncsim_mp | 0 | 0 | | hcg_ncsim_short | 0 | 0 | | ipdc_pte | 0 | 0 | | ncsim_long | 41 | 78 | | ncsim_lp | 1 | 4 | | ncsim_short | 0 | 84 | | normal | 170 | 30 | | spectreRF | 0 | 1 | | vcs | 0 | 0 |
when i run
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; while (my ($name,$pend,$run) = $sth->fetchrow_array){ my $key = ($recno++ < 5) ? $name : 'other' ; $table{$key}{'pend'} += $pend; $table{$key}{'run'} += $run; } # output for my $key (sort keys %table){ my @col = ($key);# col[0] $col[1] = $table{$key}{'pend'}; $col[2] = $table{$key}{'run'}; printf "| %-25s | %3d | %3d |\n",@col; }
i get
| adice_long | 5 | 39 | | adice_short | 254 | 192 | | ncsim_long | 41 | 78 | | ncsim_short | 0 | 84 | | normal | 170 | 30 | | other | 1 | 34 |
See i got an other row that you said you didnt, and i displayed rows correctly that you didnt. Why didnt you post what you actually got? Are you that lazy? If you dont want to help us why should we help you? You dont even grovel correctly.

and of course poj is right about the sort order. in  sort keys %table adice_long comes before adice_short

You owe the PerlOracle a remote job entry queue (

)

Log In?
Username:
Password:

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

How do I use this? | Other CB clients
Other Users?
Others chilling in the Monastery: (3)
As of 2020-10-27 12:59 GMT
Sections?
Information?
Find Nodes?
Leftovers?
    Voting Booth?
    My favourite web site is:












    Results (256 votes). Check out past polls.

    Notices?