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

for the life of me, i can't figure this out this is what i have right now, to populate a hash 
$query=$dbh->prepare($SQLstatement);
 $query->execute();
 $row=$query->fetchrow_hashref;
   $storedProfile = {};
   $storedProfile{user_id}            = $row->{user_id};
   # copy over each item from the result row
 $query->finish();

[download]
what i'd ideally like is something like 
%storedProfile = %row;

or

@keys = keys %row;
for (@keys){
 $storedProfile($key) = $row($key);
}

[download]
for the life of me, i can't figure out how to get either to work perlmonks, enlighten me

Replies are listed 'Best First'.
Re: getting the columns names from a mysql query
by joealba (Hermit) on May 02, 2002 at 04:39 UTC
    Give this a try: %storedProfile = %{$query->fetchrow_hashref}; And for a bonus tidbit of info -- fetchrow_hashref() returns lowercase column names in MySQL, but it returns uppercase column names in Oracle. Knowing is half the battle. :)
[reply]
[d/l]
      Of course DBI allows you to change the default behavior with: 
      # use uppercase keynames
$dbh->{FetchHashKeyName} = 'NAME_uc';

# use lowercase keynames
$dbh->{FetchHashKeyName} = 'NAME_lc';

      [download]

      -Blake

[reply]
[d/l]
      that uppercase oracle vs lowercase mysql thing wasted about 3days straight of mmy life last year, figuring out what the hell was going wrong when all my scripts broke on a clients db :/
[reply]
Re: getting the columns names from a mysql query
by lachoy (Parson) on May 02, 2002 at 05:18 UTC

    If in the future you decide to ditch fetchrow_hashref, you can always get the column names back using the {NAME} attribute of the statement handle:

     my $query = $dbh->prepare( $SQLstatement );
 $query->execute();
 print "Column names: ", join( ', ', @{ $query->{NAME} }, "\n",
       "As upper: ", join( ', ', @{ $query->{NAME_uc} }, "\n",
       "As lower: ", join( ', ', @{ $query->{NAME_lc} }, "\n";

    [download]

    Chris
    M-x auto-bs-mode

[reply]
[d/l]
[select]
Re: getting the columns names from a mysql query
by grep (Monsignor) on May 02, 2002 at 04:54 UTC

    I'm wondering if you are approaching this the right way. Normally I'm stuffing these $row's into an array and then dereferencing them when I need it.

    I usually end up writing something more like:
    completely untested sample code follows 

    while (my $row = $sth->fetchrow_hashref) {
 push(@profiles,$row);
}

    [download]

    then you deref @profiles as you go

    foreach (@profiles) {
 print "$_->{user_id}\n";
}

    [download]

    A couple of other things I noticed

    $storedProfile = {};
    does not do what you think it does. It creates a scalar called $storedProfile and assigns to it an empty hashref. It looks like you are not using strict, this would not compile if you were using strict (assuming you wanted an empty hash called %storedProfile). BTW to clear a hash you want to '%storedProfile = ()'

    also you can rewrite this 

    @keys = keys %row;
for (@keys){

    [download]

    as

    foreach (keys %row) {

    [download]

    Which IMO looks cleaner.



    grep
    Unix - where you can thrown the manual on the keyboard and get a command
[reply]
[d/l]
[select]
      BTW to clear a hash you want to '%storedProfile = ()'

      [download]
      actually -- thats what i have in my code.. the $ was a sloppy typo in the browser :) 
      while (my $row = $sth->fetchrow_hashref)

      [download]
      i do that when i'm expecting more than one row -- but in this case, i'm searching by a unique key, so there can only be one row -- the while loop isn't necessary :) thanks to all.. i'm gonna test this all.. 
          %storedProfile=%{$query->fetchrow_hashref};

      [download]
      seems to do what i want, though i'm worried what would happen if there's more than 1 result row...
[reply]
[d/l]
[select]
A reply falls below the community's threshold of quality. You may see it by logging in.

Back to Seekers of Perl Wisdom