in reply to Re: how to save data to new array after retrieving from sql server
in thread how to save data to new array after retrieving from sql server

Hi Pol Let say I like to build a hash from that array reference. I wrote like this, but I got error. where am i wrong?

my %hdata = ();
for my $i (0 .. $#{$table}) {
print $#{$table}; ### why cant find the length of array here
my $location = $table[i]->[0];
my $room = $table[i]->[1];
push $hdata{$location}=$room;    
}

[download]
Nirvana is Now or Never
  • Comment on Re^2: how to save data to new array after retrieving from sql server
  • Download Code

Replies are listed 'Best First'.
Re^3: how to save data to new array after retrieving from sql server
by poj (Abbot) on Jul 28, 2017 at 06:34 UTC
    where am i wrong

    $#{$table} should be either $#${table} or just $#$table

    $table is an arrayref so these 

      my $location = $table[i]->[0]; # missing $ on i
  my $room = $table[i]->[1];

    [download]
    should be 
      my $location = $table->[$i][0];
  my $room     = $table->[$i][1];

    [download]

    and push is for building an array not a hash, so this

    push $hdata{$location}=$room;

    should be

    $hdata{$location} = $room;

    or alternatively

    #!/usr/bin/perl
use strict;
use Data::Dump 'pp';
my $table = [
 ['Wellbeing Office', 'Pending'],
 ['Library','Pending'], ['Y219','InProgress'],
 ['B201','InProgress'], ['B108','InProgress'],
 ['LAB1','InProgress'], ['C303','InProgress'],
];

my $last_index = $#${table};
my $count = scalar @$table;
print "
records    = $count 
last index = $last_index\n";

#my %hdata = ();
#for my $i (0..$last_index){
#  my $location = $table->[$i][0];
#  my $room     = $table->[$i][1];
#  $hdata{$location} = $room;    
#}
#pp \%hdata;

my %hdata = ();
for my $row (@$table){
  my $location = $row->[0];
  my $status   = $row->[1];
  $hdata{$location} = $status;    
}
pp \%hdata;

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

      Hello All, I follow all your advice and finally I got the HTML page that I want with the cell change color based on condition, now I do not want table width = 100% and I want fix size for all the cells I am stucked. When I changed the table width to auto and cells to some px all the headings just jumps. any idea please? 

      #!/usr/bin/perl
use strict;
use warnings;
use Time::Piece;   

#for testing
my $dest = "C:/Perl-Script/Network Faults/";

# current date/time
my $t = localtime;
my $date = $t->wdayname.' '.$t->dmy('/');
my $time = $t->hms;

# input data
my %hdata = build_hash($dest."RoomName.txt");
my $href = build_sqlData();
my @lrooms=();

foreach my $room (sort keys  %{$href}){
    #print "room is $room\n";
    #print "status is ${$href}{$room}\n";
    push @lrooms, $room;
    #print "num is @lrooms\n";
}

