Problems returning values from a db and putting into a hash

DaWolf has asked for the wisdom of the Perl Monks concerning the following question:

Greetings.

I need the following:

Select records from a database and show them grouped by

1. Date
2. Manager
3. Status

So, I've developed the following code:

        $sth = $dbh->prepare_cached("SELECT DISTINCT A.p_code, A.busin
+ess, A.status, A.value, A.login, A.enc_date,
        B.name, B.state
        FROM business A, users B
        WHERE ORDER BY A.login, A.data_enc_proposta");
        }
        
        $dbh = SysUtils::Connect;
        $sth->execute;
        
        while (@data = $sth->fetchrow_array())
        {
            $rs = $data[0];
            $neg = $data[1];
            $st = $data[2];
            $vp = $data[3];
            $gel = $data[4];
            $dte = $data[5];
            $gen = $data[6];
            $fil = $data[7];
            
            %dados = ($fil => {$gel => {"$dte" => {  cliente => "$rs",
                                                                  nego
+cio => "$neg",
                                                                  stat
+us => "$st",
                                                                  valo
+r => "$vp",
                                                                  nome
+ => "$gen"
                                                                 }
                                                  }
                                      }
                        );
            ListaDados();
[download]

and here is the sub that shows them:

    foreach (keys (%dados))
    {
        $vf = "R\$ ";
        $tf = "R\$ ";
        $dados{$fil}{$gel}{$dte}{valor} =~ s/\.0000$/\,00/;
        $vf .= $dados{$fil}{$gel}{$dte}{valor};
        
        if ($cnt eq 0)
        {
            $message .= "<TR class=\"texto3\">
            <TD colspan=\"4\" BGCOLOR=\"$color2\"><span class =\"texto
+2\">$dados{$fil}{$gel}{$dte}{nome}</span></TD>
            </TR>
            <TABLE WIDTH=\"100%\" BORDER=\"0\" CELLSPACING=\"0\" CELLP
+ADDING=\"0\">
            <TR CLASS=\"texto2\">
            <TD>Client</TD>
            <TD>Business</TD>
            <TD>Status</TD>
            <TD>Value</TD>
            </TR>
            <TR>
            <TD COLSPAN=\"4\">
            <HR>
            </TD>
            </TR>
            <TR CLASS=\"texto3\">
            <TD BGCOLOR=\"$color2\">$dados{$fil}{$gel}{$dte}{cliente}<
+/TD>
            <TD BGCOLOR=\"$color2\">$dados{$fil}{$gel}{$dte}{negocio}<
+/TD>
            <TD BGCOLOR=\"$color2\">$dados{$fil}{$gel}{$dte}{status}</
+TD>
            <TD BGCOLOR=\"$color2\">$vf</TD>
            </TR>";
            $cnt++;
            ($color2,$color1) = ($color1,$color2);
        }
        else
        {
            $message .= "<TR CLASS=\"texto3\">
            <TD BGCOLOR=\"$color2\">$dados{$fil}{$gel}{$dte}{cliente}<
+/TD>
            <TD BGCOLOR=\"$color2\">$dados{$fil}{$gel}{$dte}{negocio}<
+/TD>
            <TD BGCOLOR=\"$color2\">$dados{$fil}{$gel}{$dte}{status}</
+TD>
            <TD BGCOLOR=\"$color2\">$vf</TD>
            </TR>";
            ($color2,$color1) = ($color1,$color2);
        }
    }
}
[download]

Well, it's generating multiple results and it returns the wrong manager (the alphabetically first on the users table), so something is going real wrong here...

Could you guys please give me a hand here?

Thanks in advance,

Er Galvão Abbott
a.k.a. Lobo, DaWolf
Webdeveloper

Comment on Problems returning values from a db and putting into a hash Select or Download Code

