using fetchrow_hashref

[Baz]

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.

[merlyn]

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

[maverick]

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 :)

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

[Baz]

Cool, thanks guys.

[extremely]

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. :)

[diotalevi]

There is no such thing as an undef hash key. Perl treats the undef as an empty, defined string.