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

Hey all...

I think I've found a stumbling block for myself, but I'd like to find out if there's something I don't know before I start making lots of changes.

We recently changed column names to a shorter format, and in most of our code we are currently using a "table.column" format.

I have been a big fan of fetchrow_hashref for a while, and so I've used it in many, many, many places.

So now, when I get my hash back, the keys look like this:

"address_1"
"city"
"state"

[download]

...when I need them to look like this:

"address.address_1"
"address.city"
"address.state"

[download]

The two changes I am considering are to: 1)use a fetchrow_array, and list my values out; or 2)use " AS " in my SELECT statements. I hate both options.

Is there a way to tell MySQL and/or the DBI that I want the table.column format in my hash keys?

There is another question out there that is similar to mine, but the answer wasn't what I was looking for, and I just want to know if there is a better solution for me.

Here is that link:

How do I get "table.column" format from a select/join?

Thanks so much,
Dale

Replies are listed 'Best First'.
Re: fetchrow_hashref, and table.column format
by bradcathey (Prior) on Dec 20, 2005 at 19:30 UTC

    Take a look at DBI Recipes by the artful gmax.


    —Brad
    "The important work of moving the world forward does not wait to be done by perfect men." George Eliot
[reply]
      Thanks to everyone for the suggestions!

      Dale
[reply]
Re: fetchrow_hashref, and table.column format
by rnahi (Curate) on Dec 20, 2005 at 20:53 UTC

    Since you are using MySQL, here is a recipe that should do what you want:

    
sub get_recs {
    my ($dbh, $query, @params) = @_ ;
    my $sth = $dbh->prepare($query);
    eval {
        if (@params) {
           $sth->execute(@params);
        }
        else {
           $sth->execute;
        }
    };
    return if $@;
    my  $table_names = $sth->{mysql_table};
    my  $field_names = $sth->{NAME_lc};
    my  $data = $sth->fetchall_arrayref();
    my  @records;

    for my $fn (@$field_names) {
        $fn = shift(@$table_names) . '.' . $fn;
    }

    for (@$data) {
        my %rec;
        @rec{@$field_names} = @$_;
        push @records, \%rec;
    }
    return \@records;
}

    [download]

    This function will give back the same output of fetchall_arrayref({}) (with the hashref option, i.e. an array made of a hashref for each record) with the table name added to each field.

[reply]
[d/l]
Re: fetchrow_hashref, and table.column format
by jeffa (Bishop) on Dec 20, 2005 at 18:29 UTC

    I would use option 2, but i like using the AS alias. 

    SELECT address_1 as 'address.address_1',
    city as 'address.city',
    state as 'address.state',
...

    [download]
    Another option might be to override fetchrow_array() and append the table name to the column names, but what happens when you join to other tables? I think it's better to do the work in the SQL statement.

    jeffa

    L-LL-L--L-LL-L--L-LL-L--
-R--R-RR-R--R-RR-R--R-RR
B--B--B--B--B--B--B--B--
H---H---H---H---H---H---
(the triplet paradiddle with high-hat)
[reply]
[d/l]
Re: fetchrow_hashref, and table.column format
by holli (Abbot) on Dec 20, 2005 at 18:45 UTC
    You could use some kind of tie magic to map key names. I didn't work it out, but you can use the code of Tie::Hash::Cannabinol (lol) as a starting point.

    But honestly, i think you're better off if you rewrite your code, either by search and replacing field names or by changing to index driven processing.

    update:
    If you have control over the database, simple create a view/stored query or whatever you call it that maps your names and save it as _tablename. In your code you only have to change tablename to _tablename.

    update:
    Mentioning Tie::Hash::Cannabinol was not a joke (as the voting indicates). The module is really a good example for tie-ing hashes.


    holli, /regexed monk/
[reply]
[d/l]

Back to Seekers of Perl Wisdom