Replies are listed 'Best First'.
Re: Probelms returning values from a db and putting into a hash by buckaduck (Chaplain) on Apr 04, 2002 at 20:41 UTC
You loop through the keys of `%dados`, implicitly placing each key in the variable `$_`. But instead of using these keys, you keep using `$fil` to display the results in your `foreach` loop. This is what you get when you use global variables. A quick fix might be to change the loop to this, locally setting `$fil` to each successive key: `foreach my $fil (keys (%dados))` [download] buckaduck	[reply] [d/l] [select]
Re: Probelms returning values from a db and putting into a hash by scain (Curate) on Apr 04, 2002 at 20:18 UTC
Check your SQL--I think that you need a constraint tying the tables together in the join. Scott	[reply]
Re: Probelms returning values from a db and putting into a hash by trs80 (Priest) on Apr 04, 2002 at 20:54 UTC
I looked over what you have and you are doing some dangerous scoping with the code you have posted. I see where you are coming from and hopefully what I post will push you in the right direction. 1. use strict, please read up on this module it is really important since it can help you find many of your errors. 2. Use gobal variables with caution. 3. Pass your data as a reference to your sub routines and scope them inside of the subroutines. You didn't post all of your ListDados sub, but I do you are calling it on what is scoped as a global. Here is how I would write the top part of your code: # your original queries format odd so I adjusted it slightly # think this may also be the source of your results # being duplicated my $sth = $dbh->prepare_cached(qq!SELECT DISTINCT A.p_code, A.business, A.status, A.value, A.login, A.e +nc_date, B.name, B.state FROM business A, users B WHERE A.login = ? AND A.data_enc_proposta = ? ORDER BY A.login, A.data_enc_proposta!); } $dbh = SysUtils::Connect; # $login_value and $data_enc_value have to come # from somewhere. $sth->execute($login_value,$data_enc_value); while (my ($rs,$neg,$st,$vp,$gel,$dte,$gen,$fil) = $sth->fetch +row_array()) { my $hashref = { $fil => { $gel => { $dte => { cliente => "$rs", negocio => "$neg", status => "$st", valor => "$vp", nome => "$gen" } } } } ); ListaDados($hashref); } [download] Then inside of your ListDados I would have something like this: sub ListDados { my ($dados) = @_; foreach (keys %{$dados}) { $vf = "R\$ "; $tf = "R\$ "; $dados->{$fil}{$gel}{$dte}{valor} =~ s/\.0000$/\,00/; $vf .= $dados->{$fil}{$gel}{$dte}{valor}; if ($cnt eq 0) { $message .= qq!<TR class="texto3"> <TD colspan="4" BGCOLOR="$color2"><span class ="texto2">$dados->{$fil} +{$gel}{$dte}{nome}</span></TD> </TR> <TABLE WIDTH="100%" BORDER="0" CELLSPACING="0" CELLPADDING="0"> <TR CLASS="texto2"> <TD>Client</TD> <TD>Business</TD> <TD>Status</TD> <TD>Value</TD> </TR> <TR> <TD COLSPAN="4"> <HR> </TD> </TR> <TR CLASS="texto3"> <TD BGCOLOR="$color2">$dados->{$fil}{$gel}{$dte}{cliente}</TD> <TD BGCOLOR="$color2">$dados->{$fil}{$gel}{$dte}{negocio}</TD> <TD BGCOLOR="$color2">$dados->{$fil}{$gel}{$dte}{status}</TD> <TD BGCOLOR="$color2">$vf</TD> </TR>!; $cnt++; ($color2,$color1) = ($color1,$color2); } else { $message .= qq!<TR CLASS="texto3"> <TD BGCOLOR="$color2">$dados->{$fil}{$gel}{$dte}{cliente}</TD> <TD BGCOLOR="$color2">$dados->{$fil}{$gel}{$dte}{negocio}</TD> <TD BGCOLOR="$color2">$dados->{$fil}{$gel}{$dte}{status}</TD> <TD BGCOLOR="$color2">$vf</TD> </TR>!; ($color2,$color1) = ($color1,$color2); } } } [download] This is just some of the things you can do to clean it up. NOTE: not throughly proofread, please CB me on any errors and I correct them.	[reply] [d/l] [select]
Re: Probelms returning values from a db and putting into a hash by petdance (Parson) on Apr 04, 2002 at 20:19 UTC
Is the problem in your DB query, or in the HTML that is generating it? How do you know? Have you dumped the raw results? Also, I don't see any condition in your WHERE clause, which seems like extra-bad news in a join. xoxo, Andy -- <megaphone> Throw down the gun and tiara and come out of the float! </megaphone>	[reply]
Re: Re: Probelms returning values from a db and putting into a hash by DaWolf (Curate) on Apr 04, 2002 at 20:24 UTC
Oops, real sorry. The condition exists, but I've forgot to put it in the code that I've posted, here it is: `WHERE A.login=B.login AND B.state='$filial'` [download] The "$filial" comes from a form field. Er Galvão Abbott a.k.a. Lobo, DaWolf Webdeveloper	[reply] [d/l]
Re: Re: Re: Probelms returning values from a db and putting into a hash by petdance (Parson) on Apr 04, 2002 at 20:37 UTC
The WHERE was just an aside. My real question is: What debugging have you done? You can't just throw 100 lines of code out there and say "Hey, someone fix it." My original question stands: Is it in the DB results? Or is it in your generation of the HTML? Is it even a Perl thing? Have you tried your query in SQLPLUS, or whatever database client goes with the database you're using? xoxo, Andy -- <megaphone> Throw down the gun and tiara and come out of the float! </megaphone>	[reply]
Re: Probelms returning values from a db and putting into a hash by RMGir (Prior) on Apr 04, 2002 at 20:38 UTC
Could your problem be the %dados = assignment? That will overwrite your hash each time. I _think_ what you want is `$dados{$fil} = {$gel => {"$dte" => { cliente => "$rs", negocio => "$neg", + status => "$st", + valor => "$vp", nome => "$gen" + } + } };` [download] But I'm hampered a bit because I don't understand the variable names. AHA! GOT IT! It's your foreach. You're not using $_, you're using $fil, but the foreach is setting $_ -- Mike	[reply] [d/l]
Re: Probelms returning values from a db and putting into a hash by DaWolf (Curate) on Apr 04, 2002 at 20:27 UTC
I've tested the SQL statement directly on the db and it works, so this is really a perl problem... Er Galvão Abbott a.k.a. Lobo, DaWolf Webdeveloper	[reply]