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;
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;
Any suggestions and/or comment would be much appreciated.
thanks, mike
-
Are you posting in the right place? Check out Where do I post X? to know for sure.
-
Posts may use any of the Perl Monks Approved HTML tags. Currently these include the following:
<code> <a> <b> <big>
<blockquote> <br /> <dd>
<dl> <dt> <em> <font>
<h1> <h2> <h3> <h4>
<h5> <h6> <hr /> <i>
<li> <nbsp> <ol> <p>
<small> <strike> <strong>
<sub> <sup> <table>
<td> <th> <tr> <tt>
<u> <ul>
-
Snippets of code should be wrapped in
<code> tags not
<pre> tags. In fact, <pre>
tags should generally be avoided. If they must
be used, extreme care should be
taken to ensure that their contents do not
have long lines (<70 chars), in order to prevent
horizontal scrolling (and possible janitor
intervention).
-
Want more info? How to link
or How to display code and escape characters
are good places to start.