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

Hi Perl Monks

I am trying to get data from a mysql quesry result, what I want to do is perform a mysql lookup for a certain row and then store all the columns in a hash, for example, I would search for id 3 and then store the data in the following form: $data{column1} $data{column2}

I know how to do them one by one in the form of $data{'field'} = $ref->{column1};

Is there a way to make it automatic though, without specifying each hash variable one by one? Best Regards John

Replies are listed 'Best First'.
Re: SQL Questions
by steves (Curate) on Jul 08, 2002 at 09:55 UTC

    Have you looked at fetchrow_hashref()? Each row is returned as a hash reference, where the column names are the keys and the DB column values are the values.

    The only caveat is that it's not guaranteed to return a unique reference each time, so you need to copy the results; e.g.: 

    my $sql = "SELECT * FROM whatever";
my $sth = $dbh->prepare($sql);
$sth->execute();
my $row;
my $save_it;

while (defined($row = $sth->fetchrow_hashref()))
{
    $save_it = {};
    %$save_it = %$row;

    # Need to save each hashed row ($save_it) now too ...
}

    [download]

    That example assumes you're storing all rows somewhere (saving off $save_it somehow). For a single row, do the one fetch, copy the hash and return it or whatever.

[reply]
[d/l]
      You are doing the same thing DBI (possibly) does, i.e., using the same hash reference. If you push '$save_it' onto an array or something, you'll end up with the same hash everywhere. You'd want to put the 'my $save_it' inside the loop. But then it'd be easier to use selectall_(hash|array)ref anyway as already suggested.
[reply]
Re: SQL Questions
by tadman (Prior) on Jul 08, 2002 at 09:59 UTC
    First, you'd want to read up on the DBI calls selectall_hashref and selectall_arrayref which can come in really handy for things like this. The statement handle equivalents are fetchall_hashref and fetchall_arrayref, of course. 
    my $hash = $dbh->selectall_arrayref("SELECT id,name,age FROM foo",
                                    "id");

    [download]
    The catch is that selectall_hashref needs a key field to use as the hash key, so these have to be unique. If you don't have a unique column, you can use the alternative array fetch method and make do, or something like this: 
    # ...
my @rows;
$sth->execute();

while (my $row = $sth->selectrow_hashref())
{
     push(@rows, { %$row });   # Force unique reference
}

    [download]
[reply]
[d/l]
[select]
Re: SQL Questions
by amphiplex (Monk) on Jul 08, 2002 at 09:44 UTC
    this should do it: 
    for (keys %$ref) {
  $data{$_} = $ref->{$_};
}

    [download]
    ---- kurt
[reply]
[d/l]
      That was kind of a lacklustre answer, wasn't it?

      Either way, you could be doing this:
      @data{keys %$ref} = values %$ref;
      No for loop, one line, no fuss.
[reply]
[d/l]
[select]

Back to Seekers of Perl Wisdom