Newbie, desperate need of explaination

[r2d22y]

Hello

I have a Mysqldatabase which I store "event data" for an application. In this case I have to use PERL because the application is made using PERL, another guy has already started but Im having seriosly problems knowing what he has done so I would really be glad if anyone could help me clearing some questions out!


Lets say my Mysql database has 5 columns:

id usrid date_from interval_nr event_cnt
----------------------------------------------
1 2 2005-04-04 2 1
2 2 2005-04-04 3 6
3 2 2005-04-04 3 1
.
----------------------------------------------

Now all rows are fetched using SELECT * FROM mytable into a hashtable using syntax below
.
.
Some declaration (prepare select execute)
.
while ($row = $sth->fetchrow_hashref ()) {
foreach $key (keys %$row) { $res{$row->{usr_id}}{$row->{date_from}}{$row->{interval_nr}}{$key}=$row->{$key};
}
}
.

What is he doing(?)... what I understand with my little knowledge he stores the values in a multidimensonal hash that has its keys %res{some queuenr}{some date}{some intervalnr}....but is the hashvalue all other rows or?


lets saya I want to summerize all elements by value "event_cnt" ("event_cnt" is a column name in my Mysql-table) how do I do that? I have been trying to do as below but then I get an "Use of uninitialized value" for values in my hashtable.

@datefrom=("2005-10-19 00:00:00","2005-10-20 00:00:00");
@usrid=(478,14,594,53,52,392,595,759);
foreach $que (@usrid) {
foreach $date_from (@datefrom){
foreach $interval (0..95) {
$tot{'income_cnt'}{$que} += $res{$que}{$date_from}{$interval}{'event_cnt'};

}
}

print $tot{'income_cnt'}{$que}." ".$tot{'lostcalls'}{$que}."\n";

}

I hope you understand what I mean..

Regards/D_S





Then I create a multidimensopnale hash (%res) that has

 

[PaulTEG]

Looks like he's setting up a multi dimesional hash, where the co-ordinates from the select are being set to the key. Without knowing all the data, I'm not sure.

I'm thinking a multidimensional hash might not be the best way to do it, but then again without a proper context of what's being done later, it's impossible to speculate. Are the table rows unique, without the key, because the values could cause the hash, at those coords to be overwritten

HTH
--Paul

Spend an hour a week on CPAN, helps cure all known programming ailments ;-)

 

[r2d22y]

What I should do is to store all the values and then write it out on a webbpage....

What do you recommend me to do for doing this in the best way in PERL, there are a lot of values that should be interpreted so I cant use many Mysql statements because then it should take to long time..

Thansk!

/D_S

 

[tchatzi]

I suggest to change it from hashref to arrayref which is easier to understand.

$instruction="select whatever";
$sth=$dbh->prepare($instruction);
$sth->execute;

#get everything that your select is suppose to fetch
while (my $rows = $sth->fetchrow_arrayref)
{
   ($id,$usrid,$date_from,$interval_nr,$event_cnt) = @$rows;
   $query{$id}->{usrid}       = $usrid;
   $query{$id}->{date_from}   = $date_from;
   $query{$id}->{interval_nr} = $interval_nr;
   $query{$id}->{event_cnt}   = $event_cnt;
}
$sth->finish();

#And now you can loop for every id and get everything you need
foreach $key (keys %query) #which means for every id
{
   $usrid       = $query{$key}->{usrid};
   $date_from   = $query{$key}->{date_from};
   $interval_nr = $query{$key}->{interval_nr};
   $event_cnt   = $query{$key}->{event_cnt};
        
   $total_event_cnt = $total_event_cnt + $event_cnt;
}

Now you can get everything realy simpler.
Lets say you want the 'event_cnt' where the 'interval_nr' is '2'

foreach $key (keys %query) #which means for every id
{
   $interval_nr = $query{$key}->{interval_nr};
   $event_cnt   = $query{$key}->{event_cnt};
   if ($interval_nr == 2)
   {
      print $event_cnt;
   }
}

Or if you know the id (lets say id=1) then

print $query{1}->{event_cnt};

By the way what is that '$tot{'lostcalls'}{$que}' ?


``The wise man doesn't give the right answers,
he poses the right questions.''
TIMTOWTDI

 

[r2d22y]

Hello

'$tot{'lostcalls'}{$que}' is a value in my hashtable %tot that I forgott to delete from this post.

Thank you very much for your help!

Regards/D_S