# output html
open OUT, ">". $dest."ICT Report.html" or die "$!";
print OUT qq(
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN" 
  "http://www.w3.org/TR/html4/loose.dtd"><html>
<head>
   <title>Bulletin</title>
    <style type="text/css">
     h4   { color: #0; font-size: 1em; }  
   </style>     

    <meta name="viewport" content="width=device-width, initial-scale=1
+">
    <link rel="stylesheet" type='text/css' media='screen' href="/Perl-
+Script/Extra/w3.css"> 
    
</head>
<body>
    <div class="w3-container w3-blue">
    <h4>NewYork Time</h4>
    <h4>Report Date $date  $time</h4>
    </div>
    <div class="w3-control-bar"></div>

);

foreach my $key (sort keys %hdata) {
        
      my $h4 = $key;
    $h4 =~ s/[\"\$]//g;
    print OUT qq!<h4>$h4</h4>
    <table border="1" width="100%" style="border-color:#e0e0e0" cellpa
+dding="5" align="left">\n!; #how to change width of table here????
                
    my @arrays = @{$hdata{$key}}; 
    for my $i (1..$#arrays) {
      print OUT '<tr>';
      my @col = split(/,/,$arrays[$i]);
                    
      for my $j (0..$#col) {
        $col[$j] =~ s/\"Period\"/\"Class\"/g;   
        $col[$j] =~ s/"//g;
        my $bg = "bgcolor=#e0e0e0";
        #print @lrooms;

         if ($col[$j] ~~ @lrooms) {
              $bg = (${$href}{$col[$j]} =~ /Pending/) ? "bgcolor=#FF00
+00" :  
                     (${$href}{$col[$j]} =~ /InProgress/) ? "bgcolor=#
+FFFF00" :
                  "";        
         }

        print OUT "<td $bg align=left>$col[$j]</td>"; # how to set the
+ fixed width of cells ???
      };
      print OUT "</tr>";      
    };
    print OUT "</table>&nbsp;<br>\n";
}
print OUT "</body></html>";
close OUT;

#-------------------------------------
# This subroutine split text file to 
# a hash of arrays for data processing
# -------------------------------------
sub build_hash{
  
  my $infile = shift;
  my %data;
  my $last ='';
  open my $ifh,'<',$infile or die "$!";
  while (my $line=<$ifh>) { 
    chomp $line; 
    if ( substr($line,0,2) eq '"$' 
      || substr($line,0,1) eq '$') { 
      $last = $line; 
    } 
    push @{$data{$last}},$line;   
  }
  close $ifh;
  return %data;
}

#--------------------------------------------
# This subroutine extracts data from sql server 
# to an array of arrays for data processing
# -------------------------------------------
sub build_sqlData{
use Data::Dumper;
use DBI;

# establish the connection
my $dbh = DBI->connect('dbi:ODBC:myDSN','user','pass',
      { RaiseError => 1, 
        PrintError => 1 } )
   or die "Could not connect to database: $DBI::errstr";
   
# sql query statement
my $sql =<< "SQL";
  SELECT RTRIM(location),RTRIM(CompletionStatus) 
  FROM ewNetworkFaults
  WHERE Type like '%Room Audio Visual%'
    AND CompletionStatus not like '%Completed%'
SQL
  
my $table = $dbh->selectall_arrayref($sql);
print Dumper $table;

my $last_index = $#${table};
my $count = scalar @$table;
print "
records = $count
last_index = $last_index\n";
my %hdata;
for my $row (@$table) {
    my $location = $row->[0];
    my $status = $row->[1];
    $hdata{$location} = $status;
}

#print Dumper \%hdata;
return \%hdata;
}

      [download]
      Nirvana is Now or Never
[reply]
[d/l]

        Remove the width and alignment from the table tag. Add cellspacing="0" if you want to change the double line borders to single. 

        print OUT qq!<h4>$h4</h4>
    <table border="1" style="border-color:#e0e0e0" cellpadding="5" cel
+lspacing="0" >\n!;

        [download]
        poj
[reply]
[d/l]
[select]
Re^3: how to save data to new array after retrieving from sql server
by AnomalousMonk (Archbishop) on Jul 28, 2017 at 07:22 UTC
    where am i wrong?
    ...
    print $#{$table}; ### why cant find the length of array here

    Your syntax for "highest array index" by reference is correct (as are the more common variations given by poj here), but be aware of the fact (pointed out by poj) that the highest array index is not the same as the array length (i.e., number of elements) for the default value of  $[ (see perlvar). (BTW:   Don't ever touch $[   :) 

    c:\@Work\Perl\monks>perl -wMstrict -le
"my $ar = [ qw(a b c d e) ];
 ;;
 print $#{ $ar };
 print $#${ ar };
 print $#$ar;
"
4
4
4

    [download]

    For readability and maintainability, I tend to prefer the for-loop approach used by poj for populating a hash from an array or array reference, but here's a variation that's a bit more terse: 

    c:\@Work\Perl\monks>perl -wMstrict -MData::Dump -le
"my $table = [
  ['Wellbeing Office', 'Pending'],
  ['Library','Pending'], ['Y219','InProgress'],
  ['B201','InProgress'], ['B108','InProgress'],
  ['LAB1','InProgress'], ['C303','InProgress'],
 ];
 ;;
 my %hdata =
   map @{ $_ }[0, 1],
   @$table
   ;
 ;;
 dd \%hdata;
"
{
  B108 => "InProgress",
  B201 => "InProgress",
  C303 => "InProgress",
  LAB1 => "InProgress",
  Library => "Pending",
  "Wellbeing Office" => "Pending",
  Y219 => "InProgress",
}

    [download]

    Give a man a fish:  <%-{-{-{-<

[reply]
[d/l]
[select]

In Section Seekers of Perl Wisdom