Perl Beginners - Addressbook Tutorial Step 8 - Creating methods to display record details
We've finished the methods to display search results, now we need a way to display all information about a particular record.
First, let's modify SQL.pm to include the person_id of the records displayed from Step 7:
sub search_start {
return <<QEND;
select distinct A.person_id as PERSON_ID, A.last_name as LAST_NAME, A.first_name as FIRST_NAME,
A.middle_name as MIDDLE_NAME, A.image as IMAGE
from person A, address B, number C, person_address D, person_number E
where A.person_id=D.person_id
and A.person_id=E.person_id
and B.address_id=D.address_id
and C.number_id=E.number_id
QEND
}
Now, let's talk about another HTML::Template tag, <TMPL_INCLUDE>. When we put this tag in a HTML::Template file, the content of the included template file becomes part of the output. For example, let's say we have a file called somefile.tmpl:
<TMPL_INCLUDE NAME='someotherfile.tmpl'>
<font size="-1" face="arial, helvetica">
hello world!
</font>
<TMPL_INCLUDE NAME='yetotherfile.tmpl'>
If the file someotherfile.tmpl contains this:
<html>
<body>
And the file yetanotherfile.tmpl contains this:
</body>
</html>
We can simply call somefile.tmpl as our output file, and the final HTML will look like this:
<html>
<body>
<font size="-1" face="arial, helvetica">
hello world!
</font>
</body>
</html>
Pretty slick. So, let's break our original template file, search_results up into 4 separate files. We'll call the first one header.tmpl:
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
<html>
<head>
<title><TMPL_VAR ESCAPE=HTML NAME=TITLE></title>
<meta name="author" content="fliptop@peacecomputers.com">
<meta http-equiv="content-type" content="text/html;charset=iso-8859-1">
</head>
<body bgcolor="#FFFFFF" link="#0000FF" alink="#FF0000" vlink="#C000FF">
<TMPL_IF NAME=ERROR>
<table>
<tr>
<td>
<font color="#FF0000">
<TMPL_VAR NAME=ERROR>
</font>
</td>
</tr>
</table>
</TMPL_IF>
<!--end header-->
Notice we've included a new template variable tag, <TMPL_VAR ESCAPE=HTML NAME=TITLE>. We'll need this variable so we can have a different title for each page. Next, let's put the search form into a template file called search_form.tmpl:
<TMPL_INCLUDE NAME='header.tmpl'>
<!--begin search_form-->
<table>
<form method="post" action="/cgi-bin/handler.cgi">
<tr>
<td align="right">Last Name:</td>
<td align="left"><input type="text" name="last_name" size="15" maxlength="50"></td>
<td align="right">First Name:</td>
<td align="left"><input type="text" name="first_name" size="15" maxlength="50"></td>
</tr>
<tr>
<td align="right">Prefix:</td>
<td align="left"><input type="text" name="prefix" size="15" maxlength="50"></td>
<td align="right">City:</td>
<td align="left"><input type="text" name="city" size="15" maxlength="50"></td>
</tr>
<tr>
<td align="right">Area Code:</td>
<td align="left">
<select name="area_code">
<option value="">Any</option>
<TMPL_LOOP NAME=AREA_CODES>
<option value="<TMPL_VAR ESCAPE=HTML NAME=AREA_CODE>"><TMPL_VAR ESCAPE=HTML NAME=AREA_CODE></option>
</TMPL_LOOP>
</select>
</td>
<td align="right">State:</td>
<td align="left">
<select name="state">
<option value="">Any</option>
<TMPL_LOOP NAME=STATES>
<option value="<TMPL_VAR ESCAPE=HTML NAME=STATE>"><TMPL_VAR ESCAPE=HTML NAME=STATE></option>
</TMPL_LOOP>
</select>
</td>
</tr>
<tr>
<td colspan="2" align="center">
<input type="submit" value="Search">
</td>
<td colspan="2" align="left">
Enter your search criteria and click <i>Search</i>.
</td>
</tr>
</table>
<input type="hidden" name="action" value="search">
</form>
<!--end search_form-->
We've included the first template file, header.tmpl as a TMPL_INCLUDE in the first line. Now, let's put the results in its own file, search_results.tmpl:
<TMPL_INCLUDE NAME='search_form.tmpl'>
<!--begin search_results-->
<table>
<tr>
<th>Last Name</th>
<th>First Name</th>
<th>Middle Name</th>
<th>Image</th>
</tr>
<TMPL_LOOP NAME=RESULTS>
<tr>
<td><TMPL_VAR ESCAPE=HTML NAME=LAST_NAME></td>
<td><TMPL_VAR ESCAPE=HTML NAME=FIRST_NAME></td>
<td><TMPL_VAR ESCAPE=HTML NAME=MIDDLE_NAME></td>
<td><TMPL_VAR ESCAPE=HTML NAME=IMAGE></td>
</tr>
</TMPL_LOOP>
</table>
<!--end search_results-->
<TMPL_INCLUDE NAME='footer.tmpl'>
Finally, we'll put the last closing tags into a file called footer.tmpl:
<!--begin footer-->
</body>
</html>
Now, we can modify the start and search action methods in Handler.pm to include the new title variable and point to the correct template files:
sub start {
my ($self) = @_;
my $record = PEACE::AddressBook::Record->new($self->_db);
$self->{_TEMPLATE}->{AREA_CODES} = $record->select_distinct_area_codes();
$self->{_TEMPLATE}->{STATES} = $record->select_distinct_states();
$self->{_TEMPLATE}->{TITLE} = 'Search for a Record' unless defined $self->_template->{TITLE};
$self->{_TFILE} = 'search_form.tmpl' unless $self->_tfile;
return;
}
sub search {
my ($self, @args) = @_;
my $args = $self->_get_args(@args);
my $error = $self->_check_args($args);
if ($error) {
$self->{_TEMPLATE}->{ERROR} = $error;
return $self->start();
}
my $record = PEACE::AddressBook::Record->new($self->_db);
$self->{_TEMPLATE} = {
RESULTS => $record->search_records($args)
};
$self->{_TFILE} = 'search_results.tmpl';
$self->{_TEMPLATE}->{TITLE} = 'Search Results';
return $self->start();
}
Now let's modify search_form.tmpl and include a link on the person's last name:
<td>
<a href="/cgi-bin/handler.cgi?action=person&person_id=<TMPL_VAR ESCAPE=URL NAME=PERSON_ID>">
<TMPL_VAR ESCAPE=HTML NAME=LAST_NAME>
</a>
</td>
We've defined a new method, person. This will be the next action method to add to Handler.pm. First, modify the %REQUIRED_PARAMS hash:
my %REQUIRED_PARAMS = (
start => {},
search => {
last_name => [ 0, 0, 'Last Name' ],
first_name => [ 0, 0, 'First Name' ],
prefix => [ 0, 0, 'Prefix' ],
city => [ 0, 0, 'City' ],
area_code => [ 0, 0, 'Area Code' ],
state => [ 0, 0, 'State' ]
},
person => {
person_id => [ 1, 1, 'Person ID' ]
}
);
Notice the person_id parameter is required and must consist of at least 1 character. Next, we'll add the person action method:
sub person {
my ($self, @args) = @_;
my $args = $self->_get_args(@args);
my $error = $self->_check_args($args);
if ($error) {
$self->{_TEMPLATE}->{ERROR} = $error;
return $self->start();
}
my $record = PEACE::AddressBook::Record->new($self->_db);
my @person = $record->get_person_by_person_id($args->{person_id});
$self->{_TEMPLATE} = {
PERSON_ID => $person[0],
LAST_NAME => $person[1],
FIRST_NAME => $person[2],
MIDDLE_NAME => $person[3],
IMAGE => $person[4],
ADDRESSES => $record->get_addresses_by_person_id($args->{person_id}),
NUMBERS => $record->get_numbers_by_person_id($args->{person_id})
};
$self->{_TFILE} = 'person.tmpl';
$self->{_TEMPLATE}->{TITLE} = 'Record Information';
return $self->start();
}
Again, the perl motto (TMTOWTDI) sings out. We could have just as easily retrieved all the person information including numbers and addresses with one utility method by nesting SQL statements in the fetch loop. However, I chose to break it down into 3 separate methods. We'll need to add these 3 methods to Record.pm:
sub get_person_by_person_id {
my ($self, $person_id) = @_;
my @person = $self->dbh->selectrow_array(
PEACE::AddressBook::SQL->select_person_by_person_id(),
undef,
$person_id
);
return @person;
}
sub get_addresses_by_person_id {
my ($self, $person_id) = @_;
my $sth = $self->dbh->prepare(PEACE::AddressBook::SQL->select_addresses_by_person_id());
$sth->execute($person_id);
my @addresses;
while (my $ref = $sth->fetchrow_hashref()) { push(@addresses, $ref); }
$sth->finish();
return \@addresses;
}
sub get_numbers_by_person_id {
my ($self, $person_id) = @_;
my $sth = $self->dbh->prepare(PEACE::AddressBook::SQL->select_numbers_by_person_id());
$sth->execute($person_id);
my @numbers;
while (my $ref = $sth->fetchrow_hashref()) { push(@numbers, $ref); }
$sth->finish();
return \@numbers;
}
Next, we'll add the new queries to SQL.pm:
sub select_person_by_person_id {
return <<QEND;
select person_id as PERSON_ID, last_name as LAST_NAME, first_name as FIRST_NAME,
middle_name as MIDDLE_NAME, image as IMAGE
from person
where person_id=?
QEND
}
sub select_addresses_by_person_id {
return <<QEND;
select A.descr as DESCR, A.address1 as ADDRESS1, A.address2 as ADDRESS2,
A.city as CITY, A.state as STATE, A.zip as ZIP
from address A, person_address B
where A.address_id=B.address_id
and B.person_id=?
QEND
}
sub select_numbers_by_person_id {
return <<QEND;
select A.descr as DESCR, A.area_code as AREA_CODE, A.prefix as PREFIX,
A.suffix as SUFFIX, A.extension as EXTENSION
from number A, person_number B
where A.number_id=B.number_id
and B.person_id=?
QEND
}
Finally, we'll create the new template file, person.tmpl:
<TMPL_INCLUDE NAME='search_form.tmpl'>
<!--begin person-->
<table>
<tr>
<td>
<b>Record: <TMPL_VAR ESCAPE=HTML NAME=PERSON_ID></b>
<TMPL_VAR ESCAPE=HTML NAME=LAST_NAME>,
<TMPL_VAR ESCAPE=HTML NAME=FIRST_NAME>
<TMPL_VAR ESCAPE=HTML NAME=MIDDLE_NAME>
</td>
<td rowspan="3">
<TMPL_VAR ESCAPE=HTML NAME=IMAGE>
</td>
</tr>
<tr>
<td>
<TMPL_LOOP NAME=ADDRESSES>
(<TMPL_VAR ESCAPE=HTML NAME=DESCR>)
<TMPL_VAR ESCAPE=HTML NAME=ADDRESS1>
<TMPL_VAR ESCAPE=HTML NAME=ADDRESS2>
<TMPL_VAR ESCAPE=HTML NAME=CITY>,
<TMPL_VAR ESCAPE=HTML NAME=STATE>
<TMPL_VAR ESCAPE=HTML NAME=ZIP>
<br>
</TMPL_LOOP>
</td>
</tr>
<tr>
<td>
<TMPL_LOOP NAME=NUMBERS>
(<TMPL_VAR ESCAPE=HTML NAME=DESCR>)
<TMPL_VAR ESCAPE=HTML NAME=AREA_CODE> -
<TMPL_VAR ESCAPE=HTML NAME=PREFIX> -
<TMPL_VAR ESCAPE=HTML NAME=SUFFIX>
<TMPL_IF NAME=EXTENSION>x<TMPL_VAR ESCAPE=HTML NAME=EXTENSION></TMPL_IF>
<br>
</TMPL_LOOP>
</td>
</tr>
</table>
<!--end person-->
<TMPL_INCLUDE NAME='footer.tmpl'>
We'll wrap the extension inside a <TMPL_IF> statement so the 'x' doesn't appear if there's no extension. Also, for now we'll just print out the image value. Later, when we work on the code to upload files using CGI.pm, the image will appear to the right of the person information.
That's it! Add a few test numbers and addresses, do an initial search, and the last name of the result list should be a link. Click the link, and you should see something like this:
Coming next - The restricted perl handler and methods to add new records
Copyright © 2001 by Peace Computer Systems