Beefy Boxes and Bandwidth Generously Provided by pair Networks
Syntactic Confectionery Delight

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 |

Replies are listed 'Best First'.
•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 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 :)


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

      Cool, thanks guys.
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.

Log In?

What's my password?
Create A New User
Node Status?
node history
Node Type: perlquestion [id://317752]
Approved by Enlil
and all is quiet...

How do I use this? | Other CB clients
Other Users?
Others surveying the Monastery: (3)
As of 2017-03-01 20:47 GMT
Find Nodes?
    Voting Booth?
    Before electricity was invented, what was the Electric Eel called?

    Results (429 votes). Check out past polls.