http://www.perlmonks.org?node_id=1187072


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 (

)