Beefy Boxes and Bandwidth Generously Provided by pair Networks
Perl Monk, Perl Meditation
 
PerlMonks  

using fetchrow_hashref

by Baz (Friar)
on Dec 30, 2003 at 19:39 UTC ( #317752=perlquestion: print w/ replies, xml ) Need Help??
Baz has asked for the wisdom of the Perl Monks concerning the following question:

The following works fin for me -
$dbh = DBI->connect("DBI:mysql:database=$serverDb;host=$serverName;por +t=$serverPort",$serverUser,$serverPass); $sth = $dbh->prepare("SELECT code,number FROM $serverTabl_4 WHERE name + = ? ORDER BY number DESC"); $sth->execute($surname); my $ref; while( $ref = $sth->fetchrow_hashref) { $codes{$ref->{code}} = $ref->{number}; print $ref->{number},"\n"; }
but where I use the following -
$dbh = DBI->connect("DBI:mysql:database=$serverDb;host=$serverName;por +t=$serverPort",$serverUser,$serverPass); $sth = $dbh->prepare("SELECT code,sum(number) FROM $serverTabl_4 group + by code"); $sth->execute(); while( $ref = $sth->fetchrow_hashref) { # $sum{$ref->{code}} = $ref->{number}; print $ref->{number},"\n"; }

I get a warning saying that print uses an uninitianlized value,
What am I doing wrong? The statement (in mysql.exe) - SELECT code,sum(number) FROM surname_26 group by code; gives me -
+------+-------------+ | code | sum(number) | +------+-------------+ | 1 | 1087 | | 21 | 606 | | 22 | 77 | | 23 | 26 | | 24 | 84 | | 25 | 43 | | 26 | 14 |
etc,etc....
Thanks.

Comment on using fetchrow_hashref
Select or Download Code
Re: using fetchrow_hashref
by extremely (Priest) on Dec 30, 2003 at 19:45 UTC
    Try SELECT code,sum(number) AS sum_number FROM $serverTabl_4 GROUP BY code where you've explicitly given the calculated column a name.

    I'm pretty sure that DBI doesn't have sexy handling to make up purty column names for the hash-refs. You're going to need to do that yourself or wind up with undef() for keys in your hashes.

    updateYeah, merlyn nailed it much clearer than I and diotalevi is right... hashes is magical. :)

    --
    $you = new YOU;
    honk() if $you->love(perl)

      There is no such thing as an undef hash key. Perl treats the undef as an empty, defined string.
•Re: using fetchrow_hashref
by merlyn (Sage) on Dec 30, 2003 at 19:45 UTC
    The keys of the hash being returned are the column headers. You aren't asking for sum(number). You're asking for number.

    You can use an "AS" clause to help there:

    SELECT code, sum(number) AS number FROM surname_26 group by code

    -- Randal L. Schwartz, Perl hacker
    Be sure to read my standard disclaimer if this is a reply.

Re: using fetchrow_hashref
by maverick (Curate) on Dec 30, 2003 at 19:47 UTC
    Chang your SQL query to read:
    SELECT code, sum(number) as foobar FROM $serverTabl_4 group by code
    and then use 'foobar' in the hash ref.

    Basically, the keys are whatever it thinks the column name is..so if you use 'sum(column)', 'avg(column)' or the like, it names the key that. If you tack on the 'as something_else' after it, you can effectively name the keys whatever you like. This is also handy when you have a join across two tables that have similarily named columns :)

    /\/\averick

    Update: Heh. 3rd fastest keys in the west...

      Cool, thanks guys.

Log In?
Username:
Password:

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

How do I use this? | Other CB clients
Other Users?
Others musing on the Monastery: (19)
As of 2014-07-11 19:34 GMT
Sections?
Information?
Find Nodes?
Leftovers?
    Voting Booth?

    When choosing user names for websites, I prefer to use:








    Results (235 votes), past polls