Database hash/array mapping and OO

[inblosam]

I think I am close, but I am not as familiar with OO Perl to get it right. I basically want to create a User object and get data from the database for the user. Essentially I am trying to get this to work with other tables in the database, so more than one row may be fetched. Here is the User.pm I made:

package User;

use strict;
use DBI();
use Data::Dumper;

sub new {

    my $class = shift;

# Connect to the database.
my $dbh = DBI->connect("DBI:mysql:database=somedb;host=localhost","use
+rname", "password",{'RaiseError' => 1});
# Now retrieve data from the table.
my $sth = $dbh->prepare("SELECT username,email,phone FROM users");
$sth->execute();

### gets the columns from the query
my @columns = @{$sth->{'NAME'}};

### this maps the columns to their order in a hash
### so later with the value of the column key I can look it up
### from the final array
my %columns = map { $columns[$_] => $_ } (0..$#columns);

### this grabs all the data from the query, with the array
### having the fields in the same order as numbered in the %columns
my $self = $sth->fetchall_arrayref();

### this works here when uncommented
#   print "$columns{email}\n";

### this works and shows the column to number mapping
print Dumper(\%columns);

### same as below except ", 'User'" is added to end
print Dumper(\$self);

    return bless $self, $class;
}



###     gets the data from the database
sub get_user {
    my $self = shift;
    my $row = shift;
    my $field = shift;


###   just prints out the contents of $self variable
print Dumper(\$self);

###   In comments is what I want to do, call in the row and the field 
###   and get the proper data back, like...


#   return $self->[$row][$column{$field}];


###   but I can't seem to get the %column data in this subroutine.
###   when I run it I get: Global symbol "%column" requires explicit p
+ackage name at User.pm line 39.

###   but this is all I can get to work right now.
    return $self->[0][1];

}

1;

And here is the script making the object, etc.:

#!/usr/bin/perl -w
use strict;
use User;

### create a new object
my $user = User->new();


### call some data, $object->sub(row,'field')

print STDOUT "User ID is:", $user->get_user(0,'username'), "\n";

exit 0;

The problem is in the User.pm I can't seem to figure out how to call the %columns hash in the subroutine. There may be other problems, but this one I haven't been able to figure out yet. Any help would much appreciated!

[Aragorn]

The variable %columns is only available in User::new(). Also, it's misspelled in User::get_user(), but even spelling it correctly won't work ;-).

An approach would be to not bless the returned array reference, but bless a hash reference which holds both the array reference with the records, and the column names:

...
my $self = {};
$self->{records} = $sth->fetchall_arrayref();
$self->{columns} = \%columns;
...

Now, in User::get_user(), you get:

...
return $self->{records}->[$row][{$self}->{columns}->{$field}];
...

Note, the code is untested, so there could be a syntax error lurking in there somewhere, but I hope I got the point across.

Arjen

[Fletch]

You don't understand what my does and variable scoping and need to see Coping with Scopng. Or look at Class::DBI which does similar stuff for you.

[inblosam]

Read that, didn't help too much. Just says use my and don't use local. I tried using local anyhow and that didn't fix the 'Global' error I got. Class::DBI probably is the way to go...I figured someone had done what I was trying to do before.

[Fletch]

No, you need to read again and understand the scoping of lexical variables. Your my %columns delcaration is only good for the sub you declare it in. If you want it visible to both subs you need to declare it outside of them at the file scope. CwS explains this.