I wanted to tackle my next project using Objects since I'm fairly inexperienced in OO, it seems like a good chance to learn.

I have created a guest object where the methods are simple add/delete/update. Using DBI, the program posts the information to a MySQL database. A simple application where the users can record information pertaining to their guests on-line.

Question 1-> There has to be a more efficient way to store the queries because I am defining the queries each time the method is called... but where should they be stored?

Question 2-> As you can see I am opening the database handle in the controller program and then passing the dbh to the method. I really don't want to open the connection to the database is there a better way to do this? when should open the connection to the database?

Here's the Guest.pm:

package Guest;

sub new  {
   my ($pkg, $last_name, $first_names, $address, $city,
       $state, $zip, $est_count) = @_;
   bless {
      _last_name => $last_name,
      _first_names => $first_names,
      _address => $address,
      _city => $city,
      _state => $state,
      _zip => $zip,
      _est_count => $est_count
   }, $pkg;
}

# Database methods: add/update/delete
# - All database objects requre a database handle parameter
#   and will use DBI to talk to database.
sub add {
   my ($obj,$dbh) = @_;
   # Insert row into database
   my $sth = $dbh->prepare( "INSERT INTO guest_list
                                (last_name, first_names, address,
                                 city, state, zip, est_count)
                              VALUES  (?,?,?,?,?,?,?)" );

   $sth->execute($obj->{_last_name},
                 $obj->{_first_names},
                 $obj->{_address},
                 $obj->{_city},
                 $obj->{_state},
                 $obj->{_zip},
                 $obj->{_est_count});
   return 1;
}
# Delete
sub delete {
   my ($obj,$dbh) = @_;

   # Prep delete statement
   my $sth = $dbh->prepare( "DELETE FROM guest_list
                             WHERE  last_name = ? and
                                    first_names = ?" );

   $sth->execute($obj->{_last_name},
                 $obj->{_first_names});
   # Don't forget to delete the object
   return 1;
}

# Update
sub update {
   my ($obj,$dbh) = @_;

   # Insert row into database
   my $sth = $dbh->prepare( "UPDATE guest_list
                              SET   address = ?,
                                    city = ?,
                                    state = ?,
                                    zip = ?,
                                    est_count = ?
                              WHERE  last_name = ? and
                                    first_names = ?" );

   $sth->execute($obj->{_address},
                 $obj->{_city},
                 $obj->{_state},
                 $obj->{_zip},
                 $obj->{_est_count},
                 $obj->{_last_name},
                 $obj->{_first_names});
   return 1;
}

#Accessor methods
sub last_name   {my $obj = shift; @_ ? $obj->{_last_name} = shift : 
   $obj->{_last_name}}
sub first_names {my $obj = shift; @_ ? $obj->{_first_names} = 
   shift : $obj->{_first_names}}
sub address     {my $obj = shift; @_ ? $obj->{_address} = shift : 
   $obj->{_address}}
sub city        {my $obj = shift; @_ ? $obj->{_city} = shift : 
   $obj->{_city}}
sub state       {my $obj = shift; @_ ? $obj->{_state} = shift : 
   $obj->{_state}}
sub zip         {my $obj = shift; @_ ? $obj->{_zip} = shift : 
   $obj->{_zip}}
sub est_count   {my $obj = shift; @_ ? $obj->{_est_count} = shift : 
   $obj->{_est_count}}

1;
[download]

test.cgi:

#!/usr/bin/perl -w

use DBI;
use Guest;

# Connect to database
my $dbh = DBI->connect( "dbi:mysql:xxx", 'xxx', 'xxx' )
   or die "Can't connect to MySQL database: $DBI::errstr\n";

my $last_name = "Rodriguez"; 
my $q_last_name = $dbh->quote($last_name);
my $first_names = "Alex"; 
my $q_first_names = $dbh->quote($first_names);

# Fetch One
my ($add,$city,$state,$zip,$cnt) =
      $dbh->selectrow_array( "SELECT address, city, 
                                     state, zip, est_count
                              FROM  guest
                              WHERE last_name = $q_last_name
                              AND   first_names = $q_first_names" );

my $guest = new Guest ($last_name,$first_names,$add,
                       $city,$state,$zip,$cnt);

#### Test Update
print "Testing Update...\n";
$guest->state("TX");
$guest->update($dbh);

$dbh->disconnect();
exit;
[download]

Any suggestions and/or comment would be much appreciated. thanks, mike