using fetchrow

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"; 
}
[download]

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"; 
}
[download]

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 |
[download]

etc,etc....
Thanks.

Comment on using fetchrow_hashref Select or Download Code

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` [download] -- Randal L. Schwartz, Perl hacker Be sure to read my standard disclaimer if this is a reply.	[reply] [d/l]
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` [download] 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...	[reply] [d/l]
Re: Re: using fetchrow_hashref by Baz (Friar) on Dec 30, 2003 at 19:55 UTC
Cool, thanks guys.	[reply]
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)	[reply] [d/l] [select]
Re: Re: using fetchrow_hashref by diotalevi (Canon) on Dec 30, 2003 at 20:00 UTC
There is no such thing as an undef hash key. Perl treats the undef as an empty, defined string.	[reply]


Your skill will accomplish what the force of many cannot
	PerlMonks

using fetchrow_hashref